SQL Server Error Msg 2628 – String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.

In this blog post, let’s learn about the error message “2628 – String or binary data would be truncated in table ‘%.*ls’, column ‘%.*ls’. Truncated value: ‘%.*ls’.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.

SQL Server Error Message

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

Reason for the Error

SQL Server Error Msg 2628 is an error message that occurs when you try to insert or update a value in a column that is too long to fit in the column’s defined data type. This error message indicates that the data being inserted or updated is being truncated to fit within the defined column length.

Suppose we have a table called Customers with the following structure:

CREATE TABLE Customers (
   CustomerID INT PRIMARY KEY,
   FirstName VARCHAR(10),
   LastName VARCHAR(10)
);

Now, let’s try to insert a row into the Customers table with a value that is too long for the LastName column:

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (1, 'John', 'Smithersmith');

This will usually trigger the SQL Server Error Message 8152 as shown below

Msg 8152, Level 16, State 30, Line 2
String or binary data would be truncated.

Now, this error message might not be that useful as you don’t have more information on which field caused this truncation error.

You can enable SQL Server to show the error message 2628 to show more meaningful message by using the below code snippet

DBCC TRACEON(460)

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 2628, Level 16, State 1, Line 2
String or binary data would be truncated in table ‘AdventureWorks2019.dbo.Customers’, column ‘LastName’. Truncated value: ‘Smithersmi’.
The statement has been terminated.

If you want the new error message to be shown for this error, the SQL Compatibility should be set to SQL Server 2019 as shown below.

This error message indicates that the value being inserted, ‘Smithersmith’, is too long to fit into the LastName column, which is defined as VARCHAR(10). As a result, the value is truncated to ‘Smithersmi’.

Solution

To fix this error, we can either increase the length of the LastName column to accommodate longer values, or we can truncate the value being inserted to fit within the defined length. For example, we could change the LastName column to VARCHAR(15):

ALTER TABLE Customers ALTER COLUMN LastName VARCHAR(15);

Or we could truncate the last name to 10 characters:

INSERT INTO Customers (CustomerID, FirstName, LastName)
VALUES (1, 'John', 'Smithersmi');

In summary, SQL Server Error Msg 2628 occurs when you try to insert or update a value in a column that is too long to fit in the column’s defined data type. The error message provides information about the table and column where the error occurred, as well as the value being truncated. To fix the error, you can either increase the length of the column or truncate the value being inserted or updated.