How to determine which string or binary data will be truncated?

Question

I have a stored procedure that works most of the time, but from time to time I get an error message:

Msg 8152, Level 16, State 2, Line 98 String or binary data would be truncated. The statement has been terminated. 

How to determine which row of data causes this problem?

Answer 1

For this answer, which handles more complex selection queries well enough, suppose we have three tables defined as follows:

 CREATE TABLE [dbo].[Authors]( [AuthorID] [int] NOT NULL, [AuthorName] [varchar](20) NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Books]( [BookID] [int] NOT NULL, [AuthorID] [int] NOT NULL, [BookName] [varchar](20) NOT NULL ) ON [PRIMARY] CREATE TABLE [dbo].[Publications]( [BookID] [int] NOT NULL, [PublicationName] [varchar](10) NOT NULL, [AuthorID] [int] NOT NULL, [WrittenBy] [varchar](10) NOT NULL ) ON [PRIMARY] 

... and we create the following data ...

 INSERT INTO Authors ( AuthorID, AuthorName ) VALUES ( 1, 'BOB' ) INSERT INTO Authors ( AuthorID, AuthorName ) VALUES ( 2, 'JANE' ) INSERT INTO Authors ( AuthorID, AuthorName ) VALUES ( 3, 'SOREN LONGNAMESSON' ) INSERT INTO Books ( BookID, AuthorID, BookName ) VALUES ( 1, 1, 'My Life' ) INSERT INTO Books ( BookID, AuthorID, BookName ) VALUES ( 2, 2, 'Writing Long Titles For Dummies' ) INSERT INTO Books ( BookID, AuthorID, BookName ) VALUES ( 3, 3, 'Read Me' ) 

... and our complex query that throws an error ...

 INSERT INTO Publications SELECT Books.BookID, Books.BookName, Authors.AuthorID, Authors.AuthorName FROM Books JOIN Authors ON Books.AuthorID = Authors.AuthorID 

... then we can find columns that can be offensive like this ...

Step 1 Convert the INSERT statement to a SELECT INTO statement and write the results to a temporary table like this ...

 SELECT Books.BookID, Books.BookName, Authors.AuthorID, Authors.AuthorName INTO ##MyResults FROM Books JOIN Authors ON Books.AuthorID = Authors.AuthorID 

Step 2 Now run the following T-SQL to compare the definitions of the columns of the target table with the source columns of your complex query ...

 SELECT SourceColumns.[name] AS SourceColumnName, SourceColumns.[type] AS SourceColumnType, SourceColumns.[length] AS SourceColumnLength, DestinationColumns.[name] AS SourceColumnName, DestinationColumns.[type] AS SourceColumnType, DestinationColumns.[length] AS SourceColumnLength FROM tempdb.sys.syscolumns SourceColumns JOIN tempdb.sys.sysobjects SourceTable ON SourceColumns.[id] = SourceTable.[id] LEFT JOIN sys.syscolumns DestinationColumns ON SourceColumns.colorder = DestinationColumns.colorder LEFT JOIN sys.sysobjects DestinationTable ON DestinationColumns.[id] = DestinationTable.[id] WHERE SourceTable.Name = '##MyResults' AND DestinationTable.Name = 'Publications' 

You can adapt this query to filter to certain types of columns (you know that the problem is with string or binary data), and also where the length of the source column is longer than the destination columns. Armed with this information, you should be left with a few columns that could lead to truncation and start your search there.

TIP! Check destination columns for ON INSERT TRIGGERS !!

Answer 2

The problem is that one of the columns in the table is longer than the destination table.

To find the length of the column that might create the problem, you can run this query

 Select Max(Len(Column1)) --Take only varchar columns in this. , Max(Len(Column2)) , Max(Len(Column3)) From YourTable 

Now you can check the length of the row with the column length of the destination table. You will most likely find that any column is longer than the specified length in the column of the target table.

