Why isn't "String or Binary data would be truncated" a more descriptive error?

Question

To start: I understand what this error means - I'm not attempting to resolve an instance of it.

This error is notoriously difficult to troubleshoot, because if you get it inserting a million rows into a table 100 columns wide, there's virtually no way to determine what column of what row is causing the error - you have to modify your process to insert one row at a time, and then see which one fails. That's a pain, to put it mildly.

Is there any reason that the error doesn't look more like this?

String or Binary data would be truncated
Error inserting value "Some 18 char value" into SomeTable.SomeColumn VARCHAR(10)

That would make it a lot easier to find and correct the value, if not the table structure itself. If seeing the table data is a security concern, then maybe something generic, like giving the length of the attempted value and the name of the failing column?

Answer 1

Microsoft is lazy?

You don't have to try each row insert separately, by the way. Just query for max(len(field)) for each text column, starting with the ones you suspect might be the culprit.

Answer 2

Know your data and tables?

At least you get an error: pretty much everywhere else it would be silently truncated... no?

Answer 3

Short Answer: That's just how it is.

Longer Answer: I could see value in showing the row number and column, maybe but it probably wouldn't make sense to show the actual information being truncated. With the VARCHAR(10) scenario, it's probably not a big deal, but excessively large size data would be a whole lot useful. But hopefully no one here is inserting anything more than a VARCHAR(MAX) can hold ;)

Answer 4

Descriptive error messages in software systems are as good as non-existant.

That holds not only for DBMS's but for as good as any kind of software one can imagine.

I think the underlying reason is that "good descriptive error messages" take too much time to implement. It is not part of the average software developer's culture to spend much time thinking about "which information would the user want to see if this particular kind of exception occurs" ? The programmers who have to write down the code for giving "good descriptive error messages" only see the cost (their time), not the benefit.

One of the most recent error messages I got from a software system is "Something wrong has happened. Please try again later.". No kidding.

Answer 5

It turns out there's an open "feature request" for this on MS Connect - I'd encourage you to vote for it if you'd like the functionality changed.

https://connect.microsoft.com/SQLServer/feedback/details/339410/

ADDED:

It actually looks like there's another request for this same feature (though poorly named) that's been outstanding since Yukon's development in 2005 that I'd encourage people to vote for as well:

https://connect.microsoft.com/SQLServer/feedback/details/125347/