SQL Server: how do I find which column/row gave me an error?

Question

Does anyone have any hints on the best way to find the source of an conversion or truncation error such as:

• Error converting data type varchar to numeric.
• String or binary data would be truncated. The statement has been terminated.

When I'm inserting batches of data, I'll get these type of errors, and it then becomes a educated guessing game as to what column is having problems, and then what row in my data is the culprit. Any advice?

Answer 1

Here is my solution (probably one you've already considered):

Isolate a sample insert query and open in Management Studio.

Comment out the second half of the column inserts. If you still get an error, you definitely have a problem in the first half. Otherwise, it's in the second half.

Keep commenting out half of your search space (a binary search) until you find at least one of the offending columns.

Answer 2

Another thing to do is to pull the data into a separate work table with the wizard (set allthe datalengths to something large like 4000) and then do the following selct on each column:

select max(len(coloumn1)) from worktable

Not only can you see which fields are too big for your data structure, you have the data available to search for all the records which are too big in that field or you know the size that column you are inserting into will need to be to accomodate the data.