Multiple-step OLE DB operation generated errors

[Seabz420]

SQL Server 2000

I have a DTS that just copies the data from one database to the SQL server. No custom code involved. This works for all 69 other tables but this one table is giving me the error:

"Error at Source for Row number 1. Errors encountered so far in this task: 1.
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."

The table (at the source) consists of 3 fields. F1 is numeric (12, 4), F2 is numeric (12, 4) and F3 is numeric (2, 4). The destination table has each field set to decimal (12, 4) and decimal (2, 0) respectively. F1 is just 12 numbers (no decimal), F2 is just 12 numbers (no decimal) and F3 is just one number (no decimal).

Again, this exact procedure occurs on other tables with no problem but this one seems to have a problem. I can import the data to Access2003 and from there can import to SQL but this is not a viable option. Any ideas?
I used the Import Data wizard to create the table and the DTS (no custom code).

I am at a loss...help! Thanks!

 

[mutley1]

Hi Seabz420,

Is it possible that you have a null value or empty field in the source table and the destination is set to disallow nulls? It does sound like a datatype issue.

Cheers,

M.

 

[Seabz420]

Mutley,
I've checked the data over and no null or empty values exist. I suspect a datatype issue but similiar numeric fields in other tables transform to nvarchar just fine. To just test it, is there a datatype that will work for sure (either char or something..not char because it doesn't work either). I thought maybe the field size wasn't large enough because when I enter the precision and scale, the length still shows as 9. I have to set the precision to 20 to get the length to show above 12. This is driving me nuts. The strange thing is that the data imports into Access just fine and then goes from Access to SQL just fine.

 

[mutley1]

Hi Seabz,

Sorry if it's a silly question (but I don't know your setup!). Is there any reason the target fields are not the same datatypes as the source?

Rgds,

M.

 

[Seabz420]

Well, I tried the same datatypes but it wouldn't work so I figured SQL needed to transform the data to something else... When I made a DTS to create the table and import the data, the fields were created with the same datatypes as the source and it still didn't work.

 

[mutley1]

You could try another package as a 1 off for the one dodgy table, but use the "create destination table" option. Drop the table, run that DTS package which will create and transfer data, then if that works, revert to the full package if there is no data transformation within it.

 

[Seabz420]

Still receive the same error message. The table gets created just fine but then it errors out when it tries to transfer it. One question is what is length in reference to Scale and Precision? The length doesn't seem to change when I change the other two, unless I make a drastic change to Precision.

 

[Seabz420]

Do you think something like this may be the issue here?

http://donxml.com/allthingstechie/archive/2006/01/05/2410.aspx

 

[Seabz420]

So I tried to take the source data and import it into a txt file using SQL 2000 but even that errors out!

 

[Seabz420]

OK, the data that I need to store are numbers ranging from 100,000,000,000 to 300,000,000,000. That pretty much rules out using any type of numeric data type, correct? It still doesn't explain why varchar doesn't work though.

 

[mutley1]

Hey Seabz

The table (at the source) consists of 3 fields. F1 is numeric (12, 4), F2 is numeric (12, 4) and F3 is numeric (2, 4).

I'm assuming the 2 numbers are precision and scale in that order? If so, isn't precision of 2 and scale of 4 a bit strange. My understanding is precision is the total amount of digits and scale is the number of digits after the decimal point, so that would imply total digits 2 but 4 after the decimal, or have I got that wrong?

 

[Seabz420]

You are correct, which makes this seem strange as I'm told that the source field is really (2,0) yet SQL is trying to read it as (2,4).

 

[mutley1]

I'd stick the tables on a test system and try amending the precision / scale and see if there are any adverse effects. I think this is what's the problem.

Cheers,

M.

 

[Seabz420]

I have no control over how the source tables are setup, nor do I have access to them (outside of data retrieval). I've mananged to work around the issue (by going in a completely different direction with this) but I'll keep all this in mind as our sister organizations will eventually run into this.