question

lukjak avatar image
lukjak asked

"String would be truncated". Which one?

Hi! I'm using script component in SSIS to generate insert statements. I'm selecting list of columns from database, then create string covering these columns(e.g. '*INSERT INTO schema.table(column1, column2.....) VALUES (...)*'). Script manages to put specific values in specific places in brackets. These queries are executed in *try-catch* block and if exception is thrown I save the error message with additional info in my table. Now the problem is this isnt small database. There are tables that have dozens of columns. There are insert statements that have dozens of *VALUES* brackets after insert statement. Checking manually which string did not meet to column requirements is nightmare. And the whole error message I get is "String or binary data would be truncated. The statement has been terminated.". Is there any way for SQL Server to tell me exactly WHICH STRING from the bracket would be truncated? After exception I have table name, error message and exact full INSERT statement with values.
insert
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
There are no quick fixes for this. It's been a very long-standing "feature", for which there is a [Connect ticket - go and vote it up][1]. [1]: https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
ha-ha-ha: "feature" :)
1 Like 1 ·
lukjak avatar image
lukjak answered
Well, my workaround is that I create tables with varchar columns set to max length, than populate with problematic data and use max(len(...)). But its very annoying.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.