Menu
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?
894
The "String or binary data would be truncated" error indicates that the procedure is attempting to store something in the DBServerInfo table that is larger than the column allows. The two known reasons this can occur are: SQL Server has at least one database whose name exceeds 25 characters in length.
Solution. To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted.
How to fix “String or binary data would be truncated” The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size.
In databases and computer networking data truncation occurs when data or a data stream (such as a file) is stored in a location too short to hold its entire length.
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/
Update 2016
It seems Microsoft has tried to delete evidence of this bug's true age. Fair enough. Find the old site archived here.
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
