How to transfer data from SQL Server 2000 to Db2 V8 using DTS and Odbc driver

hi all,

I am trying to create a dts package to transfer data from tables in my
sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
db2 oledb driver for the destination this works fine except that rows
are committed on a row by row basis thus performance is slow. I want
to use the odbc driver but when I define it as the destination, i get
errors saying that the maximum number of rows failed has exceeded the
minimum.

Does anybody know what to do in order to get the transfer working
using ibm db2 odbc as the datasource?

Thanks
Lyn"Lyn Duong" <lynd@.tablimited.com.au> wrote in message
news:8d1cda6d.0407061859.2b9da589@.posting.google.c om...
> hi all,
> I am trying to create a dts package to transfer data from tables in my
> sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
> db2 oledb driver for the destination this works fine except that rows
> are committed on a row by row basis thus performance is slow. I want
> to use the odbc driver but when I define it as the destination, i get
> errors saying that the maximum number of rows failed has exceeded the
> minimum.
> Does anybody know what to do in order to get the transfer working
> using ibm db2 odbc as the datasource?
> Thanks
> Lyn

I guess the error message is actually "The number of failing rows exceeds
the maximum
specified"? That is a very general error, but there should be a more
detailed error message immediately after it in the log, which may give a
clue to the issue - can you post the full error message?

Simon|||Hi,

Yes, I logged the errors in an exception file and in an error file
I suspect that the odbc driver is not parsing the record properly

The exception file contains
DATA TRANSFORMATION SERVICES: Data Pump Exception Log

Package Name: (null)
Package Description: (null)
Package ID: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Version: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Step Name: DTSStep_DTSDataPumpTask_1
Execution Started: 8/07/2004 9:43:51 AM
Error at Destination for Row number 1. Errors encountered so far in
this task: 1. |1|609||||||||IS

The error file contains
The execution of the following DTS Package succeeded:

Package Name: (null)
Package Description: (null)
Package ID: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Version: {AE67DD38-6E64-4184-B5EB-6EE519F9CE9A}
Package Execution Lineage: {6AE6BF2C-8187-4A9F-AA5F-2565B873DC51}
Executed On: ROULETTE
Executed By: lynd
Execution Started: 8/07/2004 9:43:50 AM
Execution Completed: 8/07/2004 9:43:51 AM
Total Execution Time: 0.25 seconds

Package Steps execution information:

Step 'DTSStep_DTSDataPumpTask_1' failed

Step Error Source: Microsoft Data Transformation Services (DTS) Data
Pump
Step Error Description:The number of failing rows exceeds the maximum
specified. ( (80004005): )
Step Error code: 8004206A
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:0

Step Execution Started: 8/07/2004 9:43:50 AM
Step Execution Completed: 8/07/2004 9:43:51 AM
Total Step Execution Time: 0.22 seconds
Progress count in Step: 0
************************************************** **************************************************

Any ideas ? Thanks!
"Simon Hayes" <sql@.hayes.ch> wrote in message news:<40ec34b4$1_1@.news.bluewin.ch>...
> "Lyn Duong" <lynd@.tablimited.com.au> wrote in message
> news:8d1cda6d.0407061859.2b9da589@.posting.google.c om...
> > hi all,
> > I am trying to create a dts package to transfer data from tables in my
> > sql server 2000 database to a db2 UDB v8.1.4 database. When I use ibm
> > db2 oledb driver for the destination this works fine except that rows
> > are committed on a row by row basis thus performance is slow. I want
> > to use the odbc driver but when I define it as the destination, i get
> > errors saying that the maximum number of rows failed has exceeded the
> > minimum.
> > Does anybody know what to do in order to get the transfer working
> > using ibm db2 odbc as the datasource?
> > Thanks
> > Lyn
> I guess the error message is actually "The number of failing rows exceeds
> the maximum
> specified"? That is a very general error, but there should be a more
> detailed error message immediately after it in the log, which may give a
> clue to the issue - can you post the full error message?
> Simonsql

how to tell if a login has already been created

I want to create a script that will run and create a predetermined set of logins AND dbrolemembership but only if they do not presently exist in the database. I have played around with sp_addlogin and sp_addrolemember. I then checked the sysusers and syslogins table to determine the existence. I cannot get a row to insert into sysusers. Can someone elighten me on where the login & rolemembership is stored. I also spent some time looking for a proc that might give me what i need.

any direction or comments are appreciated.

