Error 8152: String or Binary Data Would Be Truncated – Find the Column

Fix it now

A value being written is wider than the column that has to hold it, and on the bare 8152 message SQL Server names neither. SQL Server 2019 and later raise a different error, 2628, which names the table, the column and the truncated value.

SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();

SELECT [value] FROM sys.database_scoped_configurations
WHERE [name] = 'VERBOSE_TRUNCATION_WARNINGS';

ALTER DATABASE SCOPED CONFIGURATION SET VERBOSE_TRUNCATION_WARNINGS = ON;

1. Re-run the insert. At compatibility level 150 or higher you now get error 2628 instead of 8152, and it names the table, the column and the value that would not fit.
2. At compatibility level 140 or lower the scoped configuration has no effect at all. Trace flag 460 is the only route to 2628 on those databases.
3. With the column named, compare the two sides: SELECT MAX(LEN(SomeText)), MAX(DATALENGTH(SomeText)) FROM dbo.Source; against max_length in sys.columns for the target.
4. Widen the target with ALTER TABLE dbo.Target ALTER COLUMN SomeText nvarchar(200) NULL;, or cut the source deliberately with LEFT(col, n).

max_length in sys.columns counts bytes. An nvarchar column stores two bytes per character, so a column that holds 100 characters reports 200.

If the column is named and widened you are done. If the error is 245, 241, 8115 or 232 instead, it is a type problem rather than a width one and the next section separates them.

Why it happens

8152 is checked value by value inside the storage engine, which knows only that a value did not fit. The published message is the whole message: String or binary data would be truncated. No table, no column, no value. On a forty-column insert that sentence is the same regardless of which column overflowed, which is why the error has the reputation it has.

SQL Server 2019 introduced a second message for the same condition. Error 2628 reads String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'. Which of the two you get is decided by the VERBOSE_TRUNCATION_WARNINGS database scoped configuration, and by the database compatibility level. At level 150 and higher the setting decides, and its default is ON, so a modern database on a modern build already tells you the answer. At level 140 and lower the setting does nothing whatever and 2628 remains opt-in behind trace flag 460. That split is the part people get wrong: turning the configuration on in a database still running at 130 changes nothing at all.

The rest of this family is not about width. 245 is Conversion failed when converting the %ls value to data type %ls, and it fires for any value that will not convert to the target type, not only strings to numbers. 241 is the date and time parser refusing a string. 8115 is an arithmetic overflow during a conversion, 232 an arithmetic overflow for a type, and 8134 is division by zero. They arrive together because a single load statement can trip any of them, and telling them apart is the first thing to do.

Full reference

Getting the message that names the column

The scoped configuration is per database, so a server can behave differently from one database to the next, and a database restored from an older instance keeps the compatibility level it arrived with. If you have inherited an estate where some databases name the column and others do not, that pair of settings is why.

Measuring the two sides properly

SELECT c.name AS column_name, c.max_length, c.precision, c.scale, t.name AS type_name
FROM sys.columns AS c
JOIN sys.types AS t ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.Target')
ORDER BY c.column_id;

SELECT MAX(LEN(SomeText))        AS max_chars,
       MAX(DATALENGTH(SomeText)) AS max_bytes
FROM dbo.Source;

• LEN returns characters and ignores trailing spaces. DATALENGTH returns bytes and counts everything.
• max_length in sys.columns is in bytes, and is -1 for the max types.
• A char(n) or varchar(n) column stores n bytes for single-byte encodings; an nvarchar(n) column stores two bytes per character.
• Comparing characters against bytes is the commonest reason someone concludes a column is wide enough when it is not.

The other five codes, and what each is actually complaining about

Do not switch SET ANSI_WARNINGS OFF to make 8152 stop. With that option off, the write can complete with the value silently shortened, so the load appears to succeed and the data is quietly damaged. A failed batch is a much better outcome than a table full of half-length values nobody notices for six months.

When the column is named and it still makes no sense

• Look for a trigger on the target. The overflow may be in the audit table the trigger writes to, not in the table you inserted into.
• Look for a computed column or a persisted expression whose result is wider than its declared type.
• Check sql_variant and dynamic staging patterns, where the effective type is decided at run time.
• In a MERGE, the failing statement can be the UPDATE branch rather than the INSERT you were watching.
• If the write goes through a view, compare the view’s column definitions with the base table’s rather than assuming they agree.

Every code this article covers

Confirm the fix worked

1. The failing statement now raises 2628 and names a table and a column, or it succeeds.
2. The maximum DATALENGTH in the source is at or below max_length for the matching target column.
3. The insert completes and the row count in the target matches the row count in the source exactly.
4. The longest values in the target read as complete rather than cut short.
5. SET ANSI_WARNINGS is still ON for the connection that runs the load.

Questions people ask about this