如何确定哪些字符串或二进制数据将被截断? [英] How to figure out which string or binary data would be truncated?

问题描述

我有一个大部分时间都可以工作的存储过程，但我时不时地收到一条错误消息:

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?

推荐答案

对于这个答案，它可以很好地处理更复杂的选择查询，假设我们有三个表定义如下...

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

步骤 1将您的 INSERT 语句转换为 SELECT INTO 语句并将结果写入这样的临时表...

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

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

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.

提示！检查目标列是否有 ON INSERT TRIGGERS！！

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

这篇关于如何确定哪些字符串或二进制数据将被截断?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！