MikeRole membership is stored in sysusers table of each database (except for tempdb) and login info in master.dbo.syslogins. You right on track based on sp's and tables you mentioned. All you need to do is script those sp's (sp_addlogin and sp_addrolemember) in your QA and you'll have all the answers yourself.|||Thanks for the response.

I have written the scritps to create the users and add them to the correct roles. I would like to test to see if the user and /or role(s) are already existing when I run the script.

PRINT '===> Add logins'
EXEC sp_addlogin 'pubuser','pubuser'
EXEC sp_addlogin 'opsuser','opsuser'

PRINT '===> Add user, attach login and assign permissions for users'
USE Main
EXEC sp_grantdbaccess 'pubuser'
EXEC sp_addrolemember 'db_datareader','pubuser'
EXEC sp_addrolemember 'db_datawriter','pubuser'

EXEC sp_grantdbaccess 'opsuser'
EXEC sp_addrolemember 'db_datareader','opsuser'
EXEC sp_addrolemember 'db_datawriter','opsuser'

The issue / question is when I select * from syslogins I can see the pubuser and opsuser. However, I cannot see a cooresponding entry on sysusers. Still missing a piece somewhere?

mike|||I just ran your script and replaces Main with my database, and when I do a SELECT from sysusers of that database I see both users added. Did I miss anything?

And of course, you'd do something like "if not exist (select1 from master.dbo.syslogins where name = <new_login>) exec sp_addlogin <...>" to validate the presence or abscence of a login, same with users, etc.|||I was using the master sysusers table.

Thanks

How to tailor CREATE DATABASE for an arbitrary instance

Suppose the following:

As part of a product install (using InstallShield)...


I create a SQL Server Express instance (say "X") via a silent install

I supply a script to create a database in instance X.

The idea, of course, is to have a fully automated install. But there's one problem I can't quite see how to work around:

- the CREATE DATABASE statement needs the name of a file to contain the database, and that file needs to be in a folder that belongs to the instance (e.g. Microsoft SQL Server\MSSQL.1, Microsoft SQL Server\MSSQL.2, etc).

Is there a syntactic variant that allows me to avoid this problem?

Thanks
Josh

hi,

don't know if this can be a solution to your problem, but in the CREATE DATABASE syntax only the database name is mandatory.. http://msdn2.microsoft.com/en-us/library/ms176061.aspx

if you only specify

CREATE DATABASE myDb;

the "myDb" name will be used for physical files naming, resulting in a "myDb.Mdf" and "myDb_log.Ldf" files, stored in the folder hosting all other databases..

the new "myDb" will inherits all settings applied to "model" database, both regarding database settings as physical settings as well (size, growth, etc)...

but you loose control over the physical and logical name specification, and, of course, you can this way no longer specify size, growth, max size, ...

regards

|||

Andrea:

Between then and now, I found what I needed in one of your posts from yesterday.

With very minor adaptation, I arrived at the following (which worked like a charm):

DECLARE @.itemp VARCHAR(255);
DECLARE @.RegKey VARCHAR(255);
DECLARE @.instance VARCHAR(255);
DECLARE @.subfolder VARCHAR(255);
DECLARE @.rootfolder VARCHAR(255);
DECLARE @.DBFileName VARCHAR(255);
DECLARE @.DBLogName VARCHAR(255);
DECLARE @.CreateDB VARCHAR(MAX);

SELECT @.itemp = CONVERT(varchar, SERVERPROPERTY('InstanceName'));
IF @.itemp IS NULL
SET @.instance = 'MSSQLServer';
ELSE
SET @.instance = @.itemp;

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL\',
@.value_name = @.instance,
@.value = @.subfolder OUTPUT;

SET @.RegKey = 'SOFTWARE\Microsoft\Microsoft SQL Server\' + @.subfolder + '\Setup';

EXEC master..xp_regread @.rootkey='HKEY_LOCAL_MACHINE',
@.key = @.RegKey,
@.value_name = 'SQLDataRoot',
@.value = @.rootfolder OUTPUT;

SET @.DBFileName = @.rootfolder + '\DATA\' + 'MyDatabase.mdf';
SET @.DBLogName = @.rootfolder + '\DATA\' + 'MyDatabase_log.mdf';

-- Create the database

