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 :
--This script is compatible with SQL Server 2005 and above. --DROP TABLE tbl_sample --GO CREATE TABLE tbl_sample ( [ID] INT, [NAME] VARCHAR(10), ) GO INSERT INTO tbl_sample VALUES (1,'Bob Jack Creasey') GO INSERT INTO tbl_sample ([ID],[NAME]) VALUES (2,'Frank Richard Wedge') GO --OUTPUT
Msg 8152, Level 16, 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 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.
--DROP PROCEDURE usp_String_or_binary_data_truncated
--GO
CREATE PROCEDURE usp_String_or_binary_data_truncated
@String VARCHAR(MAX)
AS
DECLARE @VARCHAR AS VARCHAR(MAX)
DECLARE @Xml AS XML
DECLARE @TCount AS INT
SET @String= REPLACE(REPLACE(REPLACE(REPLACE(@String,'''','')
,'[',''),']',''),CHAR(13) + CHAR(10),'')
SET @Xml = CAST(('<a>'+REPLACE(@String,'(','</a><a>')
+'</a>') AS XML)
SELECT @TCount=COUNT(*)
FROM @Xml.nodes('A') AS FN(A)
;WITH CTE AS
(SELECT
(CASE
WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))>0)
THEN 1
WHEN CHARINDEX('VALUES',A.value('.', 'varchar(max)'))>0
THEN 2
WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
AND @TCount=2 THEN 2
WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
AND @TCount=3 THEN 3
END) AS[Batch Number],
REPLACE(REPLACE(A.value('.', 'varchar(max)')
,'INSERT INTO',''),'VALUES ','') AS [Column]
FROM @Xml.nodes('A') AS FN(A))
, [CTE2] AS
(
SELECT
[Batch Number],
CAST('' + REPLACE([Column], ',' , '')
+ '' AS XML)
AS [Column name And Data]
FROM [CTE]
)
,[CTE3] AS
(
SELECT [Batch Number],
ROW_NUMBER() OVER(PARTITION BY [Batch Number]
ORDER BY [Batch Number] DESC) AS [Row Number],
Split.a.value('.', 'VARCHAR(MAX)') AS [Column name And Data]
FROM [CTE2]
CROSS APPLY [Column name And Data].nodes('/M')Split(A))
SELECT
ISNULL(B.[Column name And Data],C.name) AS [Column Name]
,A.[Column name And Data] AS [Column Data]
,C.max_length As [Column Length]
,DATALENGTH(A.[Column name And Data])
AS [Column Data Length]
FROM [CTE3] A
LEFT JOIN [CTE3] B
ON A.[Batch Number]=2 AND B.[Batch Number]=3
AND A.[Row Number] =B.[Row Number]
LEFT JOIN sys.columns C
ON C.object_id =(
SELECT object_ID(LTRIM(RTRIM([Column name And Data])))
FROM [CTE3] WHERE [Batch Number]=1
)
AND (C.name = B.[Column name And Data]
OR (C.column_id =A.[Row Number]
And A.[Batch Number]<>1))
WHERE a.[Batch Number] <>1
AND DATALENGTH(A.[Column name And Data]) >C.max_length
AND C.system_type_id IN (167,175,231,239)
AND C.max_length>0
GO
EXAMPLE :
Now, you simply need to replace all single quotes of your insert into query to double quotes and pass it into the stored procedure.
Given below is the sample.
EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample VALUES (1,''Bob Jack Creasey'')' GO EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample ([ID],[NAME]) VALUES (2,''Frank Richard Wedge'')' GO --OUTPUT
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.
SQL Server Portal 
It doesn’t work for me. This procedure doesn’t show any results, although there are columns that exceed the maximum length
Hi Alvaro,
Can you please send the sample table along with the insert query.
I can test and update you accordingly.
Thanks,
Imran
Hi Imarn, I have used sample table and stored proc provided by you ,but its not working for me ,its not showing any output
before inserting values use
SET ANSI_WARNINGS OFF
it works for me
Where exactly are you telling to put SET ANSI_WARNINGS OFF.
Are you saying put before this :-
EXEC usp_String_or_binary_data_truncated ‘INSERT INTO tbl_sample VALUES (1,”Bob Jack Creasey”)’
GO
EXEC usp_String_or_binary_data_truncated ‘INSERT INTO tbl_sample ([ID],[NAME]) VALUES (2,”Frank Richard Wedge”)’
GO
If yes, i am getting this error :-
Msg 1934, Level 16, State 1, Procedure usp_String_or_binary_data_truncated, Line 18
SELECT failed because the following SET options have incorrect settings: ‘ANSI_WARNINGS’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
Msg 1934, Level 16, State 1, Procedure usp_String_or_binary_data_truncated, Line 18
SELECT failed because the following SET options have incorrect settings: ‘ANSI_WARNINGS’. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
sir,submitting a compititive form this error is occur”Server Error in ‘/’ Application.
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.
The statement has been terminated.
Source Error:
Line 157: Using conn As SqlConnection = OpenConnection()
Line 158: Dim cmd As SqlCommand = CreateCommand(conn, text, args)
Line 159: cmd.ExecuteNonQuery()
Line 160: End Using
Line 161: End Sub
Source File: C:\Inetpub\vhosts\usetonline.org\httpdocs\App_Code\SQL_DB.vb Line: 159
Stack Trace:
Hi Ramgopal,
What do you mean by “a competitive form”?
Are you a programmer or just using some kind of general software and it’s given you this error?
I also added “SET ANSI_WARNINGS OFF” to the top of my command, just above and linked to the one I wanted to execute.
SET ANSI_WARNINGS OFF was needed because of the presence of NULL values in my email fields. The SET ANSI_WARNINGS OFF command allows the query to run without being stopped.
THANKS!
I get this error on my data upload:
Error MSG:String or binary data would be truncated.
The statement has been terminated.On line no: 1328 Field name:NA
What doesn’t make sense is, my upload only has 1327 lines. Tried back space and saving the data, but it gives me the same error.
[…] String or binary data would be truncated – SQL Server … – SQL SERVER – A quick solution to ‘String or binary data would be truncated’ using Stored procedure. January 3, 2014 by Muhammad Imran […]
SET ANSI_WARNINGS OFF before my insert query fixed it for me.
how ?Still shows the same error
its not working
Don’t use SET ANSI_WARNINGS OFF it’s a quick fix but you should loose the characters while selecting a statements if Varchar(20) it may show only varchar(10) it truncates the string
hi…
when i upload a file..i get this error .”String or binary data would be truncated. The statement has been terminated.”..
i dono what can i do…
Made some changes to the stored proc,
–DROP PROCEDURE usp_String_or_binary_data_truncated
–GO
CREATE PROCEDURE [dbo].[usp_String_or_binary_data_truncated]
@String VARCHAR(MAX)
AS
DECLARE @VARCHAR AS VARCHAR(MAX)
DECLARE @Xml AS XML
DECLARE @TCount AS INT
SET @String= REPLACE(REPLACE(REPLACE(REPLACE(@String,””,”)
,'[‘,”),’]’,”),CHAR(13) + CHAR(10),”)
SET @Xml = CAST((‘‘+REPLACE(@String,'(‘,’‘)
+’‘) AS XML)
SELECT @TCount=COUNT(*)
FROM @Xml.nodes(‘A’) AS FN(A)
;WITH CTE AS
(SELECT
(CASE
WHEN (CHARINDEX(‘INSERT INTO’,A.value(‘.’, ‘varchar(max)’))>0)
THEN 1
WHEN CHARINDEX(‘VALUES’,A.value(‘.’, ‘varchar(max)’))>0
THEN 2
WHEN (CHARINDEX(‘INSERT INTO’,A.value(‘.’, ‘varchar(max)’))=0
AND CHARINDEX(‘VALUES’,A.value(‘.’, ‘varchar(max)’))=0)
AND @TCount=2 THEN 2
WHEN (CHARINDEX(‘INSERT INTO’,A.value(‘.’, ‘varchar(max)’))=0
AND CHARINDEX(‘VALUES’,A.value(‘.’, ‘varchar(max)’))=0)
AND @TCount=3 THEN 3
END) AS[Batch Number],
REPLACE(REPLACE(A.value(‘.’, ‘varchar(max)’)
,’INSERT INTO’,”),’VALUES ‘,”) AS [Column]
FROM @Xml.nodes(‘A’) AS FN(A))
, [CTE2] AS
(
SELECT
[Batch Number],
CAST(” + REPLACE([Column], ‘,’ , ”)
+ ” AS XML)
AS [Column name And Data]
FROM [CTE]
)
,[CTE3] AS
(
SELECT [Batch Number],
ROW_NUMBER() OVER(PARTITION BY [Batch Number]
ORDER BY [Batch Number] DESC) AS [Row Number],
Split.a.value(‘.’, ‘VARCHAR(MAX)’) AS [Column name And Data]
FROM [CTE2]
CROSS APPLY [Column name And Data].nodes(‘/M’)Split(A))
SELECT
ISNULL(A.[Column name And Data],C.name) AS [Column Name]
,B.[Column name And Data] AS [Column Data]
,C.max_length As [Column Length]
,DATALENGTH(B.[Column name And Data])
AS [Column Data Length]
FROM [CTE3] A
LEFT JOIN [CTE3] B
ON A.[Batch Number]=2 AND B.[Batch Number]=3
AND A.[Row Number] =B.[Row Number]
LEFT JOIN sys.columns C
ON C.object_id =(
SELECT object_ID(LTRIM(RTRIM([Column name And Data])))
FROM [CTE3] WHERE [Batch Number]=1
)
AND (C.name = LTRIM(RTRIM(A.[Column name And Data]))
OR (C.column_id =A.[Row Number]
And A.[Batch Number]1))
WHERE a.[Batch Number] 1
AND DATALENGTH(B.[Column name And Data]) >C.max_length
AND C.system_type_id IN (167,175,231,239)
AND C.max_length>0
GO
This one works for me.
Put SET_ANSI OFF
SET_ANSI Warning off