Sql error 8152 22001 string or binary data would be truncated - Исправление ошибок и поиск оптимальных решений проблем

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 id_individual NULL id_brand NULL guid NULL customer_id NULL email NULL mobile NULL initials NULL title_short NULL title_long NULL firstname NULL lastname NULL address NULL pc NULL kixcode NULL date_created NULL created_by NULL id_location_created NULL id_individual_type NULL optin NULL 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
PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric,
NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
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,
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,
NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,

Источник

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 всё еще сообщает об усечении строки, даже если строка не вставляется:

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

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

Источник

Remove From My Forums

Вопрос
Здравствуйте, у меня на SCCM 2012 SP1 возникает следующая ошибка

*** [22001][8152][Microsoft][SQL Server Native Client 11.0][SQL Server]String or binary data would be truncated. : dINSTALLED_SOFTWARE_DATA

Как я понял? на SCCM сервер приходит отчет об инвентаризации програмного обеспечения, и сервер пытается эти данные записать в таблицу dINSTALLED_SOFTWARE_DATA на сиквел сервере. Но то ли в этом отчете есть длинные строчки, то ли целиком
файл для sql большой, и сиквел не принимает данные от SCCM сервера

Я запустил на SQL сервере profiler, но он выдает ту же самую ошибку String or binary data would be truncated. Но что имено нужно обрезать, не говорит.

Можно ли узнать, что конкретно не нравится сиквелу во входящих данных?

Я в профайлере включил, в дополнение к стандартным данным, вывод всех ошибок.

Ответы

Для будущих поколений:

Проблема возникает при записи в таблицу INSTALLED_SOFTWARE_HIST, чье поле ServicePack00 имеет формат nvarchar(8), в то время в таблице, откуда копируются данные — INSTALLED_SOFTWARE_DATA — формат этого поля nvarchar(255).

Простое решение — это расширить проблемное поле до nvarchar(255), но без указки инженера Microsoft это переводит базу в состояние unsupported.

Проблема на самом деле заключается еще и в том, что в идеале в это поле никогда не должны писаться данные длиннее 1 символа. Если проверить содержимое этого поля после инвентаризации чистой машины, то там будет «1» в случае версии Service Pack 1. Однако
в случае кастомных заливок в данных инвентаризации появляются 2 инстанса операционной системы, одна нормальная, вторая же содержит в этом поле «Service Pack 1» — 14 символов.

Источник появления второго инстанса ОС при инвентаризации WMI-класса Installed_Software пока неизвестен, скорее всего это какое-то расхождение в 32- и 64-хбитных ветках реестра HKLMSoftwareMicrosoftWindows NTCurrentVersion.

Правильное решение в этом случае: перезалить машину чистой проверенной заливкой или отключить инвентаризацию этого класса.

Тест машины на «кривость» — в консоли PS от имени администратора при установленном клиенте запустить:

gwmi -namespace rootcimv2sms -query «select * from sms_installedsoftware
where Servicepack<>»»

Если выдается 1 инстанс — все хорошо, если два — машину стоит перезалить.
- Предложено в качестве ответа
  
  9 октября 2013 г. 14:49
- Помечено в качестве ответа
  Иван ПродановMicrosoft contingent staff, Moderator
  10 октября 2013 г. 4:57
Всё разобрался.

нужно включить в Profiler отображение SP: STMTStarting ещё.

так как SQL: StmtStarting показывает только выполнение хранимой процедуры (например, dbo.dInstalled_Software_Data или dbo.pInstalled_Software_Data), а процедура, как оказалось, пишет не в одну таблицу, а в несколько разных, и SP: STMTStarting, как раз, показывает,
какая таблица не может принять данные (в моем случае, это оказалась совершенно левая таблица).

Правда, не удалось разобраться, как узнать, какое поле при этом нужно увеличивать. Пришлось увеличить все nvarchar. Благо полей оказалось не так много.

Источник

To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level.

First, let’s see the error happen: let’s create a table with small fields, and then try to insert more data than it holds.

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

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)

VALUES (‘Baby’, ‘2006 Subaru Impreza WRX GD’);

Baby’s car is longer than 20 characters, so when the insert statement runs, we get an error:

Msg 8152, Level 16, State 30, Line 5

String or binary data would be truncated.

The statement has been terminated.

That sucks because we have no idea which field is causing the problem! It’s especially terrible when you’re trying to insert lots of rows.

If you’re on SQL Server 2019+,
here’s how to fix it.

Run this in the database where you want specific truncation warnings, and it takes effect right away:

ALTER DATABASE SCOPED CONFIGURATION

SET VERBOSE_TRUNCATION_WARNINGS = ON;

Another way you could fix it is to upgrade your database’s compatibility level to 2019+ (150+), but that causes a whole bunch of other things to take effect too, like adaptive memory grants, and it’s going to require a whole lot more testing with your code. Just keep it simple for now and run the above if all you’re trying to do is know which column & row is being truncated.

If you’re on SQL Server 2016-2017:
to fix it, turn on trace flag 460.

Trace flag 460 was introduced in SQL Server 2016 Service Pack 2, Cumulative Update 6, and in SQL Server 2017. (You can find & download the most recent updates at SQLServerUpdates.com.) You can turn it on at the query level, like this:

INSERT INTO dbo.CoolPeople(PersonName, PrimaryCar)

VALUES (‘Baby’, ‘2006 Subaru Impreza WRX GD’)

OPTION (QUERYTRACEON 460);

And now when the query runs, it shows you which column is getting clipped, and which row, too. In our case, we’ve only got one row – but in your real-life data, you’ll be much happier knowing which row’s triggering the error:

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 ‘.

You can turn on this trace flag at the query level as shown above, or at the server level:

