How to Fix 'String or binary data would be truncated.'

by

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!

 


0
0

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Comment:  
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts


SQL Server Reporting Services can be a pain in the arse to set up correctly for the first time. Even after you've got things running correctly, you can sometimes run into issues, which is exactly what happened to me recently. I had setup my local...  more »

Recently, I decided to begin converting my Visual Studio.net Web Site Projects (WSP) to Web Application Projects (WAP) to take advantage of some of the new Web Deployment features in Visual Studio.net 2010 (see Vishal Joshi's blog for a run through of WSP...  more »

Here's one quick fix for the ASP.NET error System.FormatException: Input string was not in a correct format. when you are using a DataList with an asp:Button control and are trying to get the CommandArgument using the DataList_RowCommand. Keep in mind...  more »