SQLTeam.com | Weblogs | Forums

Quick test for which column overflowed on insert


#1

Is there a fast/easy way to figure out what field is overflowing a column on an insert?

I have a stored procedure that creates a temp table with a ton of columns, and fills it with a select and does some data transformations and spits out a list of records. It's been working forever and then this morning it failed due to a "string or binary data would be truncated" error.

I ran the select query and didn't see any obvious issues so I increased the size of the columns in the temp table one by one and kept re-running it. I started with the obvious name and description columns but it kept erroring out. So I finally just ran the select part of the SP and dumped the results to Excel, inserted blank columns next to each field and did an =LEN(A1) etc on all of them and then sorted by every column until I found the one that was a single character longer than the temp table (someone increased a field in a transaction table and didn't mention it). This was very manual and annoying so I'm hoping there's some sort of trick that could be useful in tracking down the problem.

The temp table is being loaded from a bunch of different tables so it still would have taken a while to go through the create, insert, select, and object explorer to compare column type/lengths.

Thanks


MySQL - Choose Something Else
#2

Unfortunately, Microsoft does not provide any assistance in identifying the offending column or row, even though they surely must have that information. In fact, there is a connect issue here regarding this (submitted way back in 2008, and for which Microsoft responded by saying, "sure, we will improve it").

If you read through the comments in that connect issue, specifically the post by AmirCharania, there is sort of a workaround to identify the offending column.