That turns it on for everybody, not just you – so get everybody on your team to agree before you turn it on. This changes the error message number from 8152 to 2628 (as shown above), which means if you’ve built error handling based on the error number, you’re suddenly going to start getting different behavior.

I’m a fan of turning this trace flag on while I’m doing troubleshooting, and then once I’ve found the culprit, turning it back off again:

DBCC TRACEOFF(460, —1);

In our case, once we’ve identified that Baby’s car is too large, we either need to change his car, or change the datatype in our table to make it bigger, or cast the data on the way in to explicitly strip the extra length off his car. A data chop shop, if you will.

Don’t leave this trace flag enabled.

There’s at least one bug with it as of today on SQL Server 2017 CU13: table variables will throw errors saying their contents are being truncated even when no data is going into them. Andrew Pruski reports:

Here’s that error that we were seeing @arrowdrive

Should be fixed now but one to watch out for. https://t.co/WbPrI1YHHJ

— Andrew Pruski ??????? (@dbafromthecold) March 20, 2019

Sadly, that behavior is NOT fixed, and here’s the simplest repro script I could build:

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

BEGIN

DECLARE @Table TABLE ( SmallString VARCHAR(128) )

IF ( 1 = 0 )

/* This will never run */

INSERT INTO @Table ( SmallString )

VALUES(@BigString)

OPTION (QUERYTRACEON 460)

END

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

EXEC dbo.Repro @BigString

SQL Server 2017 CU13 still reports that the string will be truncated even though the insert doesn’t run:

Switch out the table variable for a temp table, and it works fine, as expected:

If you want to follow progress on that bug getting fixed, it’s here. Such a great example of why I’m not a fan of using trace flags by default – sure, they can fix issues, but they can also introduce unpredicted, unwanted behaviors. (And, uh, not a fan of table variables either.)

Update 2019/03/25 – the above bug is fixed in 2017 Cumulative Update 14. Yay!

Источник

In this SQL Server tutorial, we will understand the “String or binary data would be truncated” error in a SQL Server 2019, and we will also learn how to resolve this error. For this, we will cover the following topics.

String or binary data would be truncated SQL Server Error
String or binary data would be truncated SQL Server Which Column
How to fix “String or binary data would be truncated”
String or binary data would be truncated SQL Server 2019
String or binary data would be truncated Datetime SQL Server
String or binary data would be truncated in SQL Server Stored Procedure

Error Message

This error usually occurs when we try to insert more data into a column than the specified column can store. Let’s understand this with the help of an example.

CREATE TABLE Sample (
	id INT,
	Name VARCHAR(10),
	gender VARCHAR(8)
);
insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender');

In the example, we are creating a table with 3 columns in it, and for the Name column, we have defined the data type as “VARCHAR(10)“. It means that the Name column can hold a string having 10 characters.

But while inserting a value into the table, we have specified the value for the Name column with 15 characters which is more than 10.

So the above query will return the following error.

string or binary data would be truncated in sql server

Example Result

Read SQL Server bulk insert from CSV file

String or binary data would be truncated SQL Server Which Column

Till now, we have understood the reason why we are getting this error. Now let’s understand how we can find the column due to which we are getting the error.

In SQL Server 2019, Microsoft has updated the error message for this error. For SQL Server 2019, the error message not only returns the column name but also returns the table for which the error has been encountered.

For demonstration, consider the error message that we have shown in the previous section.

string or binary data would be truncated in sql server error

Identifying Column

Now, if we read the error message thoroughly, we will easily get the column name for which the error has been encountered.

If you have upgraded from some previous SQL Server version to SQL Server 2019. Then you might need to execute the following query in the database where you want a complete truncate warning.

ALTER DATABASE SCOPED CONFIGURATION 
SET VERBOSE_TRUNCATION_WARNINGS = ON;

And for SQL Server 2016/2017, we have to turn on the trace flag 460. And to enable the trace flag 460, we can execute the following query while inserting or updated table records.

insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender')
OPTION (QUERYTRACEON 460);

Now by enabling the trace flag 460, the SQL Server will return a detailed truncation warning, from which we can easily identify the column name.

Read: Identity Column in SQL Server

How to fix “String or binary data would be truncated”

The main reason behind this error is the more amount of data that we are trying to store in a column than a specific column can store. So a quick solution to solve this error is by increase the column size. For this, we can use the ALTER TABLE command and increase the column size.

For demonstration, consider the following query, used to modify the column size of the sample table.

--Modifying Table
ALTER TABLE Sample
ALTER COLUMN Name VARCHAR(20)
GO

--Inserting the new record
insert into Sample (id, Name, gender) values (1, 'Maurits Hessing', 'Agender')
GO

In the above example, first, we are using the ALTER TABLE and ALTER COLUMN statements to modify the size of the column. After this, we are using the INSERT statement to insert a new record. And if the inserted value is less than or equal to the size of the column, then the new record will be inserted successfully.

string or binary data would be truncated in sql server stored procedure

Successful Insertion

Read: Create Foreign Key in SQL Server

String or binary data would be truncated SQL Server 2019

While using SQL Server earlier versions, if we try to insert more data than specified for a column, we get the following error.

Error Message before SQL Server 2019

Now, this error message is not much helpful in debugging the issue. For this, we have to manually check the query to find and resolve the error.

But Microsoft has made some enhancements for this error message in SQL Server 2019. So, in SQL Server 2019, if this error has been encountered, the SQL Server will return a detailed truncate warning.

The column name, table name, even the value for which the error has been raised is mentioned in the error message. The example is already shown in the “String or binary data would be truncated SQL Server Which Column” topic.

If you are already using SQL Server 2019, but still not getting the detailed error message. Then execute the following query in the database where you want a complete truncate warning.

