我也有同样的问题。我的专栏太短了。

您所能做的就是增加或缩短想要放入数据库中的文本的长度。

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

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

首先创建helper类:

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

                }
            }
        }
    }
}

然后为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;
        }
    }
}

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

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

最后使用代码:

Datamodel.SubmitChangesWithDetailException();

我使用了不同的策略，在某些地方分配了8K的字段。这里只使用了大约50/100。

declare @NVPN_list as table 
nvpn            varchar(50)
,nvpn_revision  varchar(5)
,nvpn_iteration INT
,mpn_lifecycle  varchar(30)
,mfr            varchar(100)
,mpn            varchar(50)
,mpn_revision   varchar(5)
,mpn_iteration  INT
-- ...
) INSERT INTO @NVPN_LIST 
SELECT  left(nvpn           ,50)    as nvpn
        ,left(nvpn_revision ,10)    as nvpn_revision
        ,nvpn_iteration
        ,left(mpn_lifecycle ,30)
        ,left(mfr           ,100)
        ,left(mpn           ,50)
        ,left(mpn_revision  ,5)
        ,mpn_iteration
        ,left(mfr_order_num ,50)
FROM [DASHBOARD].[dbo].[mpnAttributes] (NOLOCK) mpna

我想要速度，因为我总共有1M条记录，并加载了28K条记录。

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

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

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

我也犯了同样的错误，原因是在一个从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