SQL Server字符串或二进制数据将被截断

Question

我参与了一个数据迁移项目。当我尝试将一个表中的数据插入到另一个表中（SQL Server 2005）时，出现以下错误：

Msg 8152，Level 16，State 13，Line 1
字符串或二进制数据被截断。

源数据列与目标表列的数据类型相匹配，并且在长度定义范围内，因此我不知道是什么原因导致了这个错误。

- Jim Evans

---

您能否发布一些代码以及每个表的相关信息？ - Kevin Mansel

这些表格都非常大，因此我只会发布涉及到的表格定义和代码部分，这样可以吗？ - Jim Evans

表格定义和代码都很好。 - IAmTimCorey

上次我遇到这个问题时，是由触发器引起的。该触发器将数据插入审计表中。建议也检查一下触发器。 - Sachin Vishwakarma

Answer 1

您需要发布源表和目标表的表定义，以便我们确定问题出在哪里，但问题的关键是源表中的某列比目标列更大。可能是您以一种您不知道的方式更改了格式。您正在移动的数据库模型也很重要。

- IAmTimCorey

---

我曾经遇到过同样的问题，不得不比较两个表的所有列类型和大小来解决这个问题。 - Aziz Shaikh

经过收集部分表定义并获取我的存储过程代码的练习后，冒犯的列像闪电一样跳了出来...感谢大家的参与。 - Jim Evans

SQL不告诉你是哪一列导致问题真的很棒。为了效仿这一妙招，我开始从所有错误信息中删除有用的信息。 - A.R.

对我来说，这主要是因为MS SQL在创建语句中创建一个没有给定长度的“varchar”字段，长度为1，这只是无用的。所以我会检查表DDL中是否有“varchars（1）”...或者你的数字太大了，不能用int / bigint处理... - A. Rabus

@A.R. 这个问题已经在 SQL 2017 中得到修复。 - Ian Boyd

请参考@Lukasz Szozda的答案，了解如何在错误信息中获取有问题的表格和列。 - Mick

Answer 2

正如其他人已经说过的那样，在源表中，你的某个列数据类型比目标列要大。

一个简单的解决方案是关闭警告并允许截断发生。因此，如果你收到了这个错误，但是你确定在你的旧数据库/表中截断数据（将其裁剪至适当大小）是可以接受的，那么你只需执行以下操作：

SET ANSI_WARNINGS OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;

同上，记得在完成后重新打开警告。

- Rudi Kershaw

---

同样的情况也存在于这里。有时候我需要从一个只定义为“字符串”的 Web 服务中将数据存储到表格中。但我不能把所有东西都定义为 Varchar(MAX)... - Curt

这个并没有起作用，但我得到了结果，让我能够解决问题（增加varchar长度）！所以谢谢。 - JosephDoggie

这绝对不是大规模数据迁移的最佳方案，但对我来说足够好了，可以“调试”我的查询，注意到其中一个列确实在裁剪输入的字符串。在我的特定情况下，这是最快的行动方案。 - Mxpsth

是的，我的也是一个Web服务调用，所以对于我的情况来说这样很好。 - UnskilledCoder

我遇到了与2016年数据库相同的问题，但是插入的数据不会比目标列更大。就好像SQL在执行WHERE子句之前，先查看源表/列并抛出错误一样。 - Russ R

作为后续处理：即使将SELECT语句放入CTE中，我仍然会遇到错误。 但是如果先将数据加载到临时表中，INSERT语句将完美运行。所以看起来是查询引擎的某种错误。 - Russ R

Answer 3

问题很简单：源查询中的一个或多个列包含超出其目标列长度的数据。一个简单的解决方案是对每个列执行Max(Len( source col ))，即获取源列的最大长度。

Select Max(Len(TextCol1))
    , Max(Len(TextCol2))
    , Max(Len(TextCol3))
    , ...
From ...

然后将这些长度与目标表中的数据类型长度进行比较。至少有一个超过了其目标列长度。

如果您绝对确定这不应该是这种情况 并且不在意是否是这种情况，那么另一种解决方案是将源查询列强制转换为它们的目标长度（这将截断任何过长的数据）：

Select Cast(TextCol1 As varchar(...))
    , Cast(TextCol2 As varchar(...))
    , Cast(TextCol3 As varchar(...))
    , ...
From ...

- Thomas

---

我的日常流程因为这个错误而开始出现问题。我插入的数据总是足够短，可以放下，而且我总是有其他行（在我提取的表中）有超长的字符串，由于我的过滤器，它们从未被插入。也许索引已经重建，或者统计数据已经更新，但是机器中的幽灵决定某一天它不再喜欢查询计划，因为它将其带到了一个路径，数据（太宽）“可能”在谓词中被过滤之前被插入到Where-Clause中。为了解决这个问题，我使用了LEFT()而不是CAST——只需要输入更少的字符。 - MikeTeeVee

谢谢Thomas，这很奇怪，即使我没有任何过长的数据，我仍然必须将其转换为新目标列大小，一旦我这样做了，它就可以工作了。 - JIANG

Answer 4

SQL Server 2019将最终返回更有意义的错误消息。

二进制或字符串数据被截断 => 错误消息增强

如果您在生产环境中遇到此错误，很难确定该错误来自哪个列或行，以及如何精确定位它。

要启用新行为，您需要使用DBCC TRACEON(460)。从sys.messages获取新的错误文本：

SELECT * FROM sys.messages WHERE message_id = 2628

2628 - 表 '%.*ls' 的列 '%.*ls' 中的字符串或二进制数据被截断。截断值：'%.*ls'。