ALTER DATABASE SCOPED CONFIGURATION 
SET VERBOSE_TRUNCATION_WARNINGS = ON;

String or binary data would be truncated Datetime SQL Server

This issue commonly occurs when entering a record into a table with a VARCHAR or CHAR data type column and a value is longer than the column’s length. We will not get this error in the case of a Datetime datatype.

In the case of the DateTime data type, if we try to insert a longer value than the column size then the column will ignore the extra characters and save the value.

Now for demonstration, consider the following example given below.

--Creating table
CREATE TABLE Sample (
	id INT,
	Name VARCHAR(50),
	Joining_date DATETIME
);

--inserting values
insert into Sample (id, Name, Joining_date) values (1, 'Modesty Malley', SYSDATETIME());

In the example, we are creating a table named “Sample” with 3 columns.
Out of these 3 columns, we have defined the “Joining_date” column to be a DateTime column.
Now, the Datetime column has a default fractional precision of 3, which means it can store a maximum of 3 characters in nanoseconds part (yyyy-mm-dd hh:mm:ss:nnn).
But while inserting values, we are using the SYSDATETIME() function which returns the current date-time value in Datetime2 format. The SYSDATETIME() will return the date-time value with a fractional seconds precision of 7 (yyyy-mm-dd hh:mm:ss:nnnnnnn).
But still, the value got successfully inserted in the Datetime column, but it has ignored the last 4 characters.

String or binary data would be truncated datetime

Inserted Value

Read: Types of Backup in SQL Server

String or binary data would be truncated in SQL Server Stored Procedure

This error can also occur while executing a stored procedure in SQL Server, which is trying to insert new values in a column. But still, the reason behind this error will remain the same i.e, the column length is less than the value length that we are trying to insert.

Let’s understand this with the help of an example. And for this, we are using the following query to create a sample table.

--Creating table
CREATE TABLE Sample (
	id INT,
	Name VARCHAR(5),
	Joining_date DATETIME
);

Now take a look at the Name column, for the Name column we have defined the character length to be 5.

Next, we are using the following stored procedure to insert new records in the sample table.

ALTER PROCEDURE Insert_data
AS
--inserting values
insert into Sample (id, Name, Joining_date) values (1, 'Modesty Malley', GETDATE());

GO

Exec Insert_data

But while inserting the record through the stored procedure, we have specified the name value to be 14 characters. So, if we try to execute this procedure, we will get the following error.

String or binary data would be truncated stored procedure

Error while executing stored procedure

And the simplest solution to resolve this error is by modifying the column and increasing the column length. This solution is already been discussed in the topic above.

You may like the following SQL server tutorials:

SQL Server Row_Number
SQL Server Add Column + Examples
IDENTITY_INSERT in SQL Server
SQL Server stored procedure output parameter

So in this SQL Server tutorial, we have learned how to solve the “String or binary data would be truncated” error in a SQL Server, and we have also covered the below topics.

String or binary data would be truncated SQL Server Error
String or binary data would be truncated SQL Server Which Column
How to fix “String or binary data would be truncated”
String or binary data would be truncated SQL Server 2019
String or binary data would be truncated Datetime SQL Server
String or binary data would be truncated in SQL Server Stored Procedure

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.

Источник

First published on MSDN on Oct 24, 2018
In the recent announcement at Ignite 2018 on the release of SQL Server 2019 CTP 2.0, the new Big Data Clusters , data virtualization, support for UTF-8 , and Intelligent Query Processing were highlights. But we have also previewed work being done to address the infamous error message “String or binary data would be truncated”.

This error condition may happen for example when you implement an ETL between a source and a destination that does not have matching data types and/or length. In this context, the message «String or binary data would be truncated» is one of the most time-consuming troubleshooting processes to engage in, especially in large datasets. You probably know about and voted for this feedback item before.

Let’s see an example of what happens when you insert data into a column whose size is not big enough to store it:

USE [AdventureWorks2016CTP3]
GO
DROP TABLE IF EXISTS [Sales].[SalesOrderHeaderTest]
GO
CREATE TABLE [Sales].[SalesOrderHeaderTest](
[SalesOrderID] [INT] NOT NULL,
[CustomerID] [INT] NOT NULL,
[CreditCardApprovalCode] [nvarchar](13) NULL
)
GO

INSERT INTO [Sales].[SalesOrderHeaderTest]
SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode]
FROM [Sales].[SalesOrderHeader]
GO

You receive following error message, which admittedly is not very helpful:

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

We heard that. Which is why SQL Server 2019 introduces a new message , with additional context information. For the same operation, the new error message outputs the following:

Msg 2628, Level 16, State 1, Line 14
String or binary data would be truncated in table ‘AdventureWorks2016CTP3.Sales.SalesOrderHeaderTest’, column ‘CreditCardApprovalCode’. Truncated value: ‘1231736Vi8604’.
The statement has been terminated.

Ok, the new error message provides more context, and now I have the resulting truncated value (not the source value). This is simplifying the troubleshooting process, because now I know the truncated value starts with ‘1231736Vi8604′ – that’s 13 characters. And so I can go back to my data source, and locate the source record and its length:

SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode], LEN([CreditCardApprovalCode])
FROM [Sales].[SalesOrderHeader]
WHERE CreditCardApprovalCode LIKE ‘1231736Vi8604%’

It’s 14 characters, and in my table definition I have a NVARCHAR(13). Well, I know what I need to do: change my table data type length.

This new message is also backported to SQL Server 2017 CU12 and in SQL Server 2016 SP2 CU6, but not by default. You need to enable trace flag 460 to replace message ID 8152 with 2628, either at the session or server level.

Note that for now, even in SQL Server 2019 CTP 2.0 the same trace flag 460 needs to be enabled. In a future SQL Server 2019 release, message 2628 will replace message 8152 by default.

