binary or string data would be truncated => error message enhancments
if you have that error (in production), it's not obvious to see which column or row this error comes from, and how to locate it exactly.
it's even more complicated if it's coming from a trigger, for example, and not the original insert/update.
I have seen that oracle meanwhile gives clear information which column the error is raising, which actual data length was passed, and the column's max size.
This feature is shipped in SQL Server 2017 CU12, SQL Server 2019 CTP and upcoming SQL Server 2016 CP2 CU. Read more at https://blogs.msdn.microsoft.com/sql_server_team/string-or-binary-data-would-be-truncated-replacing-the-infamous-error-8152/
Robert Wine commented
Yeah, that's a serious lack of information that takes WAY TOO LONG when you've got a large recordset. Please at least give me a field name if you won't give me a record number.
Consider a switch or option to allow the truncation to occur and to load the truncated data. Still would be nice to know which rows/keys contained the truncated data after the process competes with a warning error.
PLEASE make this a more useful message. The name of the offending column would help a lot.
This needs to be fixed. It's ridiculous this type of error message is still around.
Ganesh Muthuvelu commented
SQL Server 7, 2000, 2005, 2008, 2012, 2014, 2016, 2017
Many number of versions, many decades passed, billions of hours wasted and Microsoft still has not found a way to show a better message..
At least say which "column" has the issue.. Try loading a 250 column, 10 million rows and you get this message - it will feel like end of the world..!!
please do something.!
Yousof Zaatari commented
this is not supposed to be treated as a feature. This is by all means a core user requirement.
Either tell us the offending row and column or give us a flag to set to say we just don't care, go ahead and truncate the data.
Brad Pears commented
Really Microsoft? And Oracle has it figured out?? You should be ashamed of yourselves. It's resolving little things like this that will win people over and keep them on your platform.
when this error is on the SSIS pkg, it shows the exact column name.
C Faria commented
This gets my vote too. Why not improve on the error message making it more clearer with details on which record and column.
I've always thought this was among the worst error messages I have ever encountered as a developer. This one has been there ever since I started using SQL Server 6.5 back in 1996! Now it's 22 years later and it still exists so my hope in MS doing anything about it died a long time ago. I'm sure there have been a number of programmers at MS who have wanted to fix this, but have been told not to. I would even guess that they have the code to do it and have demonstrated it to a manager who ignores it.
Feels like a lifetime...seeing this message & wasting so much time getting around it. Would be a real step forward to have it dealt with! Thanks
rahul hundare commented
you need to check column size of both source and destination tables
Troy Truax commented
I get that having an option to simply chop off the extra data isn't the best way to handle this but at least change the error message to tell me which column it is!
The fact that this bug is still alive in 2018 is beyond ridiculous.
You know a column may be clipped, why not report that column name?
Brian J Battles commented
Why is this even an issue? Obviously deep down inside the "guts" of SQL Server "know" what column and row is causing the error, because that's how it can throw an error in the first place (duh). So why not simply pass that info back up to the interface to let us deal with it? A no brainer, Microsoft!
Larry P. brown commented
I agree this needs a fix. My most recent encounter with it took WAY too much time to resolve.
How about removing the length requirement of string data altogether and treat strings in SQL the same as a string in C#. but if its insisted to treat strings with length constraints then allow us to set an option that would either; skip rows that would be truncated, truncate data that exceeds the limit and/or redirect row to an error table/file.
my recent etl db design work reluctantly using nvarchar(max) just to avoid this painful error; for dozens of tables, hundreds of columns - we could not afford to risk this error occurring during large volume loads. Yes please fix - at very least dump the offending record info to Event log.
This should be an embarrassment for MS. Just give us the detailed info that would allow us to fix this instead of having to spend an insane amount of time jumping through every hoop in the circus to complete a task that should be completed rather quickly.