SQL Server Portal.
Real world SQL scenarios & its unique & optimized solutions.
SQL SERVER – A quick solution to ‘String or binary data would be truncated’ using Stored procedure.
String or binary data would be truncated (Error number 8152) is a very common error. It usually happens when we try to insert any data in string (varchar,nvarchar,char,nchar) data type column which is more than size of the column. So you need to check the data size with respect to the column width and identify which column is creating problem and fix it. It is very simple if you are dealing with less columns in a table. But it becomes nightmare if you are dealing with inert into query with huge number of columns and you need to check one by one column. I received this query from one of my Blog readers Mr Ram Kumar asking if there is a shortcut to resolve this issue and give the column name along with the data creating problems. I started searching for the solution but could not get proper one. So I started developing this solution. Before proceeding with the solution, I would like to create a sample to demonstrate the problem.
SAMPLE :
Msg 8152, Level 16, binary options State 14, Line 1 String or binary data would be truncated. The statement has been terminated. Msg 8152, Level 16, State 14, Line 2 String or Binary Options binary data would be truncated. The statement has been terminated.
SOLTUION : Given below is the stored procedure that can find the exact column name and its data which is exceeding the limit of column width.
EXAMPLE : Now, you simply need to replace all single quotes of your insert into query to double quotes and https://po.cash/smart/j9IBCSAyjqdBE7 pass it into the stored procedure. Given below is the sample.
As you can see above, it returned only the column name(s) whose data sizes exceed the limit of the column width. Do let me know if you come across situation like that and resolve it in a different ways.