EDIT (3/29/2019): For SQL Server 2019 CTP 2.4, Message 2628 becomes default under Database Compatibility Level 150. You can use the Database Scoped Configuration VERBOSE_TRUNCATION_WARNINGS to revert to back to Message 8152 as default. You can also use a lower Database Compatibility Level to revert back to Message 8152 as default.

Is there a limit to how much of my truncated string this error can return?
Let’s run a small test inserting a 123 character string into a VARCHAR(120):

CREATE TABLE myTable (myString VARCHAR(120));
GO
INSERT INTO myTable
VALUES (‘Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.’)
GO

Although my string gets truncated at 120 characters, the offending value that is shown is truncated to the first 100 characters:

Msg 2628, Level 16, State 1, Line 30
String or binary data would be truncated in table ‘AdventureWorks2016CTP3.dbo.myTable’, column ‘myString’. Truncated value: ‘Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore ‘.

Still plenty to find offending values in most data sources.

Pedro Lopes ( @SQLPedro ) – Senior Program Manager

Источник

Содержание

SQL-Ex blog
Как исправить ошибку «Символьные или двоичные данные могут быть усечены»
Чтобы пофиксить ошибку, включите флаг трассировки 460
Не оставляйте этот флаг включенным
Обратные ссылки
Комментарии
Sql server 8152 error
Answered by:
Question
Заметки системного администратора Windows Server
Поиск по этому блогу
Ошибка Microsoft SQL Server: SQL SERVER-Msg 8152, Уровень 16, состояние 14-строковые или двоичные данные будут усечены
Подробности ошибки:
Фактическое сообщение об ошибке:
Решение:
Sql server 8152 error
Вопрос
Ответы
Все ответы

SQL-Ex blog

Новости сайта «Упражнения SQL», статьи и переводы

Как исправить ошибку «Символьные или двоичные данные могут быть усечены»

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

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

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

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

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

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

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

Обратные ссылки

Нет обратных ссылок

Sql server 8152 error

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

I’ve been working with a sample database that the company is using for testing purposes. I (we) did not create the database — it was sent over to us by another company. I’m still a rank newbie at working with MS SQL Server, though I’ve worked with Access and MySQL in the past.

There is one table that contains bank information. At the moment, it is filled with information on imaginary (fake) banks. I need to change one record so that it contains the information of a real bank the company is using.

The problem is, I am unable to touch anything within this table. Any attempt to make changes gives me an error prompt that reads «String or binary data would be truncated». I ran the profiler, and it shows an Exception — Error: 8152 Severity 16 State 2.

Furthermore, I also get an error prompt stating: «The value you entered is not consistent with the data type or length of this column».

I’ve checked and checked again, and as far as I can tell, the value I entered _is_ consistent with the data type/length of the column.

I can make changes perfectly fine on the other tables in the database. Only this one table gives me trouble.

Could anyone shed some light on why exactly this is occurring, and why only on this one table?

Источник

Заметки системного администратора Windows Server

Поиск по этому блогу

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

Получить ссылку
Facebook
Twitter
Pinterest
Электронная почта
Другие приложения

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

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

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.

Источник

Sql server 8152 error

Вопрос

Здравствуйте, у меня на SCCM 2012 SP1 возникает следующая ошибка

*** [22001][8152][Microsoft][SQL Server Native Client 11.0][SQL Server]String or binary data would be truncated. : dINSTALLED_SOFTWARE_DATA

Как я понял? на SCCM сервер приходит отчет об инвентаризации програмного обеспечения, и сервер пытается эти данные записать в таблицу dINSTALLED_SOFTWARE_DATA на сиквел сервере. Но то ли в этом отчете есть длинные строчки, то ли целиком файл для sql большой, и сиквел не принимает данные от SCCM сервера

Я запустил на SQL сервере profiler, но он выдает ту же самую ошибку String or binary data would be truncated. Но что имено нужно обрезать, не говорит.

Можно ли узнать, что конкретно не нравится сиквелу во входящих данных?

Я в профайлере включил, в дополнение к стандартным данным, вывод всех ошибок.

Ответы

Для будущих поколений:

Проблема возникает при записи в таблицу INSTALLED_SOFTWARE_HIST, чье поле ServicePack00 имеет формат nvarchar(8), в то время в таблице, откуда копируются данные — INSTALLED_SOFTWARE_DATA — формат этого поля nvarchar(255).

Простое решение — это расширить проблемное поле до nvarchar(255), но без указки инженера Microsoft это переводит базу в состояние unsupported.

Проблема на самом деле заключается еще и в том, что в идеале в это поле никогда не должны писаться данные длиннее 1 символа. Если проверить содержимое этого поля после инвентаризации чистой машины, то там будет «1» в случае версии Service Pack 1. Однако в случае кастомных заливок в данных инвентаризации появляются 2 инстанса операционной системы, одна нормальная, вторая же содержит в этом поле «Service Pack 1» — 14 символов.

Источник появления второго инстанса ОС при инвентаризации WMI-класса Installed_Software пока неизвестен, скорее всего это какое-то расхождение в 32- и 64-хбитных ветках реестра HKLMSoftwareMicrosoftWindows NTCurrentVersion.

Правильное решение в этом случае: перезалить машину чистой проверенной заливкой или отключить инвентаризацию этого класса.

Тест машины на «кривость» — в консоли PS от имени администратора при установленном клиенте запустить: gwmi -namespace rootcimv2sms -query «select * from sms_installedsoftware
where Servicepack<>»»

Если выдается 1 инстанс — все хорошо, если два — машину стоит перезалить.

Предложено в качестве ответа Pavel Yurenev Microsoft employee 9 октября 2013 г. 14:49
Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 10 октября 2013 г. 4:57

