29

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 statement has 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?

34

No, it is not logged anywhere. Go vote and state your business case; this is one on the long list of things that should be fixed in SQL Server.

This was requested years ago on Connect (probably first in the SQL Server 2000 or 2005 timeframe), then again on the new feedback system:

And now it has been delivered, in SQL Server 2019, SQL Server 2017 CU12, and will appear in a future SQL Server 2016 SP2 CU.

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 drop everything and upgrade to SQL Server 2019, 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.

2

If you have access to run SQL Server Import and Export Wizard from SQL Server Management Studio (right-click database > Tasks > Import Data...), create a task that imports from SQL Client using your query as the data source to the destination table.

Before you run the import, you can review the data mapping and it will tell you which columns have inconsistent field types. And if you run the import task it will tell you which column(s) failed to import.

Sample Validation Warning:

Warning 0x802092a7: Data Flow Task 1: Truncation may occur due to inserting data from data flow column "NARRATIVE" with a length of 316 to database column "NARRATIVE" with a length of 60. (SQL Server Import and Export Wizard)

1

Ultimately, I could not find a way to get the column information without writing it myself.

This error message was generated by DBD::ODBC, you can also however use sys.columns (max_length) (I just don't know how).

I used code like this over my column list to get a list of arrays with two elements, the COLUMN_NAME, and MAX_LENGTH (documented in DBI column_info()).

my @max_lengths = map [ @{$_->fetchall_arrayref->[0]}[3,6] ]
    , map $dbh_mssql->column_info('database', 'dbo', $dest_table, $_)
    , @col_mssql
;

Then I caught the exceptions on INSERT and printed out something useful. In this example @$row is the data sent to sth->execute()

if ($@) {
        warn "$@\n";
        for ( my $idx=0; $idx <= $#{ $row }; $idx++ ) {
                Dumper {
                        maxlength => $max_lengths[$idx]->[1]
                        , name    => $max_lengths[$idx]->[0]
                        , length  => length( $row->[$idx] )
                        , content => $row->[$idx]
                };
        }
        die;
}

Also, please vote and upvote the other answer

  • 2
    I didn't put any code referencing sys.columns because I had absolutely no idea what code you are currently using to "automagically" generate your queries. There really isn't too much more complex I could guess about incorporating into your code than SELECT name, object_id, max_length FROM sys.columns;. Since you already have automagic code that must be doing this - or something very much like it - I didn't think an example was necessary. – Aaron Bertrand Dec 15 '13 at 20:26
  • I'm not sure how sys.columns works with two columns that have the same name. Also, I got the thing working using the library rather than sys, why would I make that as the chosen answer? Microsoft SQL doesn't have x, do y instead is a valid contribution, but if your y is inferior to my y, I'm going to do something different and mark it as chosen. – Evan Carroll Dec 16 '13 at 8:00
  • 1
    Your question was, essentially, how do I find out what column was generating the error (presumably, so you could fix that one spot, instead of re-engineering the solution). I told you where to look: sys.columns. Which is exactly where you should look to compare your source column lengths with the destination column lengths. How you do that is up to you. I didn't tell you how to fix your code, because I have absolutely no idea how your automagic query was being generated in the first place so, like I said, had no idea how to add the length determinations to whatever query you already had. – Aaron Bertrand Dec 16 '13 at 12:42
1

Finally Microsoft has decided to provide meaningful information for String or binary would be truncated starting from SQL Server 2016 SP2 CU, SQL Server 2017 CU12 and in SQL Server 2019.

The information now includes both offending table column (fully qualified name) and the offending value (truncated at 120 characters):

Msg 2628, Level 16, State 1, Line x String or binary data would be truncated in table 'TheDb.TheSchema.TheTable', column 'TheColumn'. Truncated value: '...'. The statement has been terminated.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.