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

从@gmmastros的回答

当您看到消息....时

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

想想自己……字段不够大，容不下我的数据。

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

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

如果仅仅通过查看脚本还不清楚哪一个INSERT是违规者，你可以在错误消息中计算出现的<1 row affected>行。得到的数字加1就是陈述号。在您的例子中，似乎是第二个INSERT产生了错误。

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

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

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

有些数据无法放入数据库列中(小)。要发现哪里有问题并不容易。如果你使用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();

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

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

只是想提供额外的信息:我也有同样的问题，这是因为字段对传入的数据不够大，这个线程帮助我解决了这个问题(上面的答案澄清了一切)。

但是，了解可能导致它的可能原因是非常重要的。

在我的情况下，我创建了一个字段像这样的表:

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

因此，字段“Period”的长度为零，导致插入操作失败。我将其更改为“XXXXXX”，这是输入数据的长度，现在它可以正常工作(因为字段现在的长度为6)。

我希望这对有同样问题的人有帮助:)

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


}

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

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

当我试图执行我的存储过程时，我遇到了同样的问题，因为我需要添加一些数据的列的大小比我想要添加的数据要短。

您可以增加列数据类型的大小或减少数据的长度。

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

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

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

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

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

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

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

不幸的是，没有办法改变表类型，所以它必须被删除&完全重新创建。由于我的表有超过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;

更新表类型后，数据导出服务再次开始正常工作!：）

在接受的答案下凯文教皇的注释是我所需要的。

在我的例子中，问题是我在我的表上定义了触发器，将更新/插入事务插入到审计表中，但审计表有一个数据类型不匹配，其中原始表中具有VARCHAR(MAX)的列存储为审计表中的VARCHAR(1)，所以当我在原始表列中插入任何大于VARCHAR(1)的内容时，触发器失败，我会得到这个错误消息。

我使用了不同的策略，在某些地方分配了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条记录。

此错误可能是由于字段大小小于您输入的数据。

例如，如果你的数据类型是nvarchar(7)，如果你的值是'aaaaddddf'，那么错误将显示为:

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

2016/2017更新将向您显示坏值和列。

新的跟踪标志将把旧的错误替换为新的2628错误，并打印出出错的列和值。Traceflag 460在2016年和2017年的最新累积更新中可用:

https://support.microsoft.com/en-sg/help/4468101/optional-replacement-for-string-or-binary-data-would-be-truncated

.．.或使用DBCC TRACEON:

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15

在这方面，你根本无法打败SQL Server。

你可以像这样插入一个新表:

select foo, bar
into tmp_new_table_to_dispose_later
from my_table

和将表定义与实际表进行比较你想插入数据。

有时有用，有时没用。

如果您尝试从临时表插入final/real表，它可能只是工作(例如，由于数据转换的工作方式与SSMS不同)。

另一种方法是以块的形式插入数据，而不是立即插入所有内容，你插入top 1000，你重复这个过程，直到你找到一个有错误的块。至少您可以更好地了解表中不适合的内容。

如果有人在C#应用程序中遇到此错误，我已经创建了方法来:

1. 获取我们试图进行插入/更新的表中所有列的列宽。(我得到这个信息直接从数据库中。)
2. 将列的宽度与我们试图插入/更新的值的宽度进行比较。

步骤1:

获取表直接从数据库中中所有列的列宽:

// I took HUGE help from this Microsoft docs website: - AshishK
// https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlconnection.getschema?view=netframework-4.7.2#System_Data_SqlClient_SqlConnection_GetSchema_System_String_System_String___
private static Dictionary<string, int> GetColumnSizesOfTableFromDatabase(string tableName, string connectionString)
{
var columnSizes = new Dictionary<string, int>();
            

using (var connection = new SqlConnection(connectionString))
{
// Connect to the database then retrieve the schema information.
connection.Open();


// You can specify the Catalog, Schema, Table Name, Column Name to get the specified column(s).
// You can use four restrictions for Column, so you should create a 4 members array.
String[] columnRestrictions = new String[4];


// For the array, 0-member represents Catalog; 1-member represents Schema;
// 2-member represents Table Name; 3-member represents Column Name.
// Now we specify the Table_Name and Column_Name of the columns what we want to get schema information.
columnRestrictions[2] = tableName;


DataTable allColumnsSchemaTable = connection.GetSchema("Columns", columnRestrictions);


foreach (DataRow row in allColumnsSchemaTable.Rows)
{
var columnName = row.Field<string>("COLUMN_NAME");
var dataType = row.Field<string>("DATA_TYPE");
var characterMaxLength = row.Field<int?>("CHARACTER_MAXIMUM_LENGTH");


// I'm only capturing columns whose Datatype is "varchar" or "char", i.e. their CHARACTER_MAXIMUM_LENGTH won't be null.
if(characterMaxLength != null)
{
columnSizes.Add(columnName, characterMaxLength.Value);
}
}


connection.Close();
}


return columnSizes;
}

步骤2:

将列的宽度与我们试图插入/更新的值的宽度进行比较:

public static Dictionary<string, string> FindLongBinaryOrStringFields<T>(T entity, string connectionString)
{
var tableName = typeof(T).Name;
Dictionary<string, string> longFields = new Dictionary<string, string>();
var objectProperties = GetProperties(entity);
//var fieldNames = objectProperties.Select(p => p.Name).ToList();


var actualDatabaseColumnSizes = GetColumnSizesOfTableFromDatabase(tableName, connectionString);
            

foreach (var dbColumn in actualDatabaseColumnSizes)
{
var maxLengthOfThisColumn = dbColumn.Value;
var currentValueOfThisField = objectProperties.Where(f => f.Name == dbColumn.Key).First()?.GetValue(entity, null)?.ToString();


if (!string.IsNullOrEmpty(currentValueOfThisField) && currentValueOfThisField.Length > maxLengthOfThisColumn)
{
longFields.Add(dbColumn.Key, $"'{dbColumn.Key}' column cannot take the value of '{currentValueOfThisField}' because the max length it can take is {maxLengthOfThisColumn}.");
}
}


return longFields;
}


public static List<PropertyInfo> GetProperties<T>(T entity)
{
//The DeclaredOnly flag makes sure you only get properties of the object, not from the classes it derives from.
var properties = entity.GetType()
.GetProperties(System.Reflection.BindingFlags.Public
| System.Reflection.BindingFlags.Instance
| System.Reflection.BindingFlags.DeclaredOnly)
.ToList();


return properties;
}

用法:

假设我们试图插入SomeTable类的someTableEntity，它在我们的应用程序中是这样建模的:

public class SomeTable
{
[Key]
public long TicketID { get; set; }
public string SourceData { get; set; }
}

它在SomeDbContext中，如下所示:

public class SomeDbContext : DbContext
{
public DbSet<SomeTable> SomeTables { get; set; }
}

现在，我们将尝试在这个字段中插入一个长度超过16个字符的值，并捕获以下信息:

public void SaveSomeTableEntity()
{
var connectionString = "server=SERVER_NAME;database=DB_NAME;User ID=SOME_ID;Password=SOME_PASSWORD;Connection Timeout=200";
        

using (var context = new SomeDbContext(connectionString))
{
var someTableEntity = new SomeTable()
{
SourceData = "Blah-Blah-Blah-Blah-Blah-Blah"
};
        

context.SomeTables.Add(someTableEntity);
        

try
{
context.SaveChanges();
}
catch (Exception ex)
{
if (ex.GetBaseException().Message == "String or binary data would be truncated.\r\nThe statement has been terminated.")
{
var badFieldsReport = "";
List<string> badFields = new List<string>();
                

// YOU GOT YOUR FIELDS RIGHT HERE:
var longFields = FindLongBinaryOrStringFields(someTableEntity, connectionString);


foreach (var longField in longFields)
{
badFields.Add(longField.Key);
badFieldsReport += longField.Value + "\n";
}
}
else
throw;
}
}
}

badFieldsReport将具有如下值:

'SourceData'列不能取值 "啥啥啥"因为它能承受的最大长度是 16 . < / p >

此错误仅当任何字段长度大于sql server数据库表结构中指定的字段长度时出现。