нужно включить в Profiler отображение SP: STMTStarting ещё.

так как SQL: StmtStarting показывает только выполнение хранимой процедуры (например, dbo.dInstalled_Software_Data или dbo.pInstalled_Software_Data), а процедура, как оказалось, пишет не в одну таблицу, а в несколько разных, и SP: STMTStarting, как раз, показывает, какая таблица не может принять данные (в моем случае, это оказалась совершенно левая таблица).

Правда, не удалось разобраться, как узнать, какое поле при этом нужно увеличивать. Пришлось увеличить все nvarchar. Благо полей оказалось не так много.

Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 28 мая 2013 г. 14:15

Все ответы

Достаточно в профайлере добавить 2 события:

SQL: BatchStarting и Exception

Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated.

The statement has been terminated.

Вот этот SQL:BatchStarting содержит данные вида

exec dbo.dINSTALLED_SOFTWARE_DATA 4,16777259,’05/27/2013 10:05:27′,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N’‘,NULL,N’Microsoft Visual C++ 2008 Redistributable — x86 9.0.30729.4148′,N’9.0.30729.4148′,NULL,NULL,NULL,N’<1f1c2dfc-2d24-3e06-bcb8-725134adf989>‘,NULL,NULL,N’MsiExec.exe /X<1f1c2dfc-2d24-3e06-bcb8-725134adf989>‘,NULL,NULL,NULL exec dbo.dINSTALLED_SOFTWARE_DATA 4,16777259,’05/27/2013 10:05:27′,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N’‘,NULL,N’Microsoft Visual C++ 2010 x86 Redistributable — 10.0.30319′,N’10.0.30319′,NULL,NULL,NULL,N’<196bb40d-1578-3d01-b289-befc77a11a1e>‘,NULL,NULL,N’MsiExec.exe /X<196bb40d-1578-3d01-b289-befc77a11a1e>‘,NULL,NULL,NULL exec dbo.dINSTALLED_SOFTWARE_DATA 4,16777259,’05/27/2013 10:05:27′,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,N’<6b1a624c-dedd-4728-8f85-04f648ae1262>‘,N’1′,N’КОМПАС-3D V13′,N’13.0.2′,NULL,NULL,NULL,N’‘,NULL,NULL,N’MsiExec.exe /I‘,NULL,NULL,NULL

и таких данных несколько экранов. Просто я не совсем в курсе этой ошибки. Она значит, что значение в каком-то столбце превышает допустимую длину переменной, или это может значить, что стоит null, а null запрещен, или несовпадает формат даты времени, или вот эти несколько страниц данных нельзя запихивать в сиквел одновременно?

По поводу первого, скорее всего нет, так как я смотрел, самая большая строчка 180 символов, а переменная nvarchar(255).

По поводу остального, не знаю как проверить.

Да, в одной такой записи, после exec.dbo стоят и другие таблицы из той же базы. И exeption звучит имено так

String or binary data would be truncated.

А про номер ошибки я могу узнать только из логов SCCM. Вряд ли он сам знает, что это за ошибка. Скорее всего ему эти данные дает SQL. А вот где они записаны, не понятно. В тех логах которые я включил, про 8152 ничего нет.

После этого exeption идут другие, так как запись в эту таблицу (dINSTALLED_SOFTWARE_DATA) не произошла, начинают сыпаться ошибки про таблицу содержащую суммарные данные и т.д. (Там кстати ошибка вполне конкретная: Cannot insert the value NULL into column ‘RevisionID’, table ‘CM_OPT.dbo.INSTALLED_SOFTWARE_HIST’; column does not allow nulls. INSERT fails.) Но это из-за того, что не произошла вставка первоночальных данных.

но там 15 столбцов, какой именно столбец не подходит?

И как я понял нельзя руками менять длинну переменной?

но там 15 столбцов, какой именно столбец не подходит?

И как я понял нельзя руками менять длинну переменной?

1) какой столбец вам придётся определить самостоятельно, проанализировав входные данные и размерность полей таблицы

2) что вы имеете в виду?

1) данные я проанализировал, они меньше длинны переменной. Ну, насколько это возможно, там несколько мегабайт данных, может быть что-то и пропустил.

А нет ли возможности определить это автоматически?

2) я могу в таблице выделить любой столбец, нажать изменить и поменять тип переменной, нпример, вместо nvarchar(255) поставить nvarchar(511). Но где-то было написано, что так лучше не делать.

1) данные я проанализировал, они меньше длинны переменной. Ну, насколько это возможно, там несколько мегабайт данных, может быть что-то и пропустил.

А нет ли возможности определить это автоматически?

ошибка, которую вы получаете связана с размером полей, а не переменных.

изменение размерности поля в таблице — это штатная ситуация

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

Если нажать кнопку изменить на столбце, то я вижу название столбца, его data type, и может быть он null или нет.

Data type для столбца я изменить не могу, так как в этом случае будет пересоздана вся таблица, а в её свойствах запрещено пересоздание.

Altering a Column Definition

A. Changing the data type of a column
B. Changing the size of a column

Спасибо, но не помогло. Ошибка осталась.

Да, действительно, ошибка осталась, но блоков данных стало приниматься больше. То есть SCCM передает данные блоками, и раньше сиквел падал на третьем блоке, а теперь на седьмом.

Но это как иголку в стоге сена 🙁 несколько сотен баз и половина полей в них не хочет увеличиваться.

Как я понял раньше (до 2012 версии) сиквел посылал конкретные сообщения в SCCM — вот этот столбец слишком узкий, а тот, в свою очередь, посылал сиквелу alter этот столбец. A теперь безликая ошибка и соответственно автоматически ситуацию не исправить.

