当尝试插入时，错误、字符串或二进制数据将被截断

I am running data.bat file with the following lines:

我运行数据。bat文件有以下几行:

Rem Tis batch file will populate tables

cd\program files\Microsoft SQL Server\MSSQL
osql -U sa -P Password -d MyBusiness -i c:\data.sql

The contents of the data.sql file is:

数据的内容。sql文件是:

   insert Customers
            (CustomerID, CompanyName, Phone)
             Values('101','Southwinds','19126602729')

There are 8 more similar lines for adding records.

还有8条类似的添加记录的行。

When I run this with start > run > cmd > c:\data.bat, I get this error message:

当我运行这个启动>运行> cmd > c:\数据。bat，我收到错误信息:

1>2>3>4>5>....<1 row affected>
Msg 8152, Level 16, State 4, Server SP1001, Line 1
string or binary data would be truncated.

<1 row affected>

<1 row affected>

<1 row affected>

<1 row affected>

<1 row affected>

<1 row affected>

Also, I am a newbie obviously, but what do Level #, and state # mean, and how do I look up error messages such as the one above: 8152?

当然，我是一个新手，但是Level #和state #是什么意思?我如何查找错误消息，比如上面的:8152?

10 个解决方案

#1

From @gmmastros's answer

从@gmmastros的回答

Whenever you see the message....

当你看到消息....

string or binary data would be truncated 

Think to yourself... The field is NOT big enough to hold my data.

认为自己…这个领域不够大，不能容纳我的数据。

Check the table structure for the customers table. I think you'll find that the length of one or more fields is NOT big enough to hold the data you are trying to insert. For example, if the Phone field is a varchar(8) field, and you try to put 11 characters in to it, you will get this error.

检查客户表的表结构。我想您会发现一个或多个字段的长度不足以容纳您要插入的数据。例如，如果Phone字段是varchar(8)字段，并尝试在其中输入11个字符，您将得到这个错误。

#2

In one of the INSERT statements you are attempting to insert a too long string into a string (varchar or nvarchar) column.

在一个INSERT语句中，您试图将一个太长的字符串插入到一个string (varchar或nvarchar)列中。

If it's not obvious which INSERT is the offender by a mere look at the script, you could count the <1 row affected> lines that occur before the error message. The obtained number plus one gives you the statement number. In your case it seems to be the second INSERT that produces the error.

如果仅通过查看脚本就不知道哪个插入是违例的，您可以计算错误消息之前发生的受影响的<1行>行。得到的数+ 1给出了表述数。在您的例子中，似乎是第二个插入产生错误。

#3

I had this issue although data length was shorter than the field length. It turned out that the problem was having another log table (for audit trail), filled by a trigger on the main table, where the column size also had to be changed.

我有这个问题，虽然数据长度比字段长度短。原来问题是有另一个日志表(用于审计跟踪)，由主表上的一个触发器填充，其中的列大小也必须更改。

#4

Some of your data cannot fit into your database column (small). It is not easy to find what is wrong. If you use C# and Linq2Sql, you can list the field which would be truncated:

有些数据不能放入数据库列(小的)。要找出问题出在哪里并不容易。如果您使用c#和Linq2Sql，您可以列出将被截断的字段:

First create helper class:

首先创建助手类:

public class SqlTruncationExceptionWithDetails : ArgumentOutOfRangeException
{
    public SqlTruncationExceptionWithDetails(System.Data.SqlClient.SqlException inner, DataContext context)
        : base(inner.Message + " " + GetSqlTruncationExceptionWithDetailsString(context))
    {
    }

    /// <summary>
    /// PArt of code from following link
    /// http://*.com/questions/3666954/string-or-binary-data-would-be-truncated-linq-exception-cant-find-which-fiel
    /// </summary>
    /// <param name="context"></param>
    /// <returns></returns>
    static string GetSqlTruncationExceptionWithDetailsString(DataContext context)
    {
        StringBuilder sb = new StringBuilder();

        foreach (object update in context.GetChangeSet().Updates)
        {
            FindLongStrings(update, sb);
        }

        foreach (object insert in context.GetChangeSet().Inserts)
        {
            FindLongStrings(insert, sb);
        }
        return sb.ToString();
    }

    public static void FindLongStrings(object testObject, StringBuilder sb)
    {
        foreach (var propInfo in testObject.GetType().GetProperties())
        {
            foreach (System.Data.Linq.Mapping.ColumnAttribute attribute in propInfo.GetCustomAttributes(typeof(System.Data.Linq.Mapping.ColumnAttribute), true))
            {
                if (attribute.DbType.ToLower().Contains("varchar"))
                {
                    string dbType = attribute.DbType.ToLower();
                    int numberStartIndex = dbType.IndexOf("varchar(") + 8;
                    int numberEndIndex = dbType.IndexOf(")", numberStartIndex);
                    string lengthString = dbType.Substring(numberStartIndex, (numberEndIndex - numberStartIndex));
                    int maxLength = 0;
                    int.TryParse(lengthString, out maxLength);

                    string currentValue = (string)propInfo.GetValue(testObject, null);

                    if (!string.IsNullOrEmpty(currentValue) && maxLength != 0 && currentValue.Length > maxLength)
                    {
                        //string is too long
                        sb.AppendLine(testObject.GetType().Name + "." + propInfo.Name + " " + currentValue + " Max: " + maxLength);
                    }

                }
            }
        }
    }
}

