sql - 如何找出哪个字符串或二进制数据将被截断？

我有一个大多数时候都可以使用的存储过程，但是不时地，我收到一条错误消息:

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

如何确定哪个数据字符串导致了此问题？

最佳答案

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

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]

...我们创建以下数据...

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' )

...而引发错误的复杂查询是...

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

...然后我们可以找到像这样冒犯的列...

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

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

步骤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'

您可以调整此查询以筛选出某些列类型(您知道问题出在字符串或二进制数据上)以及源列的长度大于目标列的长度。有了这些信息，您应该只剩下几列可能会被截断并可以从那里开始搜索的列。

小费!检查目标列中的“插入触发器”!