How to Fix 'String or binary data would be truncated.'
December 29, 2009
Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated.
There's a good chance that if you're database driven application is running into the following sql error message that says "String or binary data would be truncated", that the error is being caused by an issue in a SQL statement or in the SQL code of a Stored Procedure. Here's the SqlException error message that I received recently:
String or binary data would be truncated.
The statement has been terminated.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.
Since the error message is not totally specific, you will have to dig into the SQL code/ Stored Procedure to see what may be causing the issue. In my case, this "String or binary data would be truncated" error message was caused when creating a Table Variable to contain search results that had a column named "Title" with the data type NVARCHAR(100), which was smaller than my actual Articles table column "Title" with a data type NVARCHAR(140).
Essentially, because the Table Variables column "Title" NVARCHAR(100) was smaller than my Articles table column "Title" NVARCHAR(140), when I tried to do a INSERT INTO my Table Variable, it would give me the SqlException, because data in the "Title" column of my Articles table was larger than NVARCHAR(100).
Keep in mind, that I only discovered this error after the data in the "Title" column of my Articles Table exceeded the Table Variables "Title" column NVARCHAR(100). During testing, I never had input more than (100) characters in the "Title" column of my Articles table so the stored procedure had actually worked without giving me an SqlException error message.
So if you DECLARE any #Temp tables or @Table Variables be sure that the data types size is the same as your actual Table, so that when you INSERT data from the actual table into the #Temp table or @Table Variable, the columns will be large enough to hold the inserted data.
At any rate, hopefully this explanation will help you debug your "String or binary data would be truncated." error message. Good luck!