通过启用跟踪标志460，可以在会话或服务器级别上替换消息ID 8152为2628，这条新消息也已经被带回到了SQL Server 2017 CU12（以及即将发布的SQL Server 2016 SP2 CU）。需要注意的是，即使在SQL Server 2019 CTP 2.0中，仍需启用相同的跟踪标志460。在未来的SQL Server 2019版本中，将默认使用消息2628替换消息8152。

---

SQL Server 2017 CU12也支持此功能。

改进：在SQL Server 2017中，可选替换“字符串或二进制数据将被截断”消息，并提供扩展信息

This SQL Server 2017 update introduces an optional message that contains the following additional context information.

Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber
String or binary data would be truncated in table '%.*ls', column '%.*ls'.
Truncated value: '%.*ls'.

The new message ID is 2628. This message replaces message 8152 in any error output if trace flag 460 is enabled.

db<>fiddle演示

---

ALTER DATABASE SCOPED CONFIGURATION

VERBOSE_TRUNCATION_WARNINGS = { ON | OFF }

适用于：SQL Server（从 SQL Server 2019 (15.x) 开始）和 Azure SQL 数据库

允许您启用或禁用新的字符串或二进制数据会被截断错误消息。SQL Server 2019（15.x）引入了一条新的、更具体的错误消息（2628）来处理这种情况:

在表 '%.*ls' 的列 '%.*ls' 中，字符串或二进制数据将被截断。截断的值: '%.*ls'。

当数据库兼容性级别为150且设置为ON时，截断错误会引发新的错误消息2628，以提供更多上下文信息，并简化故障排除过程。

当数据库兼容性级别为150且设置为OFF时，截断错误会引发先前的错误消息8152。

对于数据库兼容性级别为140或更低的情况，错误消息2628仍然是一条需要启用跟踪标志460的选择性错误消息，此时该数据库作用域配置不起作用。

- Lukasz Szozda

---

现在，SQL Azure 也可以使用此功能：https://azure.microsoft.com/en-gb/updates/global-trace-flags-are-now-available-in-azure-sql-database-managed-instance/。 - Ian Kemp

谢谢提供这些链接 - 很有用，了解到这些变化。 - LaoR

Answer 5

这可能是另一个原因，如果你为一列设置了默认值并且该默认值超出了该列的长度。看起来有人在一个长度为5的列上输入了超过5的默认值。这让我很烦恼，因为我尝试弄清楚为什么无论我插入什么都不起作用，即使我只插入一个整数1。因为表模式上的默认值违反了该列的限制，它导致了所有问题 - 我认为这给我们带来了教训 - 避免在表模式中使用默认值。 :)

- Brian

---

我认为避免默认值并不是一个好的解决方案。默认值非常有用。我不会通过移除默认值来解决由于打字错误而导致的数据库“问题”…… - Jacob H

Answer 6

这里有一个略微不同的答案。你的列名和长度可能全部匹配，但是在SELECT语句中你可能将列按错误的顺序指定了。假设tableX和tableY有相同的列名，但是顺序不同。

- SoloPilot

---

太出乎意料了，但它起作用了。谢谢！（对我来说，失败的是INSERT INTO x SELECT * FROM y。） - Andreas Reiff

Answer 7

我将增加另一种可能导致这个错误的原因，只是因为没有人提到过它，它可能会帮助一些未来的人（因为OP已经找到了答案）。如果您要插入的表具有触发器，则可能是触发器生成了错误。当表字段定义被更改但审计表未更改时，我曾经见过这种情况。

- HLGEM

Answer 8

如果您使用的是SQL Server 2016-2017： 要修复它，请打开trace flag 460。

DBCC TRACEON(460, 1);
GO

确保在完成操作后将其关闭：

DBCC TRACEOFF(460, 1);
GO

source

- nimajv

Answer 9

对于其他人，也要检查你们的存储过程。在我的情况下，我的存储过程CustomSearch中，我无意中声明了一个长度不足的列，因此当我输入大量数据时，尽管我的数据库具有很大的长度，我仍然收到了这个错误。我只需更改自定义搜索中列的长度，错误就消失了。这只是提醒。谢谢。

- bot

---

这正是我的情况。源/目标表匹配良好，但存储过程定义了一个长度较短的#table，导致失败。谢谢！ - Joy Walker

Answer 10

是的 - "一品脱装不满半品脱壶"。我对各种被建议的存储过程没有什么运气（无论原因是什么），但只要两个表位于同一个数据库中（或者你可以将它们放到同一个数据库中），你就可以使用INFORMATION_SCHEMA.COLUMNS来定位错误字段，方法如下：

select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c1.CHARACTER_MAXIMUM_LENGTH,c2.table_name,c2.COLUMN_NAME, c2.DATA_TYPE,c2.CHARACTER_MAXIMUM_LENGTH
from [INFORMATION_SCHEMA].[COLUMNS] c1
left join [INFORMATION_SCHEMA].[COLUMNS] c2 on 
c1.COLUMN_NAME=c2.COLUMN_NAME
where c1.TABLE_NAME='MyTable1'
and c2.TABLE_NAME='MyTable2'
--and c1.DATA_TYPE<>c2.DATA_TYPE
--and c1.CHARACTER_MAXIMUM_LENGTH <> c2.CHARACTER_MAXIMUM_LENGTH
order by c1.COLUMN_NAME

这将允许您上下滚动，比较字段长度。注释部分可以让您看到（一旦取消注释），是否存在数据类型不匹配，或者特别显示在字段长度方面不同的那些 - 因为我太懒了，不想滚动 - 只要知道整个过程基于源列名与目标列名相匹配。

- Kevin Anderson

---

我本来也想写这样的东西，但你让它变得简单了。非常方便，而且运行得很好。我能够用它比较一个有90多列的表格，其中两列立刻就跳出来了。谢谢！ - Joy Walker