Ошибка error while update execution data truncation

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.

In this article, we’ll take a look into SQL truncate improvement in SQL Server 2019.

Data inserts and updates are a normal and regular task for the developers and database administrators as well as from the application. The source of the data can be in multiple forms as if direct insert using T-SQL, stored procedures, functions, data import from flat files, SSIS packages etc.

Sometimes we receive the bad data in terms of character limits more than the defined limit in a column. For example, if we want to insert bulk data into a database table using an insert statement, we get a bad data on character length 11 into employee name column while our existing column (Employee_name) allows only 10 characters, so how SQL Server will behave? it will raise an SQL truncate error. Insert statement will fail in this case. We normally call it as silent truncation and occur when we try to insert string data (varchar, nvarchar, char, nchar) into more than the size of the column.

If we are dealing with the huge amount of data with lots of columns, if we get any error it becomes difficult to find out which column, data caused the issue. However, it is important for us to dig out into the data and identify the bad data, fix it in order to import the data. We can use a profiler or extended events to troubleshoot the data insert, update, but again it is a resource and time-consuming. We can also use the custom stored procedure to check the length of the data before inserting into the table, but it is an extra overhead for us. Moreover, if we are getting frequent SQL truncate errors, it might be difficult to troubleshoot using a profiler and extended events in that case. This is the issue developers and DBA used to highlight in a different forum to improve the error related with silent truncation of data so that it can be quickly fixed.

Let us first create a sample database, table and insert some dummy data into it.

Example 1

As we can see above, we get the SQL truncate error message ‘String or binary data would be truncated.’

Therefore, before we move further, let me explain why this error occurred. In below query, we are checking the length of the string that we want to insert into our workload.

While using below query, we can check that the Name column in our DemoSQL2019 table allows only 10 characters.

As we can see above, SQL truncate error occurred due to the length of the data more than the length of the string allowed in the column.

Example 2

Let us look at the complex data:

We can notice here that the table involves many columns and we are trying to insert multiple records in our example. Now, we have the same SQL truncate error message, but we did not get any clue about which row is causing the issue. Due to a large number of insert statements, it would be difficult to fix this issue. This is the behavior until SQL Server 2017. Now in below section let us see how SQL Server 2019 solves this issue.

SQL Server 2019 behavior for data truncation

Pre-requisite

Before we explore the SQL Server 2019 solution to the silent data truncation issue, we should have below pre-requisites:

• Installation of SQL Server 2019 Public preview version.
• SQL Server Management Studio 18.0 Preview 4

In my previous article, we learned that SQL Server 2019 preview version (SQL Server vNext CTP 2.0) launched recently and you can install it on windows version by following up the SQL Server 2019 overview and installation.

Now let us look at the SQL Server 2019 behavior of this SQL truncate issue. We can see in the database properties, the database compatibility level is set to 150, which is the new compatibility level for SQL Server 2019.

If database compatibility level is other than 150, we can change it using the below query.

Now let us run the query from the example 1 in this SQL Server 2019 Database with compatibility level 150.

We got a similar error ‘String or binary data would be truncated’. So, do we have the same kind of behavior in SQL Server 2019 as well?

No, SQL Server 2019 gives information that is more useful to fix the issue. We get the below message if the string or binary data is truncated.

String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.

This error message highlights clearly the table name, column name, and the truncated value in SQL Server 2019. Therefore, we do not worry or troubleshoot to find the problematic data or column giving this error message. This was a real concern for most of the DBA’s and developers and this feature was in demand from a long time.

We can look this message into sys. messages table in SQL Server 2019; message id 2628 contains this SQL truncate error message. We can see the table contains error messages for the same error code in the different language. SQL Server picks up the correct message_id based on the collation setting which is the language id in this table.

While in previous versions till SQL Server 2017, only ‘String or binary data would be truncated’ message is shown

In SQL Server 2019, we need to enable trace flag 460 using DBCC TraceOn. This trace flag displays the SQL truncate error message – “String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.”

Let us enable the trace flag 460 and run the query.

Now let us run the insert statement from our script in example 2 with DBCC TRACEON(460)

Solution to fix String or binary data truncation

Now in SQL Server 2019.we can easily find out the column and data, which caused the issue. In order to fix the issue, we can choose from any of the below solution appropriate to us.

• Fix the data that we are trying to insert or update. Data length should not exceed the maximum allowed limit for the particular column
• Use ‘SET ANSI_WARNINGS OFF’ to truncate the data and insert it as per column maximum string length

In the below example used ‘ SET ANSI_WARNINGS off’. Therefore, SQL Server will truncate the data as needed to make it fit into the column. We will not get any SQL truncate error as well since data is truncated and inserted into the table.

We can see the SQL Server inserts data as fit into the column data size. For example, ‘SQLShack ApexSQL Community’ inserted to the table after truncation as ‘SQLShack A’

• Modify the column using the alter table statement

We can also modify the column property to allow more string length data. We can use the below query to alter table.

In the below example, we modified the column [CustomerName] to varchar (50) from varchar(30) and executed the example 2 query. This time it executes successfully.

Conclusion

Silent data truncation in SQL Server 2019 is a really nice enhancement. This will make many database administrators and developers happy with this detailed SQL truncate error message. Currently, we have to enable the trace flag in SQL Server 2019. I believe this feature will be available without trace flag as well in an upcoming release.

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.

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

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:

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:

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:

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:

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:

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!

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

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:

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:

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:

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.

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/

You might have get the data truncation error in SQL Server such as “String or binary data would be truncated” . It does not specify the exact source of error in terms of database name, table name, column name and truncated values.

It usually happens when a data value somewhere does not fit under a table’s column data type size so it returns only “String or binary data would be truncated”  error rather than giving a detailed error .

Sometimes it becomes worse when you are performing bulk insertions and data having have too many columns so in this situation it becomes hard to find out which column and data causing the issue.

In SQL Server 2019, you can see detailed error of truncation like database name, table name and column name that causing issue.

Lets demonstrate a truncation error for SQL Server different different versions until SQL Server 2017 and SQL server 2019.

Check SQL Server Verbose Truncation Warnings until SQL Server 2017

Here we are using SQL Server 2017 and its compatibility level is 140.

To check database compatibility level > Right Click on database > then Click on Properties > click on Option

You can also check same using T-SQL query.

select name , Compatibility_Level, collation_name from sys.databases 
where name = 'SQLServerTutorials'

Now create a table named as TBLX, here for column ‘NAME’  data type size is given 5.

Lets try to insert value for ‘NAME’ column of size more than 5 to explore the truncation error .

CREATE TABLE TBLX
(
ID INT identity(1,1),
NAME VARCHAR(5),
)
GO

INSERT INTO TBLX VALUES ('SQL SERVER')

As you can see, It gives an error saying –‘String or binary data would be truncated.’

It does not provide exact error message so it makes hard to find out the root cause of error when performing bulk insertions or dealing with large set of queries, multiple columns so on..

Check SQL Server Verbose Truncation Warnings with SQL Server 2019

It requires SQL Server Compatibility level to 150.

Also Read..

 4,829 total views,  1 views today

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

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

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