SQL Server Error: String or binary data would be truncated
-
Hi, guys We encountered a SQL server error message saying, "String or binary data would be truncated". I know it's because some new records to be inserted into a table has value exceed the limit of the column's definition, eg, the value to be inserted is something like "abcdef", but the column's definition is something like "varchar(3)". The problem is, there are more than 10 tables that have been inserted new records in that stored proc, and each has huge amount of columns. Is there a way to let SQL Server tell exactly which table and which column it complains about? So I can avoid the extremely tedious task of using the same problematic data to examine each column? Thanks very much,
-
Hi, guys We encountered a SQL server error message saying, "String or binary data would be truncated". I know it's because some new records to be inserted into a table has value exceed the limit of the column's definition, eg, the value to be inserted is something like "abcdef", but the column's definition is something like "varchar(3)". The problem is, there are more than 10 tables that have been inserted new records in that stored proc, and each has huge amount of columns. Is there a way to let SQL Server tell exactly which table and which column it complains about? So I can avoid the extremely tedious task of using the same problematic data to examine each column? Thanks very much,
I don't know, so I Googled for
"String or binary data would be truncated". One of the hits[^] looks promising, especially the reply by Axos_Tech. :)Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
-
I don't know, so I Googled for
"String or binary data would be truncated". One of the hits[^] looks promising, especially the reply by Axos_Tech. :)Luc Pattyn [Forum Guidelines] [My Articles] Nil Volentibus Arduum
Please use <PRE> tags for code snippets, they preserve indentation, improve readability, and make me actually look at the code.
Thanks, but I cannot see how Axos_Tech's words actually help me in our situation. The post you gave has some words I quite agree: "This is notorious error in SQL." and "didn’t find any elegant way of finding this error." I think, this is one thing MS's dev failed the good practice here :)
-
Hi, guys We encountered a SQL server error message saying, "String or binary data would be truncated". I know it's because some new records to be inserted into a table has value exceed the limit of the column's definition, eg, the value to be inserted is something like "abcdef", but the column's definition is something like "varchar(3)". The problem is, there are more than 10 tables that have been inserted new records in that stored proc, and each has huge amount of columns. Is there a way to let SQL Server tell exactly which table and which column it complains about? So I can avoid the extremely tedious task of using the same problematic data to examine each column? Thanks very much,
Since it's an error you should get more info along the message. The whole message should include also the row number for the error so using you should be able to locate the statement. An example of the error:
Msg 8152, Level 16, State 14, Line 11
String or binary data would be truncated.So in that case the error was in the statement at line 11. On the other hand, by using try-catch[^] block you can catch the error and produce more information about the error (like the statement, parameter values etc) for example using RAISERROR[^] statement
The need to optimize rises from a bad design.My articles[^]