Про set ansi_warnings

насколько я понял, это относится к INSERT и UPDATE. А в моем случае EXEC.

Сам по себе exec ничего не вставляет, вставка осуществляется через команду INSERT, поэтому вам достаточно в процедуру добавить эту настройку и все вставки пройдут без ошибок, НО(!) имейте в виду, что вы потеряете часть данных т.к. они усекутся под размер ваших полей.

И всё-таки не думаю, что так уж сложно найти в какое поле не могут быть записаны данные, профайлер в руки и несколько минут вашего времени должны помочь найти причину!

вот, я и не пойму как это сделать. Несколько сот классов инвентаризации. Можно по очереди включать их, потом дожидаться, когда на всех клиентах пройдет инвентаризация и проверять, что нет ошибок. В один блок данных попадает около трех таблиц, по 10-15 полей в каждой. это несколько недель, а не минут.

А если потом появится новый клиент со специфическим ПО или драйвером и нужно заново проверять все триста полей.

Что-то тут не так.

Спасибо, будем искать специалиста.

Но всё равно спасибо за помощь.

нужно включить в Profiler отображение SP: STMTStarting ещё.

Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 28 мая 2013 г. 14:15

Для будущих поколений:

Если выдается 1 инстанс — все хорошо, если два — машину стоит перезалить.

Предложено в качестве ответа Pavel Yurenev Microsoft employee 9 октября 2013 г. 14:49
Помечено в качестве ответа Иван Проданов Microsoft contingent staff, Moderator 10 октября 2013 г. 4:57

Неужели я такой счастливчик, что единственный словил этот баг.

А не подскажете как зарегистрировать можно?

А не подскажете как зарегистрировать можно?

В R2 точно не поправлено, т.к. не зарегистрировано как баг. Sad but true.

Большое спасибо, у меня руки так и не дошли зарегистрировать баг.

Правильное решение в этом случае: перезалить машину чистой проверенной заливкой или отключить инвентаризацию этого класса.

Попробовал на паре(20) машин, данные обновились:

1. Делаем коллекцию из старых машин(старые данные HardwareInventory):

select SMS_R_SYSTEM.ResourceID,SMS_R_SYSTEM.ResourceType,SMS_R_SYSTEM.Name,SMS_R_SYSTEM.SMSUniqueIdentifier,SMS_R_SYSTEM.ResourceDomainORWorkgroup,SMS_R_SYSTEM.Client from SMS_R_System inner join SMS_G_System_WORKSTATION_STATUS on SMS_G_System_WORKSTATION_STATUS.ResourceId = SMS_R_System.ResourceId where SMS_G_System_WORKSTATION_STATUS.LastHardwareScan

Если есть контракт премьер-поддержки, то Вы можете попросту попросить разрешение у меня, открыв заявку. В свою очередь я Вам буду настоятельно рекомендовать перезалить машины проверенным образом, как я писал выше.

Если честно, если Вы поменяете базу самостоятельно, с вероятностью 95% проблем не будет, а инженер поддержки этого попросту не заметит, если Вы сами ему не расскажете. 🙂

P.S. Фикс отложили на следующий релиз SCCM, который выйдет в 2015 году.

Столкнулся с такой же проблемой на SCCM 2012 R2 5.00.7958.1000.
Машины залиты проверенным, созданным и залитым через SCCM образом ОС Windows 7.
Проблема возникла словно сама собой месяца три назад, т.к. классы инвентаризации не менялись.
Сейчас проблемных машин — 80%.

1. Как мне получить это одобрение на модификацию БД (куплен EAS)?
2. Где можно увидеть в каком состоянии БД «unsupported» или «supported»?
3. Если она перейдет с состояние «unsupported» (после изменения длинны поля) — чем это чревато?
4. Где можно найти официальное описание/статус зарегистрированного бага SCCM 2012 R2 «bug (SMS 410599)» — «SMS_INVENTORY_DATA_LOADER Microsoft SQL Server 8152 SQL issued a message, the importance of 16: [22001] [8152] [Microsoft] [SQL Server Native Client 11.0] [SQL Server] String or binary data would be truncated. : pINSTALLED_SOFTWARE_DATA«?

Если выдается 1 инстанс — все хорошо, если два — машину стоит перезалить.

А какой тут у меня инстанс на скриншоте, Подскажите.

Источник

Reading Time: 8 minutes

Speak guys!
In this article, I want to explain to you exactly what the message “String or binary data would be truncated” means, how can we identify which string is causing the error, how to hide this error message (if you want), what the change in sys.messages impacted this theme from SQL Server 2016+ and more!

So if you have trouble identifying and correcting occurrences of this error message, today will be the last time this will still be a problem for you.

One of the most common SQL Server errors, the message “String or binary data would be truncated” occurs when a value is trying to be inserted or updated in a table and it is larger than the maximum field size.

Example 1 — Maximum field size with 10 characters, string with 10:

Example 2 — Maximum field size with 10 characters, string with 11:

Example 3 — Difficult to identify the error

Look at the example 3. I am no longer entering a fixed value but from another table. This kind of situation may seem simple in a scenario such as the 1 and 2 examples, but when you are entering multiple records, especially taking data from multiple columns, it is difficult to identify which record and which column is causing this error message and This task may end up spending more time than you would like.

How to Bypass String Truncation

If you want to bypass string truncation at a given time or operation, you have the option to do so in SQL Server, as already shown in the article SQL Server — Why Not Use SET ANSI_WARNINGS OFF. I do not recommend using this technique at all, as it is a solution that is only masking the problem and not correcting it, but I would like to show that it exists and that it can be done.

When you use the SET ANSI_WARNINGS OFF command, you cause the SQL Server engine to no longer generate this error on execution, causing your 14 character text to be truncated and stored in the 10 character column. Excess characters will be silently discarded, ignoring and masking a problem writing your system data without anyone knowing.

