Ошибка sql символьные или двоичные данные могут быть усечены

CREATE TABLE dbo.CoolPeople(PersonName VARCHAR(20), PrimaryCar VARCHAR(20));

GO

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)

VALUES ('Baby', '2006 Subaru Impreza WRX GD');

GO

Машина Baby длиннее, чем 20 символов, поэтому при выполнении оператора INSERT получаем ошибку:

Msg 8152, Level 16, State 30, Line 5

String or binary data would be truncated.

The statement has been terminated.

Это засада, поскольку у нас нет идей относительно того, какое поле вызвало проблемы! Это особенно ужасно, когда вы пытаетесь вставить множество строк.

Чтобы пофиксить ошибку, включите флаг трассировки 460

Флаг трассировки 460 был введен в SQL Server Sevice Pack 2, Cummulative Update 6, и в SQL Server 2017. (Вы можете найти и загрузить последние обновления с SQLServerUpdates.com.) Вы можете включить флаг на уровне запроса, например:

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)

VALUES ('Baby', '2006 Subaru Impreza WRX GD')

OPTION (QUERYTRACEON 460);

GO

Теперь, если выполнить запрос, он покажет вам, какой столбец усекается, а также какая строка. В нашем случае мы имеем только одну строку, но в реальной жизни много полезней будет знать, какая строка вызвала ошибку:

Msg 2628, Level 16, State 1, Line 9

String or binary data would be truncated in 

table 'StackOverflow2013.dbo.CoolPeople', column 'PrimaryCar'. 

Truncated value: '2006 Subaru Impreza '.

Вы можете включить этот флаг трассировки как на уровне запроса (в нашем примере выше), так и на уровне сервера:

DBCC TRACEON(460, -1);

GO

Этот оператор включает его для всех, а не только для вас — поэтому сначала договоритесь со своей командой разработчиков, прежде чем включать его. Это изменит номер ошибки 8152 на 2628 (как показано выше), означающее, что если вы строили обработку ошибок на основании этих номеров, вы сразу получите другое поведение.

Я любитель включения этого флага трассировки на время отладки и изучения, но как только обнаруживаю источник проблем, выключаю его, снова выполнив команду:

DBCC TRACEON(460, -1);

GO

В нашем случае, как только мы идентифицировали избыточную длину машины Baby, необходимо либо изменить название машины, либо изменить тип данных в нашей таблице, чтобы сделать размер столбца больше. Можно также предварительно обрабатывать данные, явно отсекая избыточные символы. Мастерская по разборке данных, если хотите.

Не оставляйте этот флаг включенным

По крайней мере, имеется связанный с этим один баг в SQL Server 2017 CU13: табличные переменные будут выбрасывать ошибки, говорящие, что их содержимое усекается, даже если никакие данные не вставляются в них.

Вот простой скрипт, чтобы проверить, пофиксили ли это поведение:

CREATE OR ALTER PROC dbo.Repro @BigString VARCHAR(8000) AS

BEGIN

	DECLARE @Table TABLE ( SmallString VARCHAR(128) )

	IF ( 1 = 0 )

		/* Это никогда не выполняется */

		INSERT INTO @Table	( SmallString )

		VALUES(@BigString)

		OPTION (QUERYTRACEON 460)

END

GO

DECLARE @BigString VARCHAR(8000) = REPLICATE('blah',100)

EXEC dbo.Repro @BigString

GO

SQL Server 2017 CU13 всё еще сообщает об усечении строки, даже если строка не вставляется:

Переключение с табличной переменной на временную таблицу приводит к ожидаемому поведению:

Это замечательный пример, почему не следует использовать флаги трассировки по умолчанию. Конечно, они могут пофиксить проблемы, но они также могу вызвать непредсказуемое или нежелательное поведение. (И, вообще, я не фанат табличных переменных.)

I’ve built a stored procedure that analyses a source table or query with several characteristics per column among which the minimum length (min_len) and maximum length (max_len).

CREATE PROCEDURE [dbo].[sp_analysetable] (
  @tableName varchar(8000),
  @deep bit = 0
) AS

/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/

DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0

IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
  DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
  DROP TABLE ##tmpColumns
END

if CHARINDEX('from', @tableName)>0
  set @isQuery=1