SET @.CreateDB = 'CREATE DATABASE [OTHGMD] ON PRIMARY
(NAME = ''MYDATABASE'', FILENAME = ''' +
@.DBFileName + ''', SIZE = 51200KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB) ' +
'LOG ON (NAME = N''MYDATABASE_log'', FILENAME = ''' +
@.DBLogName + ''', SIZE = 5120KB , MAXSIZE = 2048GB , FILEGROWTH = 10%) ' +
'COLLATE SQL_Latin1_General_CP1_CI_AS;'

EXEC (@.CreateDB);
GO

Thanks Very Much
Josh

p.s. A pox on the SQL Syntax committee for making us have to do this. Can you think of a good reason why the file name can't be a variable?

|||

hi,

usually referenced objects are to be considered constants or literals... only parameters can (and should be) variable

as you surely know, you are executing dynamic SQL, composed on the fly and executed... SQL Server takes even care of that as it can, somehow and sometime, avoid recompilation of some dynamic statements, but that' all... just a "limitation" of current implementation of DBMS engines, and, as per the CREATE DATABASE statement, this regards SQL Server "only" as each different vendor provides it's own "syntax" to generate new catalogues and database... it's not that standard... AFAIK the ANSI standard starts with the SCHEMA, and, for the ANSI commetee, there's no part for "variables", as all must be resolved in "literals"... it is the compilation phase, in SQL Server as in other engines, that pepare the final statement, replacing variables with literals (where allowed by the host language/tool/engine) and finally passes the "final" result as output to the compiling phase.. tokenizer, algebrizer and other technologies come then into play.. but this is another story

regards

How to Sum non-duplicate values

Hi,
I have to create a report with a data as follows-
Voucher Branch Item ItemAmount
-- -- -- --
V1 Branch1 Item1 100
V2 Branch1 Item1 100
V3 Branch1 Item1 100
V4 Branch1 Item2 50
V5 Branch1 Item2 50
V6 Branch1 Item3 75
V7 Branch2 Item1 150
V8 Branch3 Item5 250
The table should have branch group and Sum(ItemAmount) per Branch.
I need to display ItemAmount totals per Branch but the totals should
consider only distinct ItemAmount per Item.
Total per Branch1 should be 100 + 50 + 75 = 225 When I say
Sum(ItemAmount, "Branch") in Branch group it's calculating 100 + 100 +
100 + 50 + 50 + 75 = 475.
Any suggestions would be appreciated.On Mar 16, 7:18 pm, "Chiru" <uchira...@.gmail.com> wrote:
> Hi,
> I have to create a report with a data as follows-
> Voucher Branch Item ItemAmount
> -- -- -- --
> V1 Branch1 Item1 100
> V2 Branch1 Item1 100
> V3 Branch1 Item1 100
> V4 Branch1 Item2 50
> V5 Branch1 Item2 50
> V6 Branch1 Item3 75
> V7 Branch2 Item1 150
> V8 Branch3 Item5 250
> The table should have branch group and Sum(ItemAmount) per Branch.
> I need to display ItemAmount totals per Branch but the totals should
> consider only distinct ItemAmount per Item.
> Total per Branch1 should be 100 + 50 + 75 = 225 When I say
> Sum(ItemAmount, "Branch") in Branch group it's calculating 100 + 100 +
> 100 + 50 + 50 + 75 = 475.
> Any suggestions would be appreciated.
I would suggest changing the report query (or stored procedure) to
something like this:
SELECT BRANCH, SUM(DISTINCT(ITEMAMOUNT))
FROM TABLE_X
GROUP BY BRANCH
Or, set up a separate dataset that has this query in it and use it for
the sums. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Developer

how to sum begining balance

Hi, i'm new in using crystal report 8.5 and SQL 7, now i'm trying to create report begining balance, see below details:
date item qty
12/1/05 001 150
12/12/05 002 200
12/23/05 003 250
1/1/06 001 10
1/12/06 002 15
1/23/06 003 20
2/10/06 001 100
2/15/06 002 150
2/27/06 003 200

how to sum begining balance quantity every month?
i want like these:

jan 06
beg item
150 001
200 002
250 003

feb 06
beg item
160 001
215 002
270 003

million thanks in advancegroup by date , for every month

u will get
dec 05
beg item
150 001
200 002
250 003

suppress dec 05 and put a formula in group header

dateadd('m',-1,suppresseddatefield)

hope it helps|||Sraheem thanks for your reply but sorry is not like i want i'm bit confius to describe, let me try like below:

Item Description Beg In Out End
1614 0.125 X 60 mm 346 131
1671 0.075x60mm 3,196 340
1626 Dia 0.85 mm 13,114 0

i need to create the formula for beg balance.
i have question, is it possible to create sum formula using three condition like sum ({qty},{item},cut off date)
in which to calculate beg balance I must sum qty until cut off date
then after i find beg balance i can calculate end balance.

How to store Resume and cover letter into SQL Server?

Hi,

I am trying to create an asp.net web recruiting application for HR, which will give the users the ability to both copy/paste the Resume and cover letter in textbox and upload resume and cover letter, then submit it (which will be saved into SQL Server 2000 table).

I am thinking to save Resume and cover_letter as Image data type columns in SQL Server.

. Can someone give me a direction about how to save the uploaded resume and cover letter to table and if it's the easiest way to do it?

. What to deal with different formats of uploaded resumes? I hope to limit to only Word or HTML

. Since I also give user another option - copy/paste the resume and coverletter into a textboxes. Can I simply save the copy/paste resume and cover letter into text field column? Later, say, if any HR recruiter retrieve the text from database, will it concatonates everything together without line break?

Any ideas is appreciated.

You have two options use NChar 4000 or NVarchar 4000 and limit the size of the uploaded file so you can use ANSI SQL %Like% in your search or use NText and use Full Text index which is an add on to SQL Server dependent on Microsoft Search service with the Microsoft Catalog populated all the time to get search results. Full Text index uses Microsoft proprietry key word search as CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE. Run a search for Full Text index implementation in SQL Server BOL(books online) Hope this helps.|||

You can save the resume in the database in an image field

//Create a Buffer Stream to Hold the uploaded file
string FileName = GetFileName(txtFileUpload.PostedFile.FileName.ToString());
string FileType = txtFileUpload.PostedFile.ContentType.ToString();
int FileSize = int.Parse(txtFileUpload.PostedFile.ContentLength.ToString());
byte[] DocBuffer = new byte[FileSize];
Stream objSream;
objSream = txtFileUpload.PostedFile.InputStream;
objSream.Read(DocBuffer,0,FileSize);
//Pass this buffer as a paramter to a Stored procedure which will insert a new record into ur table
SqlParameter pcvContent = new SqlParameter("@.cvContent",SqlDbType.Image);
pcvContent.Direction = ParameterDirection.Input;
pcvContent.Value = cvContent;
dbCommand.Parameters.Add(pcvContent);
//======================================================================
to retrive the saved file from the database you have to do the following
do your select statment
Response.AddHeader("Content-Disposition","attachment; filename='"+ dr["FileName"].ToString() +"'");
Response.ContentType = dr["FileType"].ToString();
Response.BinaryWrite( (byte[]) dr["CvContent"]);
//=========================================================================
this the first solution.
The secound one is to create a resume builder.

|||Personally, I would consider storing BOTH the raw document in an IMAGEcolumn, and ripping the text of the document and storing that in a TEXTcolumn. This will allow you to set up a full-text index forsearching the resumes, while at the same time making it easy for usersto get the resumes back in whatever format they were submitted in.

|||

hi,

I need to do a keyword search in word and pdf documents. I tried using Index Server linked with SQL Server but its giving me many problems. So i'm planning to rip off the text from the word or pdf docs and store the text in a TEXT field as u said in one of the threads so that i can do a search easily.

Is it possible to read from a word or a pdf file and store it in a string. Sorry about contacting u directly

Thanks in Advance

How to stop PAD_INDEX being scripted

Our team checks SQL scripts into version control. On my machine, however, the generated script always differs slightly. When I generate a create table script, the primary key part always has PAD_INDEX = OFF. Nobody else's server generates this. Does anyone know what option or setting I need to change to prevent PAD_INDEX = OFF from being output?

WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]

BOL specifies

When creating indexed views or manipulating rows in tables participating in an indexed view, seven SET options must be assigned specific values. The SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be ON. The SET option NUMERIC_ROUNDABORT must be OFF.

If any of these settings is different, data modification statements (INSERT, UPDATE, DELETE) on any table referenced by an indexed view fail and SQL Server raises an error listing all SET options that violate setting requirements. In addition, for a SELECT statement that involves an indexed view, if the values of any of the SET options are not the required values, SQL Server processes the SELECT without considering the indexed view substitution. This ensures correctness of query result in cases where it can be affected by the above SET options.

If the application uses a DB-Library connection, all seven SET options on the server must be assigned the required values. (By default, OLE DB and ODBC connections have set all of the required SET options correctly, except for ARITHABORT.)

|||

Thank you for taking the time to copy and paste. However, I don't see how your post is relevant.

I am asking what determines whether the PAD_INDEX option is included in scripted CREATE TABLE statements.

|||Is anyone able to help with this?