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 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?
Ultimately, I could not find a way to get the column information without writing it myself.
This error message- "String or binary data would be truncated." 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 "$@
"; 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; }