How do I identify the column(s) responsible for “String or binary data would be truncated.”
I am generating some queries automagically with code I wrote to SELECT from a remote Pg database, and insert into a local SQL Server database. However, one of them is generating this error: [Microsoft][ODBC SQL Server Driver][SQL Server]String or binary data would be truncated. (SQL-22001) [state was 22001 now 01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statementhas been terminated. (SQL-01000) at .insert.pl line 106. How do I find out what column is generating that error and lacks the length for the input? Is there a way to do this without brute force-guessing all the varchar?How do I fix string or binary data would be truncated in SQL server?
To fix this error, patch to SQL Server 2016 SP2, CU6 or newer (including SQL Server 2017), and then turn on trace flag 460. You can enable it at the query level or at the server level. This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:
binary or string data would be truncated => error message enhancments And now it has been delivered in the following versions:
- SQL Server 2019
- SQL Server 2017 CU12
- SQL Server 2016 SP2 CU6
In the very first public CTP of SQL Server 2019, it only surfaces under trace flag 460. This sounds kind of secret, but it was published in this Microsoft whitepaper. This will be the default behavior (no trace flag required) going forward, though you will be able to control this via a new database scoped configuration VERBOSE_TRUNCATION_WARNINGS. Here is an example: USE tempdb; GO CREATE TABLE dbo.x(a char(1)); INSERT dbo.x(a) VALUES('foo'); GO Result in all supported versions prior to SQL Server 2019:Msg 8152, Level 16, State 30, Line 5 String or binary data would be truncated. The statement has been terminated. Now, on SQL Server 2019 CTPs, with the trace flag enabled: DBCC TRACEON(460); GO INSERT dbo.x(a) VALUES('foo'); GO DROP TABLE dbo.x; DBCC TRACEOFF(460);Result shows the table, the column, and the (truncated, not full) value:
Msg 2628, Level 16, State 1, Line 11
String or binary data would be truncated in table 'tempdb.dbo.x', column 'a'. Truncated value: 'f'.
The statement has been terminated.
Until you can move to a supported version/CU, or move to Azure SQL Database, you can change your "automagic" code to actually pull the max_length from sys.columns, along with the name which you must be getting there anyway, and then applying LEFT(column, max_length) or whatever PG's equivalent is. Or, since that just means you'll silently lose data, go figure out what columns are mismatched and fix the destination columns so they fit all of the data from the source. Given metadata access to both systems, and the fact that you're already writing a query that must automagically match source -> destination columns (otherwise this error would hardly be your biggest problem), you shouldn't have to do any brute-force guessing at all.