Example:

SET NOCOUNT ON

SET ANSI_WARNINGS OFF

IF ((@@OPTIONS & 8) > 0) PRINT ‘SET ANSI_WARNINGS is ON’

IF (OBJECT_ID(‘tempdb..#Teste’) IS NOT NULL) DROP TABLE #Teste

CREATE TABLE #Teste ( Nome VARCHAR(10) )

INSERT INTO #Teste

VALUES (‘Dirceu Resende’) — 14 caracteres

SELECT * FROM #Teste

Return with SET ANSI_WARNINGS ON (Default):

Return with SET ANSI_WARNINGS OFF:

String or binary data would be truncated in SQL Server 2019

SQL Server 2019 was released on 04 November 2019, during Microsoft Ignite, and with it, a huge range of new features were officially released.

One of these new features is the new posts available on sys.messages, which I had already shared with you in my article. SQL Server 2019 — News and Features List since late August of 2018:

With this change, it’s now much easier to identify exactly where the truncation of values is taking place:

According to traceflags documentation, the 460 traceflag replaces the data truncation 8152 ID message with the 2628 ID message. At the 150 database compatibility level onwards (2019), the 2628 ID message is the default and the 460 traceflag has no effect.

That is, this new behavior will only work automatically in SQL Server 2019 if the connection database is in 150 compatibility mode onwards.

Observation: This change to SQL Server 2019 sys.messages began in the CTP2.0 release and until the final release came out, it was not automatically activated, requiring the same changes as the 2017 and 2016 versions to work.

What has changed from SQL Server 2016 +

With the changes required for this implementation in SQL Server 2019, Microsoft eventually released this new message also in the 2016 (from SP2 Cumulative Update 6) and 2017 (From Cumulative Update 12) versions. And to be able to use this new message, we can use 2 in different ways:

1 Form: Using Initialization Parameter -T460

The first and most practical way is to enable 460 traceflag on the entire instance using the -T460 initialization parameter in the SQL Server service:

Once added, it will be necessary to restart the service and from then on, the new message will be working by default, without having to make any query changes:

2 Form: Using 460 Traceflag

One way that does not require restarting the SQL Server service and also does not require changing code is by using the DBCC TRACEON command, which allows you to enable this session level and instance level (global) traceflag:

Session level (only affects your session):

Instance level (global — affects all sessions):

Script used:

PRINT @@VERSION

CREATE TABLE dbo.Teste (

nome1 VARCHAR(10),

nome2 VARCHAR(10),

nome3 VARCHAR(10)

)

— Modo padrão — Mensagem anterior

INSERT INTO dbo.Teste (nome1, nome2, nome3)

VALUES(‘123456789’, ‘1234567890’, ‘12345678901’)

— Nível de instância (global — afeta todas as sessões)

DBCC TRACEON(460, —1);

— Nível de sessão (afeta apenas a sua sessão)

DBCC TRACEON(460);

— Após ativar a traceflag, a mensagem é alterada

INSERT INTO dbo.Teste (nome1, nome2, nome3)

VALUES(‘123456789’, ‘1234567890’, ‘12345678901’)

Remarks: To disable traceflag, use the DBCC TRACEOFF (460) or DBCC TRACEOFF (460, -1) command, depending on the parameter used for activation. If you have enabled instance-level traceflag, affecting all sessions, when you restart the SQL Server service, you must enable traceflag again.

There was a bug in SQL Server 2017 CU12 and CU13, which caused this “String or binary data would be truncated” error in insertion operations involving table variables, when the string size was larger than the field even in cases where the code was not executed (Ex: Within an IF with condition 1 = 0). This was fixed in SQL Server 2017 CU14.

Identifying String Truncation Before SQL Server 2016

If you are using a version prior to 2016 and have this difficulty in identifying which values are breaking the limit of any column, I will share a simple script, which can identify this type of error even in the old versions of SQL Server, creating a table “clone” of the original, with the size of the columns set to the maximum, inserting the data in this new table and comparing the size used by the size of the columns in the original table.

Example situation where script can be used:

I tried to execute my INSERT command and it gave an error in some value, in some column, which I don’t know what it is. Now is the time when you think about the work you will have to do to identify this, especially if you have about 100 columns instead of just those 3 in the example .. Having to insert the data in a “clone” table and keep consulting and comparing the largest size of each column… It seems laborious ..

To make this column identification work easier, I will share with you the script below, which makes this identification for you. Remember to change the source table and script name from INSERT / UPDATE to the cloned table with your original script.

DECLARE @Nome_Tabela VARCHAR(255) = ‘Teste’

— Crio uma tabela com a mesma estrutura

IF (OBJECT_ID(‘tempdb..#Tabela_Clonada’) IS NOT NULL) DROP TABLE #Tabela_Clonada

SELECT *

INTO

#Tabela_Clonada

FROM

dbo.Teste — Tabela original (LEMBRE-SE DE ALTERAR AQUI TAMBÉM)

WHERE

1=2 — Não quero copiar os dados

— Agora, vou alterar todas as colunas varchar/char/nvarchar/nchar para varchar(max)

DECLARE @Query VARCHAR(MAX) = »

SELECT

@Query += ‘ALTER TABLE #Tabela_Clonada ALTER COLUMN [‘ + B.[name] + ‘] ‘ + UPPER(C.[name]) + ‘(MAX); ‘

FROM

sys.tables A

JOIN sys.columns B ON B.[object_id] = A.[object_id]

JOIN sys.types C ON B.system_type_id = C.system_type_id

WHERE

A.[name] = @Nome_Tabela

AND B.system_type_id IN (167, 175, 231, 239) — varchar/char/nvarchar/nchar

AND B.max_length > 0

EXEC(@Query)

——————————————————————————————————————

— Faço a inserção dos dados que estão apresentando erro na tabela temporária (LEMBRE-SE DE ALTERAR AQUI TAMBÉM)

——————————————————————————————————————

INSERT INTO #Tabela_Clonada

— Alterar esse SELECT aqui pelo seu

SELECT [name], [type_desc] + ‘ | ‘ + lock_escalation_desc, SCHEMA_NAME([schema_id])

FROM sys.tables

— Crio uma tabela temporária com o tamanho máximo de caracteres de cada coluna

SET @Query = »

SELECT

@Query = @Query + ‘SELECT ‘ + QUOTENAME(A.[name], »») + ‘ AS coluna, ‘ + QUOTENAME(B.[name], »») + ‘ AS tipo, MAX(DATALENGTH(‘ + QUOTENAME(A.[name]) + ‘)) AS tamanho FROM #Tabela_Clonada UNION ‘

FROM

tempdb.sys.columns A

JOIN sys.types B on B.system_type_id = A.system_type_id and B.[name] <> ‘sysname’

WHERE

A.[object_id] = OBJECT_ID(‘tempdb..#Tabela_Clonada’)

AND B.system_type_id IN (167, 175, 231, 239) — varchar/char/nvarchar/nchar

SET @Query = LEFT(@Query, LEN(@Query)—6)

IF (OBJECT_ID(‘tempdb..#Tamanho_Maximo_Coluna’) IS NOT NULL) DROP TABLE #Tamanho_Maximo_Coluna

CREATE TABLE #Tamanho_Maximo_Coluna (

coluna VARCHAR(255),

tipo VARCHAR(255),

tamanho INT

)

INSERT INTO #Tamanho_Maximo_Coluna

EXEC(@Query)

— E por fim, faço a comparação dos dados de uma tabela pela outra

SELECT

B.[name] AS coluna,

C.[name] AS tipo,

B.max_length AS tamanho_atual,

D.tamanho AS tamanho_necessario

FROM

sys.tables A

JOIN sys.columns B ON B.[object_id] = A.[object_id]

JOIN sys.types C ON B.system_type_id = C.system_type_id

JOIN #Tamanho_Maximo_Coluna D ON B.[name] = D.coluna

WHERE

A.[name] = @Nome_Tabela

AND B.system_type_id IN (167, 175, 231, 239) — varchar/char/nvarchar/nchar

AND B.max_length < D.tamanho

Running this script will give you the exact return of which columns are too small for the data they are receiving and what is the optimal size to be able to store this data.

Observation: Be careful when using this script on very large tables. Because it needs to get the largest size of each column in the table, this operation can be a little slow and cumbersome on tables with millions of records. So have very careful!

I hope you enjoyed this article, a big hug and until the next post!
Hugs

References:

SQL Server 2019 — News and Features List
SQL Server — Why Not Use SET ANSI_WARNINGS OFF
How was SQL Server 2019 What’s New Live on the dotNET Channel?
https://www.sqlshack.com/sql-truncate-enhancement-silent-data-truncation-in-sql-server-2019/
https://imasters.com.br/banco-de-dados/adeus-string-binary-data-truncated
https://www.brentozar.com/archive/2019/03/how-to-fix-the-error-string-or-binary-data-would-be-truncated/

Источник

~~No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.~~

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again in the new feedback system, and now it has been delivered in the following versions:

SQL Server 2019
SQL Server 2017 CU12
SQL Server 2016 SP2 CU6

In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS.

Here is an example:

USE tempdb;
GO
CREATE TABLE dbo.x(a char(1));

INSERT dbo.x(a) VALUES('foo');
GO

Result in all supported versions prior to SQL Server 2019:

Msg 8152, Level 16, State 30, Line 5

String or binary data would be truncated.

The statement has been terminated.

Now, on SQL Server 2019 CTPs, with the trace flag enabled:

DBCC TRACEON(460);
GO

INSERT dbo.x(a) VALUES('foo');
GO
DROP TABLE dbo.x;
DBCC TRACEOFF(460);

Result shows the table, the column, and the (truncated, not full) value:

Msg 2628, Level 16, State 1, Line 11

String or binary data would be truncated in table ‘tempdb.dbo.x’, column ‘a’. Truncated value: ‘f’.

The statement has been terminated.

Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your «automagic» code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG’s equivalent is. Or, since that just means you’ll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you’re already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn’t have to do any brute-force guessing at all.

Источник

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

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

Вопрос

Ответы

If you’re on SQL Server 2019+, here’s how to fix it.

If you’re on SQL Server 2016-2017: to fix it, turn on trace flag 460.

Don’t leave this trace flag enabled.

String or binary data would be truncated SQL Server Which Column

How to fix “String or binary data would be truncated”

String or binary data would be truncated SQL Server 2019

String or binary data would be truncated Datetime SQL Server

String or binary data would be truncated in SQL Server Stored Procedure

SQL-Ex blog

Как исправить ошибку «Символьные или двоичные данные могут быть усечены»

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

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

Обратные ссылки

Комментарии

Sql server 8152 error

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Заметки системного администратора Windows Server

Поиск по этому блогу

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

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

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

Решение:

Sql server 8152 error

Вопрос

Ответы

Все ответы

How to Bypass String Truncation

String or binary data would be truncated in SQL Server 2019

What has changed from SQL Server 2016 +

1 Form: Using Initialization Parameter -T460

2 Form: Using 460 Traceflag

Identifying String Truncation Before SQL Server 2016

If you’re on SQL Server 2019+,
here’s how to fix it.

If you’re on SQL Server 2016-2017:
to fix it, turn on trace flag 460.