August 17, 2010 at 3:52 pm
Greetings all,
I have encountered this problem in the past, and today it reared its ugly head again in my Datawarehouse loads this morning. Basically what happened is a bad value was passed into our ERP system (where our datawarehouse gets its source data) and subsequently, when it went to load into one of the datawarehouse tables I got an arithmetic overflow message. Typically I use one of two methods to isolate the issue; if the table is small I will simply do a max(int_column_Name) on each of the int columns from the source and compare the results to the decimal/int types in the destination table. If the table is large then I use a binary reduction trick an old friend taught me by starting a transaction and commenting out half the columns and running the insert to see if it fails, reducing each time by half until I finally find the problematic column. Each method takes a good deal of time, especially on the table today since its roughly 5 million records, and running an insert statement, rolling back the transaction and starting a new one has its potential human error issues.
So I am wondering...is there an easier method for isolating WHICH column has an arithmetic overflow or string truncation issue? It seems absurd that SQL Server is smart enough to know there was an issue inserting a specific data type into a column, but fails to tell you which one. It seems to me there should be an error capturing method that would isolate this or something to assist on this...
Link to my blog http://notyelf.com/
August 18, 2010 at 8:32 am
shannonjk (8/17/2010)
Greetings all,I have encountered this problem in the past, and today it reared its ugly head again in my Datawarehouse loads this morning. Basically what happened is a bad value was passed into our ERP system (where our datawarehouse gets its source data) and subsequently, when it went to load into one of the datawarehouse tables I got an arithmetic overflow message. Typically I use one of two methods to isolate the issue; if the table is small I will simply do a max(int_column_Name) on each of the int columns from the source and compare the results to the decimal/int types in the destination table. If the table is large then I use a binary reduction trick an old friend taught me by starting a transaction and commenting out half the columns and running the insert to see if it fails, reducing each time by half until I finally find the problematic column. Each method takes a good deal of time, especially on the table today since its roughly 5 million records, and running an insert statement, rolling back the transaction and starting a new one has its potential human error issues.
So I am wondering...is there an easier method for isolating WHICH column has an arithmetic overflow or string truncation issue? It seems absurd that SQL Server is smart enough to know there was an issue inserting a specific data type into a column, but fails to tell you which one. It seems to me there should be an error capturing method that would isolate this or something to assist on this...
are you using SSIS to load data?
August 18, 2010 at 10:49 am
Yes and no. Yes SSIS executes the package, but the package is a stored procedure on the database which executes the Insert statement.
Link to my blog http://notyelf.com/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply