SQL Server error 8152: String or binary data truncated
A value is longer than the column it's going into, so SQL Server stops the INSERT or UPDATE instead of cutting it. Newer versions name the column (2628).
- Error number
8152- Severity
- 16
- Category
- Data conversion and truncation
What it means
SQL Server won't silently shorten data. If a string or binary value is longer than the target column, for example 60 characters into a varchar(50), the statement fails. Error 8152 doesn't say which column or row; that made it hard to debug, so SQL Server 2019 replaced it with error 2628, which names the table, column and the truncated value.
Common causes
- Data longer than the column's declared length, often from user input or an import.
- A column that is too narrow for real data, such as
nvarchar(50)for e-mail addresses or names. - An
INSERT ... SELECTwhose columns are in a different order than the target's column list, so a long column lands in a short one. - A variable or table variable declared too short in a procedure, which truncates silently on
SETbut fails onINSERT.
How to fix it
-
On SQL Server 2019 and later, get error 2628 with the column and value instead. It is on by default at database compatibility level 150 and above; you can also turn it on explicitly.
ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;
-
Compare the source lengths with the target columns to find the culprit.
SELECT MAX(LEN(Email)) AS max_email, MAX(LEN(Name)) AS max_name FROM staging.Customers; SELECT name, max_length, TYPE_NAME(user_type_id) AS type_name FROM sys.columns WHERE object_id = OBJECT_ID(N'dbo.Customers');
-
Widen the column if the data is legitimate.
ALTER TABLE dbo.Customers ALTER COLUMN Email nvarchar(320) NOT NULL;
-
Or shorten on purpose where truncation is acceptable.
INSERT INTO dbo.Notes (Summary) SELECT LEFT(Body, 200) FROM staging.Notes;
-
Always name the target columns in
INSERT ... SELECTso the order can't drift.
Reproduce it safely
CREATE TABLE #t (code varchar(3));
INSERT INTO #t VALUES ('ABCD'); -- 8152, or 2628 on SQL Server 2019+
DROP TABLE #t;
Questions
How do I find which column is being truncated?
On SQL Server 2019 or later at compatibility level 150, the error becomes 2628 and names the table, column and value. On older versions, compare MAX(LEN(...)) of each source column with the target column lengths.
Can I make SQL Server truncate silently instead?
SET ANSI_WARNINGS OFF makes inserts truncate without error, but it also changes other behavior and isn't allowed with indexed views or computed column indexes. Prefer an explicit LEFT() where truncation is intended.
Related errors
2628Truncated in table, column245Conversion failed converting a value22001PostgreSQL: value too long for type1406MySQL: Data too long for columnORA-12899Oracle: value too large for column
More data conversion and truncation errors
241Conversion failed for date and/or time242Out-of-range date conversion8115Arithmetic overflow error converting8114Error converting data type512Subquery returned more than 1 value8134Divide by zero error encountered220Arithmetic overflow error for data type248Conversion overflowed an int column
The code and message text are from the Microsoft Database Engine events and errors reference. Browse the full SQL Server error list or search every database error.
Work on SQL Server in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQL Server and the other databases you use.