Post reply

Batch alter stored procedure within one single transaction with error handling

  • June 16, 2011 at 8:58 am

    #1339803

    Dynamic SQL, or custom error handling with a temp table or similar (on error insert an error row into the table and then right at the end check whether it should be commit or rollback)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • June 16, 2011 at 10:45 am

    #1339905

    princa (6/16/2011)

    Lowell (6/16/2011)

    yes, opc.three's comment is the key; i testest it to make sure...for me i created a suite of tables and then 4 different functions and procs, all with GO statements in bewtween; when there was an error, the whole thing rolled back,a nd when ti was clean/error free, it committed the whole thing.

    after i thought about it, it made perfect sense, since that's really what any application does ...it starts a transaction, and then makes multiple command calls...sometimes with differnet connections.... ithat post helped me see the error of my older way of thinking.

    I tested again and I found out that even I set xact_abort ON, it doesn't roll back the whole transaction, it rolls back any statements before the error, but after all run....that's why have another error saying "no corresponding begin transaction for commit transaction", because it rolls back everything and corresponding to the begin transaction, and then it goes to the end of script then hit commit trasaction and there is not corresponding one...

    so set xact_abort ON will really roll back everything in a single transaction?

    Thanks for the posts Lowell :blush:

    The key to using XACT_ABORT is that you must run the script with SqlCmd and provide the -b switch. This solution is geared towards automating code deployments which is typically what you're doing when you have one script with DML and DDL all strung together in a sequence of batches.

    If you simply run the script in SSMS then Gail's explanation is spot-on as to why the script continues after an error occurs.

    Regarding the error message you posted:

    "Msg 208, Level 16, State 1, Procedure vwExistingActions, Line 3

    Invalid object name 'xxxxx'. ---This is the actual error I made it.

    Msg 3902, Level 16, State 1, Line 1

    The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION."

    Here is the sequence of events that lead to you seeing what you saw:

    > SET XACT_ABORT ON

    > BEGIN TRAN (tran count = 1)

    > Batch 1 may have completed OK

    > Batch 2 may have completed OK

    > ...

    > Batch n had an error - XACT_ABORT is on so the TRAN is automatically rolled back (trancount now = 0)

    > Note: SSMS is still executing commands in the query window whereas SqlCmd would have aborted the batch if you had provided the -b switch.

    > Batch n+1 may have completed OK

    > Batch n+2 may have completed OK

    > ...

    > Now you hit the COMMIT TRAN command and SQL Server throws the second error you saw because trancount = 0 due to the automatic rollback brought about by having XACT_ABORT turned on.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

Viewing 2 posts - 16 through 16 (of 16 total)

You must be logged in to reply to this topic. Login to reply