如何确定哪些字符串或二进制数据会被截断？

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?

如何确定导致此问题的数据字符串?

4 个解决方案

#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...

步骤1将INSERT语句转换为SELECT INTO语句,并将结果写入临时表,如下所示...

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...

步骤2现在执行以下T-SQL,将目标表的列定义与复杂查询的源列进行比较...

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

小费!检查你的目标列是否有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:

假设您在执行上述查询后得到了column2的问题,即varchar的长度大于列长度。然后要查找特定值,您可以运行此查询:

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

如果您想创建动态SQL解决方案或者只是不想输入它,请尝试使用此方法创建CASE语句和where子句

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.

由于某些列的大小较小而发生此错误,但您尝试在该列中插入更长的Text。

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.

在上述情况下,这种考验会发生。因此,首先检查varchar列,如果可能,请增加列的大小。