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

发布时间：2020-12-12 07:25:29 所属栏目：MsSql教程来源：网络整理

导读：我有一个大多数时间都可以运行的存储过程,但是每次都会收到一条错误消息： Msg 8152,Level 16,State 2,Line 98String or binary data would be truncated.The statement has been terminated. 如何确定导致此问题的数据字符串？解决方法对于这个能够很好地

我有一个大多数时间都可以运行的存储过程,但是每次都会收到一条错误消息：

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,[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,BookName ) VALUES ( 2,2,'Writing Long Titles For Dummies' )
INSERT INTO Books ( BookID,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,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'

您可以调整此查询以过滤到某些列类型(您知道问题是字符串或二进制数据)以及源列的长度大于目标列的位置.有了这些信息,您应该只留下一些可能导致截断的列,并可以从那里开始搜索.

小费！检查您的目标列是否有ON INSERT TRIGGERS！

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!