为什么“字符串或二进制数据不会被截断”不是一个更具描述性的错误?

To start: I understand what this error means - I'm not attempting to resolve an instance of it.

首先:我理解这个错误的含义——我并没有试图解决它的一个实例。

This error is notoriously difficult to troubleshoot, because if you get it inserting a million rows into a table 100 columns wide, there's virtually no way to determine what column of what row is causing the error - you have to modify your process to insert one row at a time, and then see which one fails. That's a pain, to put it mildly.

这个错误是出了名的难以解决,因为如果你把它插入一百万行到一个表100列宽,几乎没有办法确定列的行导致错误的——你必须修改过程中插入一行,然后看哪一个失败。说得婉转些，那是一种痛苦。

Is there any reason that the error doesn't look more like this?

有什么原因使误差不像这样?

String or Binary data would be truncated
Error inserting value "Some 18 char value" into SomeTable.SomeColumn VARCHAR(10)

That would make it a lot easier to find and correct the value, if not the table structure itself. If seeing the table data is a security concern, then maybe something generic, like giving the length of the attempted value and the name of the failing column?

这将使查找和纠正值变得容易得多，如果不是表结构本身。如果查看表数据是一个安全问题，那么可能需要一些通用的东西，比如给出尝试值的长度和失败列的名称?

7 个解决方案

#1

It turns out there's an open "feature request" for this on MS Connect - I'd encourage you to vote for it if you'd like the functionality changed.

在MS Connect上有一个开放的“功能请求”——如果你想改变功能，我建议你投它一票。

https://connect.microsoft.com/SQLServer/feedback/details/339410/

https://connect.microsoft.com/SQLServer/feedback/details/339410/

ADDED:

补充道:

It actually looks like there's another request for this same feature (though poorly named) that's been outstanding since Yukon's development in 2005 that I'd encourage people to vote for as well:

实际上，自从2005年育空开发以来，这个功能(虽然名字不太好)似乎还有另一个要求，我也鼓励人们去投票:

https://connect.microsoft.com/SQLServer/feedback/details/125347/

https://connect.microsoft.com/SQLServer/feedback/details/125347/

Update 2016

更新2016

It seems Microsoft has tried to delete evidence of this bug's true age. Fair enough. Find the old site archived here.

微软似乎已经试图删除这个bug的真实年龄的证据。很好。找到这里存档的旧站点。

#2

After not finding an acceptable answer anywhere I came up with the following:

在我找不到一个可以接受的答案后，我想到了以下几点:

1. Get the query that is causing the problems (you can also use SQL Profiler if you dont have the source)
2. 获取导致问题的查询(如果没有源，也可以使用SQL分析器)
3. Remove all WHERE clauses and other unimportant parts until you are basically just left with the SELECT and FROM parts
4. 删除所有WHERE子句和其他不重要的部分，直到基本上只剩下SELECT和FROM part
5. Add WHERE 0 = 1 (this will select only table structure)
6. 添加0 = 1的地方(这将只选择表结构)
7. Add INTO [MyTempTable] just before the FROM clause
8. 在FROM子句之前添加到[tempmytable]

You should end up with something like

你应该得到类似的结果

SELECT
 Col1, Col2, ..., [ColN]
INTO [MyTempTable]
FROM
  [Tables etc.]
WHERE 0 = 1

This will create a table called MyTempTable in your DB that you can compare to your target table structure to see where they differ i.e. you can compare the columns on both tables.

这将在您的DB中创建一个名为MyTempTable的表，您可以将其与目标表结构进行比较，以查看它们的不同之处，即您可以比较两个表上的列。

EDIT: You can compare the data types and column sizes of each column on the original table and MyTempTable to see where they differ.All column names in your new table will be the same as the old, and the data types and sizes will be the same EXCEPT where the offending column is. In other words, with this query, SQL will automatically create columns that are large enough to handle the largest possible entry from the source table

编辑:您可以比较原始表和MyTempTable上每个列的数据类型和列大小，以查看它们的不同之处。新表中的所有列名都将与旧表相同，数据类型和大小也将相同，但违规列所在的位置除外。换句话说，通过这个查询，SQL将自动创建足够大的列，以处理源表中最大的条目

#3

Answering a DUP that got closed, so answering here instead. This pattern can be used, if somewhat elaborate, but it can be useful when it is not trivial to change the application, or set up profiler to see what is happening. Sometimes, you just need the error to propagate to the APP itself so you can see right from the APP, the correct and useful error message.

回复一个关闭的DUP，所以在这里回复。这个模式可以使用，如果稍微复杂一点的话，但是当更改应用程序或设置profiler以查看正在发生的事情时，它是有用的。有时候，你只需要将错误传播到应用程序本身，这样你就可以从应用程序中看到正确而有用的错误信息。

In those cases, a quick poke into the DB with this solution will save you lots of time. Save it as a template, and make quick changes to it to solve this problem on any table.

