Ms sql ошибка 8152

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%’

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

How to Force an Operation that Results in Truncation to Execute Anyway

• Do you know what truncation is?
• Do you know what is going to be truncated?
• Is it your intention to truncate long data to fit?

If and and only if you answer YES to the above questions, you can force your inserts to ignore the warning and run anyway. If you answered no to any of the above, read on.

SET ANSI_WARNINGS  OFF;
-- Your operation TSQL here.
SET ANSI_WARNINGS ON;

(source)

What is Truncation in this Context

Truncation is simply putting as much of a value as will fit into the column and then discarding any portion that doesn’t fit. For example, truncating The quick brown fox jumps over the lazy dog to 13 characters would be The quick bro

Why would I receive this error message

You receive this error when you attempt to insert data that won’t fit into a destination column definition because the data is too short.

I’m trying to insert many rows of data and want to determine which rows don’t fit

Aaron’s excellent answer notes that if you are running SQL Server 2019 or newer, you’ll actually get a message that contains the column and value that won’t fit — which is awesome! But if you aren’t running a version that new, read on for tips.

If you receive this error message while attempting to bulk insert many rows of data, you might try splitting the insert into multiple inserts and running them separately to narrow down where the long value is.

Alternatively, you could insert the data into a new temp table and search said temp table for values that won’t fit into your destination table.

--(insert into new temp table #Vendors)
INSERT INTO #Vendors(VendorID, VendorName, VendorAddress, 
  VendorCityName, VendorStateName, VendorZip, VendorContactName, 
  VendorContactPhone, VendorContactEmail, VendorSpecialty)
VALUES(151330, 'Hyperion', '77 West 66th Street', 'New York', 
  'NY', 10023, 'John Hinks', '212-337-6564', 
  'jhinks@hyperionbooks.com', 'Popular fiction')

Then query for rows that don’t fit.

--(query for values that don't fit)
SELECT *,
LEN(VendorContactEmail) AS Length
FROM #Vendors
WHERE LEN(VendorContactEmail) > 20 --set your destination column length is here

See also LEN and DATALENGTH documentation for information on whitespace handling and binary data lengths.

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

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

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

Ошибка 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.