How to figure out which string or binary data woul

问题:

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?

回答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!!

回答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

回答3:

This will print your error message and store incorrect values in a global temp table. It's not ideal and will be applicable 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's 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 wanted to create a dynamic SQL solution or just don't want to type it out, try this to create your CASE statements and where clause

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

回答4:

This error is occurring due to less size for some column, but you are trying to insert more length of Text in to that column.

For Ex:

EMP_Name varchar(10)

and you are trying to insert/update

JOHN VOUGER

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