While supporting a number of MS SQL servers that I took over as DBA this year, I ran into a scheduled job that handles a nightly data feed started failing. After reverse engineering exactly how this job worked, I found a specific stored procedure failing with the “String or binary data would be truncated” error.

What does this mean? Basically, the process is trying to run an insert query and is trying to insert data into a column that is not big enough. For example, say I have a column defined as type nvarchar(3) and I’m trying to insert ‘testing’ into that column. This will generate this error as ‘testing’ is 7 characters and that column is limited to 3.

Solution! I have two options, ignore the error and let SQL truncate the data; or I can find the offending data increase the size of the destination column.

Ignoring the error by adding “SET ANSI_WARNINGS OFF” to the query will get me around the error; but be warned it will truncate the data and is not recommended.

This leaves increasing the size of the destination column. However, when my source data has over 100k rows of data and my destination table has over 35 columns, that is easier said then done. I could just increase the size of every column in my destination table; but that is overkill in my case. I know there are likely more elaborate ways of going about finding the offending data; but due to time, I needed to find the offending data quick. While this query is very simple, it worked perfectly to help me find the offending data: