SQL Server String Binary Data Would be Truncated Catch

Question

When updating a series of tables with many VarChar(n) columns, some updates where I perform a Replace operation kill my transaction with "string or binary data would be truncated." I am trying to come up with a repeatable way to handle this.

One option is to first select the row ID and length of the would-be-transformed column if its length exceeds the size of the column. I am not a fan of this because it seems like the I will get no query plan re-use and it will perform slowly.

Another option would be to figure out how to catch the rows (first row?) causing the fail and report that to the caller. I don't think this will work because the error level is too high to let me pinpoint the problem.

I could also ignore the whole thing by taking a Substring/Left of the new value and putting that in, but that will mask it and not let me know where actual problems are.

I'm using MSSQL 2008.

Edit - an idea here looks kind of promising: http://sqlblogcasts.com/blogs/danny/archive/2008/01/12/scuffling-with-string-or-binary-data-would-be-truncated.aspx:

    1. Take a copy of the destination table:
    SELECT * INTO #Destination FROM DestinationTable WHERE 1=2
    GO
    2. Set ANSI_WARNINGS OFF and perform the insert into the copy of the destination table, then set ANSI_WARNINGS ON again:
    SET ANSI_WARNINGS OFF
    GO
    INSERT INTO #Destination
    SELECT * FROM SourceTable
    GO
    SET ANSI_WARNINGS ON
    GO
    As ANSI_WARNINGS is off SQL Server truncates the fields rather than produces the warning.
    3. Next compare what you would like to insert against what was inserted with the ANSI_WARNINGS OFF truncating. By using EXCEPT you only select the rows that don't match, and have therefore been truncated:
    SELECT * FROM SourceTable
    EXCEPT
    SELECT * FROM #Destination
    GO

Answer 1

"Another option would be to figure out how to catch the rows (first row?) causing the fail and report that to the caller. I don't think this will work because the error level is too high to let me pinpoint the problem."

I assumed you needed to pinpoint the problem (i.e. which column it's occuring in). If that's not the case then you could just do the following:

You could try to update with ansi warnings on and if it causes an error then report the exception, turn ansi warnings off and import it into the table, and then turn ansi warnings back on.

But if you do need to pinpoint the problem then it's going to take more code to locate the problem... but for speed sake you may not want to be looking for errors proactively so you could use the above technique and only look for the problem once sql tells you there is one.

Answer 2

What source are you updating from? I.e. are you updating from another table as a set based operation, or maybe from a loop in an application where you are updating rows one at a time?

Is this just a problem for a particular column, or something you run into for various columns?

The more targeted the problem is the easier it will be to fix. Answer that and then I'll update my answer with more info. Basically, though you want to query over your source data for just records that will not cause a problem and process them. Then report the rest as exceptions and/or truncate them so they can be imported if that's what you want.

Edit: I don't know what kind of environment you're trying to solve this in but basically you need to compare the length of the data you're importing to the length of the field it's being imported into. If you're doing this in pure sql then it's very possible to accomplish this but it's gonna be a huge pain in the butt. If you're in a .net or similar language then it shouldn't be that much trouble.

Your checks are done before your update, not as part of the update. So reuse of the query plan isn't an issue. If speed is an issue then you may want to come up with a strategy to only do updates after you've got a certain number of updates to commit. That way you can do set based operations on say 100 updates at a time instead of 1 at a time. That assumes you don't need realtime updates though.