在这些情况下，使用这种解决方案快速地插入DB将节省大量时间。将其保存为模板，并对其进行快速更改，以解决任何表上的这个问题。

The problem

Sample table

样表

create table StringTruncation
(A int, B varchar(10), C nvarchar(5), D nvarchar(max), E datetime)

Sample statement

示例语句

insert StringTruncation values
(1, '0123456789', 'abcdef', 'This overflows on C', GETDATE())

The dreaded useless error

可怕的无用的错误

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

The example shows only 2 columns where it could overflow, but imagine if it were 20 columns, or 40.

这个示例只显示了可以溢出的2列，但是想象一下如果是20列，或者40列。

The solution

-- First move the table out of the way
exec sp_rename StringTruncation, StringTruncation_;

-- cover it with a query
create view dbo.StringTruncation
with schemabinding
as
select
    A,
    convert(Nvarchar(max),B) B,
    convert(Nvarchar(max),C) C,
    D, E
from dbo.StringTruncation_
GO

-- use a trigger to allow INSERTs, with the length checks thrown in
create trigger dbo.trig_ioi_StringTruncation
on StringTruncation
instead of insert
as
set nocount on
declare @offending nvarchar(max)
select TOP 1 @offending = case
    when len(C) > 5 then 'Data too long for Column [C] Size 5: ' + C
    when len(B) > 10 then 'Data too long for Column [D] Size 10: ' + B
    end
from inserted
where len(C) > 5 or len(B) > 10
GO

-- keep good data
if @@rowcount = 0
    insert StringTruncation_
    select * from inserted
else
    raiserror(@offending,16,1)
GO

Test it

insert StringTruncation values
(1, '0s123456789', 'abcde', 'This overflows on C', GETDATE())

Result

结果

Msg 50000, Level 16, State 1, Procedure trig_ioi_StringTruncation, Line 18
Data too long for Column [D] Size 10: 0s123456789

(1 row(s) affected)

Msg 50000，级别16，状态1，过程trig_ioi_StringTruncation，第18行数据对列太长[D]

Notes

• It needs a mirror trigger for UPDATEs
• 它需要一个镜像触发器来更新
• It will currently only report on the first offending record-column. It is possible to report more than one record-column, but I believe that is actually counter-productive.
• 它目前只报告第一个违规记录栏。有可能报告不止一个记录栏，但我认为这实际上是反效果的。

#4

Microsoft is lazy?

微软很懒吗?

You don't have to try each row insert separately, by the way. Just query for max(len(field)) for each text column, starting with the ones you suspect might be the culprit.

顺便说一句，你不需要分别尝试每一行插入。只需为每个文本列查询max(len(field))，从您怀疑可能是罪魁祸首的字段开始。

#5

Short Answer: That's just how it is.

简而言之:事情就是这样。

Longer Answer: I could see value in showing the row number and column, maybe but it probably wouldn't make sense to show the actual information being truncated. With the VARCHAR(10) scenario, it's probably not a big deal, but excessively large size data would be a whole lot useful. But hopefully no one here is inserting anything more than a VARCHAR(MAX) can hold ;)

更长的回答:我可以看到显示行号和列的值，但是显示被截断的实际信息可能没有意义。对于VARCHAR(10)场景，这可能不是什么大问题，但是过大的数据会非常有用。但希望这里没有人能插入超过VARCHAR(MAX)的东西;

#6

Descriptive error messages in software systems are as good as non-existant.

描述错误消息在软件系统中是一样的好与不存在。

That holds not only for DBMS's but for as good as any kind of software one can imagine.

这不仅适用于DBMS，也适用于任何类型的软件。

I think the underlying reason is that "good descriptive error messages" take too much time to implement. It is not part of the average software developer's culture to spend much time thinking about "which information would the user want to see if this particular kind of exception occurs" ? The programmers who have to write down the code for giving "good descriptive error messages" only see the cost (their time), not the benefit.

我认为根本的原因是“良好的描述性错误消息”需要太多的时间来实现。一般的软件开发人员不会花大量时间考虑“如果这种特殊的异常发生，用户希望看到哪些信息”?那些需要编写“好的描述性错误消息”代码的程序员只会看到成本(他们的时间)，而不是收益。

One of the most recent error messages I got from a software system is "Something wrong has happened. Please try again later.". No kidding.

我最近从软件系统中得到的一条错误消息是“发生了错误”。请稍后再试。没有开玩笑。

#7

I had this error too, but I found what was causing:

我也犯了这个错误，但我找到了原因:

<option>description</option>  <-- this is not error but is wrong
<option value="option1">option1</option>
<option value="option2">option2</option>
<option value="option3">option3</option> 

My MVC application recivies "value" as empty strig ant that causes exception

我的MVC应用程序将“值”定义为导致异常的空strig ant