How to figure out which string or binary data would be truncated?

Question

I have a stored procedure which works most of the time, but every now and again, 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 do I figure out which data string is causing this issue?

Answer 1

For this answer, which handles more complex select queries quite well, let's assume 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 is throwing the error is...

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 the columns that are likely to be offending like this...

Step 1 Convert your INSERT statement into 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 execute the following T-SQL to compare the column definitions of your destination 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 down to certain column types (you know the problem is with string or binary data) and also where the length of the source column is greater than the destination columns. Armed with this information you should be left with only a few columns that could possible cause truncation and can start your search from there.

TIP! Check your destination columns for ON INSERT TRIGGERS!!

Answer 2

The issue is clear that one of your column in the table is having a length more than the destination table.

To find the length of the column which might be creating the issue 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 string with the column length of your destination table. Most probably you will find any one column is having a length more than the specified length your destination table column.

Lets say you get that the column2 has the issue after executing the above query ie the length of your varchar is more than the column length. Then to find the specific value you can run this query:

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