Suppose you understand that column2 has a problem after executing the above query, i.e. the length of your varchar is greater than the length of the column. Then, to find a specific value, you can run this query:

 select * from yourtable where len(column2)>20 --change 20 to the actual value of your column2 

Answer 3

This will print your error message and save the incorrect values ​​in the global temp table. It is not perfect and will be applied in all situations, but it works.

Our tables

 IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable; IF OBJECT_ID('tempdb..#valuesToBeInserted') IS NOT NULL DROP TABLE #valuesToBeInserted; CREATE TABLE yourTable ( ID INT IDENTITY(1,1), Col1 CHAR(2), Col2 VARCHAR(5), Col3 VARCHAR(10) ); GO SELECT * INTO #valuesToBeInserted FROM ( SELECT '12' col1,'12345' col2,'1234567890' col3 --good value UNION ALL SELECT '123','12345','1234567890' --bad value UNION ALL SELECT '12','123456','1234567890' --bad value ) A 

Actual solution

 BEGIN TRY INSERT INTO yourTable(Col1,col2,col3) SELECT * FROM #valuesToBeInserted END TRY BEGIN CATCH IF OBJECT_ID('tempdb..##TruncatedResults') IS NOT NULL DROP TABLE ##TruncatedResults; PRINT ERROR_MESSAGE() + CHAR(13) + 'Truncated values are in ##truncatedResults' SELECT CASE WHEN DATALENGTH(Col1) > 2 THEN 1 ELSE 0 END AS isCol1Truncated, CASE WHEN DATALENGTH(Col2) > 5 THEN 1 ELSE 0 END AS isCol2Truncated, CASE WHEN DATALENGTH(Col3) > 10 THEN 1 ELSE 0 END AS isCol3Truncated, * --lazy man select --col1, --col2, --col3 INTO ##truncatedResults --global temp table FROM #valuesToBeInserted WHERE DATALENGTH(Col1) > 2 OR DATALENGTH(Col2) > 5 OR DATALENGTH(Col3) > 10 END CATCH 

If you want to create a dynamic SQL solution or just do not want to enter it, try this to create your own CASE statements and where is the sentence

 DECLARE @caseStatement VARCHAR(MAX), @whereClause VARCHAR(MAX); SELECT @caseStatement = COALESCE(@caseStatement + ',','') + 'CASE WHEN ' + CONCAT('DATALENGTH(',COLUMN_NAME,') > ',CHARACTER_MAXIMUM_LENGTH) + ' THEN 1 ELSE 0 END AS Is' + COLUMN_NAME + 'Truncated', @whereClause = COALESCE(@whereClause,'') + CONCAT('DATALENGTH(',COLUMN_NAME,') > ',CHARACTER_MAXIMUM_LENGTH,' OR ') FROM INFORMATION_SCHEMA.COLUMNS WHERE CHARACTER_MAXIMUM_LENGTH > 0 AND TABLE_NAME = 'yourTable' SELECT @whereClause = 'WHERE ' + SUBSTRING(@whereClause,1,LEN(@whereClause) - 3) SELECT @caseStatement SELECT @whereClause 

Results:

 CASE WHEN DATALENGTH(Col1) > 2 THEN 1 ELSE 0 END AS IsCol1Truncated,CASE WHEN DATALENGTH(Col2) > 5 THEN 1 ELSE 0 END AS IsCol2Truncated,CASE WHEN DATALENGTH(Col3) > 10 THEN 1 ELSE 0 END AS WHERE DATALENGTH(Col1) > 2 OR DATALENGTH(Col2) > 5 OR DATALENGTH(Col3) > 10 

Answer 4

This error occurs due to the smaller size for some column, but you are trying to insert more text length into this column.

For Ex:

 EMP_Name varchar(10) 

and you are trying to insert / update

 JOHN VOUGER 

during the above case this happens. So, first check the varchar columns and, if possible, increase the size of the column.