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

error, string or binary data would be truncated when trying to insert

提问by karthik

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

我正在使用以下几行运行 data.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:

data.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:

当我使用start> run> cmd>运行它时c:\data.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？

回答by karthik

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.

检查customers 表的表结构。我想您会发现一个或多个字段的长度不足以容纳您要插入的数据。例如，如果 Phone 字段是一个 varchar(8) 字段，并且您尝试将 11 个字符放入其中，则会出现此错误。

回答by alterfox

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.

尽管数据长度比字段长度短，但我遇到了这个问题。结果证明问题在于有另一个日志表（用于审计跟踪），由主表上的触发器填充，其中列大小也必须更改。

回答by Andriy M

In one of the INSERTstatements you are attempting to insert a too long string into a string (varcharor nvarchar) column.

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

If it's not obvious which INSERTis the offender by a mere look at the script, you could count the <1 row affected>lines that occur beforethe 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.

如果INSERT仅通过查看脚本就不清楚哪个是违规者，您可以计算<1 row affected>出现在错误消息之前的行数。获得的数字加一为您提供语句编号。在您的情况下，它似乎是产生错误的第二个 INSERT。

回答by Tomas Kubes

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://stackoverflow.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:

然后为 SubmitChanges 准备包装器：

public static class DataContextExtensions
{
    public static void SubmitChangesWithDetailException(this DataContext dataContext)
    {
        //http://stackoverflow.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();

回答by RaRdEvA

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 thread 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).

因此字段“Period”的长度为零并导致插入操作失败。我将其更改为“XXXXXX”，即传入数据的长度，现在它可以正常工作（因为字段现在的长度为 6）。

I hope this help anyone with same issue :)

我希望这可以帮助任何有同样问题的人:)

回答by Esperento57

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

在 sql server 上，您可以像这样使用 SET ANSI_WARNINGS OFF：

        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();

        }

回答by bizimunda

I had the same issue. The lengthof my column was too short.

我遇到过同样的问题。我的专栏的长度太短了。

What you can do is either increasethe length or shortenthe text you want to put in the database.

您可以做的是增加长度或缩短要放入数据库的文本。

回答by webMac

Also had this problem occurring 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更新语句。

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

最后发现相关历史表中的列定义nvarchar在某些特定情况下没有映射原始表列的类型长度。

回答by hola77

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 语句中，列的顺序与原始表不同。如果您将#table3 中的顺序更改为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

回答by Marco Roy

I had the same problem, even after increasing the size of the problematic columns in the table.

我遇到了同样的问题，即使增加了表中有问题的列的大小。

tl;dr: The length of the matching columns in corresponding Table Types may also need to be increased.

tl;dr：对应表类型中匹配列的长度也可能需要增加。

In my case, the error was coming from the Data Export service in Microsoft Dynamics CRM, which allows CRM data to be synced to an SQL Server DB or Azure SQL DB.

就我而言，错误来自 Microsoft Dynamics CRM 中的数据导出服务，该服务允许将 CRM 数据同步到 SQL Server DB 或 Azure SQL DB。

After a lengthy investigation, I concluded that the Data Export service must be using Table-Valued Parameters:

经过长时间的调查，我得出结论，数据导出服务必须使用表值参数：

You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

您可以使用表值参数将多行数据发送到 Transact-SQL 语句或例程，例如存储过程或函数，而无需创建临时表或许多参数。

As you can see in the documentation above, Table Types are used to create the data ingestion procedure:

正如您在上面的文档中看到的，表类型用于创建数据摄取过程：

CREATE TYPE LocationTableType AS TABLE (...);
CREATE PROCEDURE dbo.usp_InsertProductionLocation
  @TVP LocationTableType READONLY

Unfortunately, there is no way to alter a Table Type, so it has to be dropped & recreated entirely. Since my table has over 300 fields (), I created a query to facilitate the creation of the corresponding Table Type based on the table's columns definition (just replace [table_name]with your table's name):

不幸的是，无法更改表类型，因此必须完全删除并重新创建它。由于我的表有 300 多个字段 ()，因此我创建了一个查询以方便根据表的列定义创建相应的表类型（只需替换[table_name]为您的表名）：

SELECT 'CREATE TYPE [table_name]Type AS TABLE (' + STRING_AGG(CAST(field AS VARCHAR(max)), ',' + CHAR(10)) + ');' AS create_type
FROM (
  SELECT TOP 5000 COLUMN_NAME + ' ' + DATA_TYPE
      + IIF(CHARACTER_MAXIMUM_LENGTH IS NULL, '', CONCAT('(', IIF(CHARACTER_MAXIMUM_LENGTH = -1, 'max', CONCAT(CHARACTER_MAXIMUM_LENGTH,'')), ')'))
      + IIF(DATA_TYPE = 'decimal', CONCAT('(', NUMERIC_PRECISION, ',', NUMERIC_SCALE, ')'), '')
      AS field
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = '[table_name]'
  ORDER BY ORDINAL_POSITION) AS T;

After updating the Table Type, the Data Export service started functioning properly once again! :)

更新表类型后，数据导出服务再次开始正常运行！:)