Then prepare the wrapper for SubmitChanges:

然后准备提交更改的包装:

public static class DataContextExtensions
{
    public static void SubmitChangesWithDetailException(this DataContext dataContext)
    {
        //http://*.com/questions/3666954/string-or-binary-data-would-be-truncated-linq-exception-cant-find-which-fiel
        try
        {
            //this can failed on data truncation
            dataContext.SubmitChanges();
        }       
        catch (SqlException sqlException) //when (sqlException.Message == "String or binary data would be truncated.")
        {

            if (sqlException.Message == "String or binary data would be truncated.") //only for EN windows - if you are running different window language, invoke the sqlException.getMessage on thread with EN culture
                throw new SqlTruncationExceptionWithDetails(sqlException, dataContext);
            else
                throw;
        }
    }
}

Prepare global exception handler and log truncation details:

准备全局异常处理程序和日志截断细节:

protected void Application_Error(object sender, EventArgs e)
{
    Exception ex = Server.GetLastError();
    string message = ex.Message;
    //TODO - log to file
}

Finally use the code:

最后使用代码:

Datamodel.SubmitChangesWithDetailException();

#5

on sql server you can use SET ANSI_WARNINGS OFF like this:

在sql server上，可以使用设置ansi_warning:

        using (SqlConnection conn = new SqlConnection("Data Source=XRAYGOAT\\SQLEXPRESS;Initial Catalog='Healthy Care';Integrated Security=True"))
        {
            conn.Open();

            using (var trans = conn.BeginTransaction())
            {
                try
                {
                    using cmd = new SqlCommand("", conn, trans))
                    { 

                    cmd.CommandText = "SET ANSI_WARNINGS OFF";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "YOUR INSERT HERE";
                    cmd.ExecuteNonQuery();

                    cmd.Parameters.Clear();

                    cmd.CommandText = "SET ANSI_WARNINGS ON";
                    cmd.ExecuteNonQuery();

                    trans.Commit();
                    }
                }
                catch (Exception)
                {

                    trans.Rollback();
                }

            }

            conn.Close();

        }

#6

Also had this problem occuring on the web application surface. Eventually found out that the same error message comes from the SQL update statement in the specific table.

在web应用程序表面也出现了这个问题。最终发现相同的错误消息来自特定表中的SQL update语句。

Finally then figured out that the colum definition in the relating history table(s) did not map the original table column lenght of nvarchar types in some specific cases.

最后得出关联历史表中的colum定义在某些特定情况下并不映射nvarchar类型的原始表列lenght。

Hope this hint helps someone else too.. ;)

希望这个提示也能帮助别人。,)

#7

Just want to contribute with additional information: I had the same issue and it was because of the field wasn't big enough for the incoming data and this thred helped me to solve it (the top answer clarifies it all).

只是想提供更多的信息:我也有同样的问题，因为这个字段不够大，不能接收到的数据，这帮助我解决了问题(上面的答案澄清了一切)。

BUT it is very important to know what are the possible reasons that may cause it.

但是很重要的是要知道是什么原因导致了它。

In my case i was creating the table with a field like this:

在我的例子中，我创建了这样一个字段:

Select '' as  Period, * From Transactions Into #NewTable

Therefore the field "Period" had a length of Zero and causing the Insert operations to fail. I changed it to "XXXXXX" that is the length of the incoming data and it now worked properly (because field now had a lentgh of 6).

因此，字段“周期”的长度为0，导致插入操作失败。我将它改为“XXXXXX”，这是传入数据的长度，它现在可以正常工作了(因为字段现在有6个lentgh)。

I hope this help anyone with same issue :)

我希望这对任何有同样问题的人都有帮助。

#8

Another situation in which you can get this error is the following:

另一种情况下，你可以得到这个错误是:

I had the same error and the reason was that in an INSERT statement that received data from an UNION, the order of the columns was different from the original table. If you change the order in #table3 to a, b, c, you will fix the error.

我也有同样的错误，原因是在从UNION接收数据的INSERT语句中，列的顺序与原始表不同。如果您将表3中的顺序更改为a、b、c，您将修复错误。

select a, b, c into #table1
from #table0

insert into #table1
    select a, b, c from #table2
    union
    select a, c, b from #table3

#9

I had the same issue. The length of my column was too short. What you can do is either increase the lenght or shorten the text you want to put in the database.

我也有同样的问题。我的专栏太短了。您可以做的是增加lenght或缩短要放入数据库的文本。

#10

Another situation, in which this error may occur is in SQL Server Management Studio. If you have "text" or "ntext" fields in your table, no matter what kind of field you are updating (for example bit or integer). Seems that the Studio does not load entire "ntext" fields and also updates ALL fields instead of the modified one. To solve the problem, exclude "text" or "ntext" fields from the query in Management Studio

另一种可能出现此错误的情况是在SQL Server Management Studio中。如果您的表中有“text”或“ntext”字段，无论您正在更新哪种字段(例如bit或integer)。似乎Studio没有加载整个“ntext”字段，而是更新所有字段，而不是修改后的字段。要解决这个问题，请在Management Studio中从查询中排除“text”或“ntext”字段