“String or binary data would be truncated in SQL server,” Are you encountering this issue when attempting to add more data to a column? We have the answer for you. We at Bobcares can take care of your SQL server troubles with our Server Management Service.

What is “string or binary data would be truncated in sql server” error?

The error “string or binary data would be truncated in sql server” appears usually when we try to add more data into a column than its specified limit. For example,

CREATE TABLE Example (
 id INT,
 Name VARCHAR(10),
 Sex VARCHAR(8)
);

insert into Example (id, Name, Sex) values (1, 'Sachin Tendulkar', 'Male');

In the above command, the maximum capacity of the Name column is to store string upto 10 characters. However, we have inserted a Name of 15 characters. Hence the query will return the error, “string or binary data would be truncated in sql server”.

How to locate the column causing the error?

We must enable the trace flag 460 for SQL Server 2016/17. Once enabled, the column name can be quickly determined from the SQL Server’s precise truncation notice. To enable the trace flag 460, we can run the following query when adding or updating table records.

insert into Example (id, Name, Sex) values (1, 'Sachin Tendulkar', 'Male')
OPTION (QUERYTRACEON 460);

If we are using SQL Server 2019 upgraded from another previous SQL Server version, then we can run the following query in the db where we want a complete truncate warning.

ALTER DATABASE SCOPED CONFIGURATION 

SET VERBOSE_TRUNCATION_WARNINGS = ON;

For SQL Server 2019, the error message itself contains the column name and also the table for which the error has been occured. By checking the error message carefully, we can locate the details.

How to fix the error?

1. The first option is to confirm that the string value we are trying to insert into the table can fit to the column we’ve already specified.
2. If we need to insert the data to the table, then the only option is to increase the length of the column using the ALTER TABLE command. For example( with respect to the previous example)

   ALTER TABLE Example
   ALTER COLUMN Name VARCHAR(20)
   GO

   insert into Example (id, Name, Sex) values (1, 'Sachin Tendulkar', 'Male')
GO
3. Next, check the length of the data programmatically before performing the insert or update operations.
4. Then, implement error handling in the application to manage cases where data truncation might occur.

An Example of Avoiding the Error Programmatically

DECLARE @FirstName NVARCHAR(50) = 'A very long first name that exceeds fifty characters';

IF LEN(@FirstName) > 50
BEGIN
— Handle the error or truncate the string
SET @FirstName = LEFT(@FirstName, 50);
END

INSERT INTO Employees (EmployeeID, FirstName, LastName)
VALUES (1, @FirstName, ‘Doe’);

[Need assistance further? We are available 24/7.]

Conclusion

The error appear when we try to add more data to a column than its specified limit. In this article, our Support team has provided some methods to locate it and to resolve it.