Error string or binary data would be truncated the statement has been terminated

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

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

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

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

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.

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.

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.

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.

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.

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.

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

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.

SQL-Ex blog

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Комментарии

Показывать комментарии Как список | Древовидной структурой

Автор не разрешил комментировать эту запись

Источник

String or binary data would be truncated in SQL Server

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

Table of Contents

String or binary data would be truncated SQL Server Error

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.

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

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.

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.

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.

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.

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

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.

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.

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

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.

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.

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:

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

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.

Источник

String or binary data would be truncated the statement has been terminated error

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

Answered by:

Question

I have the following problem.

When I edit a field in the SQL Server Management Studio Express then I get the following error:

Error Source: .Net SqlClient Data Provider
Error Message: String or binary data would be truncated.

The statement has been terminated

When I use the SQL update commando, it works fine.

Fore debugging we need to change the enabled field several times.

Is there a solution fore this?

The Table Layout

/****** Object: Table [dbo].[Proces] Script Date: 06/02/2008 10:01:20 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo] . [Proces] (

[prio] [bigint] NOT NULL,

[depends] [int] NOT NULL,

[type] [int] NOT NULL,

[enabled] [smallint] NOT NULL,

[naam] [nvarchar] ( 81 ) NOT NULL,

[param1] [text] NOT NULL,

CONSTRAINT [PK_Proces] PRIMARY KEY CLUSTERED

) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Answers

ntext can contain any length of data,

You will get error » String or binary data would be truncated. The statement has been terminated.» when existing column contains data having more length than new column length.

If u want your data to be preserved and to change ntext column to nvarchar then use,

ALTER TABLE dbo . Table_Name
ALTER COLUMN Col_Name nvarchar ( MAX ) null

Ntext is deprecated in SQL 2005, If your table still contains data more than 8000 which is MAX, then that column data will be treated as Ntext

Are you copy pasting the value to the column? the error says you are updating the column with larger size than what it can accomodate. Check for blank space in the column value

Ehh no, the text column (param1) does not change. The column holds a big text file, witch is working ok!

By chancing the enabled column value from 0 to 1 (or vice versa) will an other program do something with the text. The functionality is ok, and works fine.

But by chancing the ‘enabled’ column from 0 to 1 I get the error.

Can you please tell me how to get ahold of the software for «SQL update commando». I have 30 entries I can not delete from a SQL 2005 table. Your help would be much appreciated!

Hope everyone is having a great day!

I am also getting the same error while trying to update a row by SQL server management studio 2005.

I have one table with ntext column with other columns.
For some records in this table, I am geting «String or binary data would be truncated» error message when trying to modify any column/cell value in SQL server management studio 2005(Right click on table and then open table, opens table data in SQL management studio, i am trying to update/delete there).

I am getting this error even when I am trying to change a single character in any column in that row, even when i try to input minimal data in particular cell then currently it has.

The same error I am getting while trying to delete that row in SQL server management studio 2005.

I marked this happens only when the ntext column in that row contains large data in it.

I can delete/modify values in the rows in same table for which ntext column has smaller data.

It was strange because there was existing data in these records under the
‘ntext’ column already. In trying to update it to a single character I
noticed I received the error messages for records that had lengthy data in
it (like paragraphs long) versus the records that I was able to update that
only had a few characters which I didn’t receive the error message and was
able to update it to a single character.

Since we’re still in the
development stage I ran an UPDDATE statment to set all of this ‘ntext’ column
to null and it seems like that this did the trick.

EDIT/DELETE is only possible by firing the query in ‘query window’.

CAN ANYBODY TELL ME IS IT THE BUG IN SQL SERVER MANAGEMENT STUDIO 2005? IS THERE ANY FIX FOR IT?

Источник

Problem

Part of my job is migrating data to SQL Server from any classic database using insert scripts. During such data migrations, I have frequently encountered the error message “String or binary data would be truncated…” and the insert statement terminates. In most cases this can be avoided by taking several precautions like trimming, translate or replacing unwanted characters from the string. However, in some cases we may not need the extra length of the string and can be trimmed and inserted. In such situations, there is an option in SQL Server to avoid the error and insert the data by automatically trimming it. Let us see how to use the option with an example.

To start with let us see what will happen when we try to insert a longer string in a column of smaller length. In this sample table MTB_Table_A, the column Name is of 10 nvarchar in length. To this column, I am trying to insert a string of length 15.

CREATE TABLE [MTB_Table_A](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](10) NULL
) ON [PRIMARY]
GO

INSERT INTO MTB_Table_A VALUES ('James D Madison')
GO

/* Result */
Msg 2628, Level 16, State 1, Line 1
String or binary data would be truncated in table 'Test.MyTecBits.com.dbo.MTB_Table_A', column 'Name'. Truncated value: 'James D Ma'.
The statement has been terminated.

Solution