IF @intErrorCode=0 BEGIN
  if @isQuery=1 begin
    --set @tableName = 'USE '+@db+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
    --replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
    set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
    exec(@tableName)
    IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
      set @intErrorCode=1
      SET @errorMSG='Error generating temporary table from query.'
    end
    else begin
      set @tableName='##tmpTableToAnalyse'
    end
  end
end

IF @intErrorCode=0 BEGIN
  SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
  select
    c.column_name as [column],
    cast(sp.value as varchar(1000)) as description,
    tc_fk.constraint_type,
    kcu_pk.table_name as fk_table,
    kcu_pk.column_name as fk_column,
    c.ordinal_position as pos,
    c.column_default as [default],
    c.is_nullable as [null],
    c.data_type,
    c.character_maximum_length as length,
    c.numeric_precision as [precision],
    c.numeric_precision_radix as radix,
    cast(null as bit) as [is_unique],
    cast(null as int) as min_len,
    cast(null as int) as max_len,
    cast(null as int) as nulls,
    cast(null as int) as blanks,
    cast(null as int) as numerics,
    cast(null as int) as distincts,
    cast(null as varchar(500)) as distinct_values,
    cast(null as varchar(50)) as remarks
  into ##tmpColumns'
  if @isQuery=1 begin
    SET @tmpQ=@tmpQ+' from tempdb.information_schema.columns c, (select null as value) sp'
  end
  else begin
    SET @tmpQ=@tmpQ+'
      from information_schema.columns c
      left join sysobjects so    on so.name=c.table_name  and so.xtype=''U''
      left join syscolumns sc    on sc.name=c.column_name and sc.id  =so.id 
      left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''  
      left join information_schema.key_column_usage kcu_fk    on kcu_fk.table_name = c.table_name     and c.column_name = kcu_fk.column_name
      left join information_schema.table_constraints tc_fk    on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
      left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
      left join information_schema.table_constraints tc_pk    on rc.unique_constraint_name = tc_pk.constraint_name
      left join information_schema.key_column_usage kcu_pk    on tc_pk.constraint_name = kcu_pk.constraint_name
 '
  end
  SET @tmpQ=@tmpQ+' where c.table_name = '''+@tableName+''''

  exec(@tmpQ)
end

IF @intErrorCode=0 AND @deep = 1 BEGIN
  DECLARE
    @count_rows int,
    @count_distinct int,
    @count_nulls int,
    @count_blanks int,
    @count_numerics int,
    @min_len int,
    @max_len int,
    @distinct_values varchar(500)
  DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
    select [column] from ##tmpColumns;
  OPEN curTmp
  FETCH NEXT FROM curTmp INTO @column_name
  WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
    set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
      '  @count_rows=count(0), '+char(13)+char(10)+
      '  @count_distinct=count(distinct ['+@column_name+']),'+char(13)+char(10)+
      '  @count_nulls=sum(case when ['+@column_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
      '  @count_blanks=sum(case when ltrim(['+@column_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
      '  @count_numerics=sum(isnumeric(['+@column_name+'])),'+char(13)+char(10)+
      '  @min_len=min(len(['+@column_name+'])),'+char(13)+char(10)+
      '  @max_len=max(len(['+@column_name+']))'+char(13)+char(10)+
      ' from ['+@tableName+']'
    exec sp_executesql @tmpQ,
                       N'@count_rows int OUTPUT,
                         @count_distinct int OUTPUT,
                         @count_nulls int OUTPUT,
                         @count_blanks int OUTPUT,
                         @count_numerics int OUTPUT,
                         @min_len int OUTPUT,
                         @max_len int OUTPUT',
                       @count_rows     OUTPUT,
                       @count_distinct OUTPUT,
                       @count_nulls    OUTPUT,
                       @count_blanks    OUTPUT,
                       @count_numerics OUTPUT,
                       @min_len        OUTPUT,
                       @max_len        OUTPUT

    IF (@count_distinct>10) BEGIN
      SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
    END ELSE BEGIN
      set @distinct_values=null
      set @tmpQ = N'USE '+DB_NAME()+';'+
        '  select @distinct_values=COALESCE(@distinct_values+'',''+cast(['+@column_name+'] as varchar),  cast(['+@column_name+'] as varchar))'+char(13)+char(10)+
        '  from ('+char(13)+char(10)+
        '    select distinct ['+@column_name+'] from ['+@tableName+'] where ['+@column_name+'] is not null) a'+char(13)+char(10)
      exec sp_executesql @tmpQ,
                         N'@distinct_values varchar(500) OUTPUT',
                         @distinct_values        OUTPUT
    END
    UPDATE ##tmpColumns SET
      is_unique      =case when @count_rows=@count_distinct then 1 else 0 end,
      distincts      =@count_distinct,
      nulls          =@count_nulls,
      blanks         =@count_blanks,
      numerics       =@count_numerics,
      min_len        =@min_len,
      max_len        =@max_len,
      distinct_values=@distinct_values,
      remarks       =
        case when @count_rows=@count_nulls then 'all null,' else '' end+
        case when @count_rows=@count_distinct then 'unique,' else '' end+
        case when @count_distinct=0 then 'empty,' else '' end+
        case when @min_len=@max_len then 'same length,' else '' end+
        case when @count_rows=@count_numerics then 'all numeric,' else '' end
    WHERE [column]=@column_name

    FETCH NEXT FROM curTmp INTO @column_name
  END
  CLOSE curTmp DEALLOCATE curTmp
END

IF @intErrorCode=0 BEGIN
  select * from ##tmpColumns order by pos
end

IF @intErrorCode=0 BEGIN --Clean up temporary tables
  IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
    DROP TABLE ##tmpTableToAnalyse
  END
  IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
    DROP TABLE ##tmpColumns
  END
end

IF @intErrorCode<>0 BEGIN
  RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode

I store this procedure in the master database so that I can use it in every database like so:

sp_analysetable 'table_name', 1
// deep=1 for doing value analyses

And the output is:


column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks
id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric,
guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric,
id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty,
optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,


TurboConf — расширение возможностей Конфигуратора 1С

I have created a simple way of finding offending fields by:

1. Getting the column width of all the columns of a table where we’re trying to make this insert/ update. (I’m getting this info directly from the database.)
2. Comparing the column widths to the width of the values we’re trying to insert/ update.

Assumptions/ Limitations:

1. The column names of the table in the database match with the C# entity fields. For eg: If you have a column like this in database:
   

   You need to have your Entity with the same column name:

   public class SomeTable
   {
      // Other fields
      public string SourceData { get; set; }
   }
   

2. You’re inserting/ updating 1 entity at a time. It’ll be clearer in the demo code below. (If you’re doing bulk inserts/ updates, you might want to either modify it or use some other solution.)

Step 1:

Get the column width of all the columns directly from the database:

// For this, I took help from Microsoft docs website:
// 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;
}

Step 2:

Compare the column widths with the width of the values we’re trying to insert/ update:

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

Demo:

Let’s say we’re trying to insert someTableEntity of SomeTable class that is modeled in our app like so:

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

And it’s inside our SomeDbContext like so:

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

This table in Db has SourceData field as varchar(16) like so:

Now we’ll try to insert value that is longer than 16 characters into this field and capture this information:

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.rnThe 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;
        }
    }
}

The badFieldsReport will have this value:

‘SourceData’ column cannot take the value of
‘Blah-Blah-Blah-Blah-Blah-Blah’ because the max length it can take is
16.

Ошибка Microsoft SQL Server: SQL SERVER-Msg 8152, Уровень 16, состояние 14-строковые или двоичные данные будут усечены

Подробности ошибки:

Строковые или двоичные данные будут усечены. Заявление было прекращено.

This error message below occurs when part data is been truncated while loading a table.

Фактическое сообщение об ошибке:

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

Решение:

Либо сократите данные, которые усекаются, либо увеличьте длину столбца в таблице, либо используйте инструкции below вверху и внизу инструкции sql insert, которая подавляет ошибку.

SET ANSI_WARNINGS OFF;
-- Insert TSQL here.
SET ANSI_WARNINGS ON;

При использовании Andi_Warnings off ошибка будет подавлена в приведенном выше коде, но мы потеряли часть фактических данных, которые должны были быть у нас в таблице. Поэтому будьте осторожны при работе с такими заданными опциями.

Дополнительная информация ниже:

Используйте ANSI_WARNING OFF с осторожностью, так как иногда важные данные могут быть усечены или потеряны. Также оказывает негативное влияние на производительность оператора insert.