String or binary data would be truncated

Microsoft, please fix thisThis issue is still out there, and once again, it’s the top voted on item for SQL Server. Everytime I see this:

Msg 8152, Level 16, State 13, Line 1
String or binary data would be truncated.

I’m annoyed, and I’m not alone. If you’ve had to load data into SQL Server, and you’ve every encountered this error, you’ve probably been frustrated. In fact, when you get this particular error, you might wonder what to do.

Many of us find this error to be a problem because we don’t know where the error is located. In fact, we often end up jumping through hoops to somehow track down the offending row. We might scan through our input file. Or we might build a parallel process to load the data into a series of MAX columns and then use T-SQL to search out the data, change it, and then export it again for us in our ETL process. Or we might throw up our hands in frustration and go for a walk.

SQL Server developers, if you feel any of these things, go vote for this item.

When there is a mismatch between our input data and the schema, there is a lot of friction to getting work done. While DBAs might be willing to track down the invalid rows, developers hate it and business people often get confused. This one reason why developers dislike relational schemas and look for easier data stores, like NoSQL databases. Trying to figure out that there’s one name in a list of 1000 that’s got 31 characters and doesn’t fit in a 30 character field is a pain for anyone..

I have no idea of the effort to fix this, but this isn’t a syntactical sugar item. Microsoft, this is a piece of work that can be extremely helpful. Pick a method to solve thisand get to work. Ignore the error rows and output them, let us redirect them to another table, or some up with some other solution. Make something simple that works, but please, just fix this.

It should be embarassing to you that this is still a issue that is reported and voted on in SQL Server.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.8MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged . Bookmark the permalink.