To avoid this error and to insert the string with truncation, use the ANSI_WARNINGS option. On setting ANSI_WARNINGS to OFF, the error message will not be displayed and the data will be automatically truncated to the length of the destination column and inserted. Here is the previous insert statement with SET ANSI_WARNINGS OFF.

SET ANSI_WARNINGS OFF
INSERT INTO MTB_Table_A VALUES ('James D Madison');
SET ANSI_WARNINGS ON
GO

This will be helpful when you are sure you are migrating less important data. However, it is not a good idea to use important data like phone numbers, SSN numbers, etc…

Warning

• After inserting the data do not forget to switch ON the ANSI_WARNINGS setting. Keeping the ANSI_WARNINGS OFF may lead to truncation of important string values without your knowledge while running further migration scripts.
• SET ANSI_WARNINGS OFF will stop issuing warning on errors in math operations, arithmetic over flow errors, divide by zero errors, null value in aggregate functions, etc… So use it with care.

Reference

• Read more about SET ANSI_WARNINGS at Microsoft Docs.

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

There are many times where a query may result to the following error: String or binary data would be truncated. The statement has been terminated.

We will go through a simple example for explaining this behavior.

Reproducing the “String or binary data would be truncated” error

Creating the Sample Table

Consider the following table:

CREATE TABLE [dbo].[Products](
[id] [int] NOT NULL,
[code] [varchar](10) NULL,
[description] [varchar](100) NULL,
CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
[id] ASC
)) ON [PRIMARY];
GO

In our scenario, we will use the Products table which as the name implies, it stores information about products.

The table has three columns:

• id of type int which is also the primary key
• code of type varchar with size 10
• description of type varchar with size 100

Populate the Table with Sample Data

Let’s try to insert some records:

insert into Products(id,code,description)
values (1,'00000-1234','Product A');
GO

We can see that the above insert statement was executed successfully:

select * from Products;
GO

Result:

id | code | description
—————————
1 |00000-1234 | Product A

Getting the Error Message

Now let’s try to insert another record:

insert into Products(id,code,description)
values (2,'00000-34567','Product B');
GO

In this case, the above insert statement returns an error:

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

Why we got the String Truncation Error Message

The above error occurs because the size of the code value (11) in the insert statement exceeds the allowed size (10).

If we take a look at the table’s definition, we can see that the size of the code column is 10.
Though the size of the value (00000-34567) in the insert statement is 11 characters. In such cases SQL Server returns the abovementioned error.

If we also try to insert multiple records (i.e. by using row constructors) and there is even one record which contains a value that exceeds the allowed size defined for the specific field in the table, then the whole statement fails:

insert into Products(id,code,description)
values (1,'00000-1234','Product A'),(2,'00000-34567','Product B'),(3,'00000-3456','Product C');
GO

Error Message:

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

Now let’s try something else:

insert into Products(id,code,description)
values (2,cast('00000-34567' as varchar(10)),'Product B');
GO

Within the above statement the 11-characters code value was converted to varchar(10) and the insert statement was executed successfully.

Though if we see the records in the Products table we can see that the last character (7) was not included.

select * from Products;
GO

Result:

id | code | description
—————————
1 | 00000-1234 | Product A
2 | 00000-3456 | Product B

As we can see, there are two approaches handling this issue: either filter your data correctly (with respect to the size of characters for the columns in the table’s definition) before trying to insert them into the table, or use a SQL Server function (i.e. cast, convert, substring, etc.) in order to automatically remove the redundant characters. The choice is yours!

Of course, you can always use larger column sizes I guess! 🙂

Strengthen you SQL Server Development Skills – Enroll to our Online Course!

Check our online course titled “Essential SQL Server Development Tips for SQL Developers”
(special limited-time discount included in link).

Via the course, you will sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!

Enroll from $14.99

Featured Online Courses

• Introduction to Azure SQL Database for Beginners
• SQL Server 2019: What’s New – New and Enhanced Features
• SQL Server Fundamentals – SQL Database for Beginners
• Essential SQL Server Administration Tips
• Boost SQL Server Database Performance with In-Memory OLTP 
• Essential SQL Server Development Tips for SQL Developers
• Working with Python on Windows and SQL Server Databases
• Introduction to Computer Programming for Beginners
• .NET Programming for Beginners – Windows Forms with C#
• Introduction to SQL Server Machine Learning Services
• Entity Framework: Getting Started – Complete Beginners Guide
• How to Import and Export Data in SQL Server Databases
• Learn How to Install and Start Using SQL Server in 30 Mins
• A Guide on How to Start and Monetize a Successful Blog

Artemakis Artemiou is a Senior SQL Server Architect, Author, a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 20 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and {essentialDevTips.com}. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA). Moreover, Artemakis teaches on Udemy, you can check his courses here.

Views: 3,885

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit «Cookie Settings» to provide a controlled consent. Read More