String or Binary Data would be truncated in SQL Server | How to fix it?

“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?

While you are working with databases, there is a possibility of encountering warnings about the truncated column values. These warnings do not prevent data from being saved or returned, but they indicate that the value recovered was larger than the allocated space in the host variable. “string or binary data would be truncated in sql server” is not technically an error but a warning and does not block data operations. This typically occurs when your database server, API, or query tool does not report truncation information according to the SQL standard.

For example, Informix reports column value truncation at the time of fetching, similar to how it handles NULL values, by using indicator variables. Each fetched column can optionally return a two-byte numeric indicator.

If the indicator is negative it indicates that the fetched value is NULL and the contents of the host variable should be ignored.

If the indicator is positive, the host variable contains a truncated value, and the indicator shows the actual length of the data that was truncated.
By comparing the indicator’s value to the length of the host variable, you can determine which column was truncated and how much of the data was not returned.

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.

Don’t let SQL truncation slow you down

Talk to us

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

Solution 2: Data Not Written Due to Precision Issues

The records were not written to the database because of a precision mismatch in the data.
From the ODBC logs, it can be confirmed that the query was issued to the database. Anyhow, a mismatch in precision for any one column caused the entire record to be refused. This can be resolved by checking the precision of each column in the table followed by ensuring the incoming data matches it.

Screenshot Reference:
From the attached screenshot, it is found that the value “Mathematics” exceeds the precision defined in the table DDL. Accordingly the entire record is skipped, and the data does not flow into the target through the mapping.

Additional Information:
When the length of the data is within the precision defined in the table DDL, the record flows into the target without errors.

Screenshot Reference of Successful Insertion:
In the successful example, all values are within the table’s defined precision. Consequently, the data is inserted into the target without any errors.

An Example of Avoiding the Error Programmaticall

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.