String or binary data would be truncated.

This error message really irritates me.

Msg 8152, Level 16, State 2, Line 1
String or binary data would be truncated.
The statement has been terminated.

I should probably open a Microsoft Connect item about this but would it really be that hard to tell you the column name? When you’re importing a data from temporary tables with a large amount of columns it can be rather tedious to check which one is causing the problem. Here’s an approach I often take to identify the column quickly.

USE AdventureWorks
GO
 
-- Generate SQL for all the character columns
SELECT 'MAX(LEN(' + COLUMN_NAME + ')) AS ' + COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Contact'
AND TABLE_SCHEMA = 'Person'
AND (DATA_TYPE LIKE '%char%'
OR DATA_TYPE LIKE '%text%');
 
-- Run the generated SQL against your table
SELECT MAX(LEN(Title)) AS Title,
MAX(LEN(FirstName)) AS FirstName,
MAX(LEN(MiddleName)) AS MiddleName,
MAX(LEN(LastName)) AS LastName,
MAX(LEN(Suffix)) AS Suffix,
MAX(LEN(EmailAddress)) AS EmailAddress,
MAX(LEN(Phone)) AS Phone,
MAX(LEN(PasswordHash)) AS PasswordHash,
MAX(LEN(PasswordSalt)) AS PasswordSalt
FROM Person.Contact;

When ran against your target table the script will produce a MAX character count for all the char / text type fields. Not exactly rocket science but this makes it easy to identify the column(s) causing the issue.

max data lengths


One Comment

  1. mbourgon says:

    I actually just opened a connect case for this truncation error. While I have a way around it (select into temp tables with the ANSI WARNINGS on and off, then EXCEPT the two tables), it’s still aggravating. Feel free to vote on it.

    https://connect.microsoft.com/SQLServer/feedback/details/585266/overflow-return-which-field-is-having-the-issue

Leave a Reply