SQL : “String or binary data would be truncated” Error

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:

select top 1 COLUMN
from table t
order by len(COLUMN) desc
Just changed “COLUMN” to the name of each column of data I need to check until I found all offending data. Very simple and easy to tweak as needed. I found plenty of complex solutions; but sometimes all it takes is a simple solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s