Exit and rollback everything in script on error

Question

Hey guys !

I'm in a bit of a pickle here. I have a TSQL script that does a lot of database structure adjustments but it's not really safe to just let it go through when something fails.

to make things clear:

• using MS SQL 2005
• it's NOT a stored procedure, just a script file (.sql)

what I have is something in the following order

BEGIN TRANSACTION
    ALTER Stuff
    GO

    CREATE New Stuff
    GO

    DROP Old Stuff
    GO
IF @@ERROR != 0
    BEGIN
  PRINT 'Errors Found ... Rolling back'
  ROLLBACK TRANSACTION
  RETURN
    END
ELSE
     PRINT 'No Errors ... Committing changes'
     COMMIT TRANSACTION

just to illustrate what I'm working with ... can't go into specifics now, the problem ...

When I introduce an error (to test if things get rolled back), I get a statement that the ROLLBACK TRANSACTION could not find a corresponding BEGIN TRANSACTION. This leads me to believe that something when REALLY wrong and the transaction was already killed. what I also noticed is that the script didn't fully quit on error and thus DID try to execute every statement after the error occured. (I noticed this when new tables showed up when I wasn't expecting them because it should have rollbacked)

any help in this department is welcome

if more speficics are needed, ask!

greetz

Answer 1

When the error occurs, the transaction is rolled back automatically, and the current batch is aborted.

Execution continues into the next batch, however. So all the stuff in the batches after the error gets executed. And then when you check for errors later, you try to rollback an already rolled back transaction.

Also, to stop the entire script, not just the current batch, you should use:

raiserror('Error description here', 20, -1) with log

See my answer here for details on that one.

So you need to check for @error after each batch, I think something like this should work:

BEGIN TRANSACTION
GO

ALTER Stuff
GO

if @@error != 0 raiserror('Script failed', 20, -1) with log
GO

CREATE New Stuff
GO

if @@error != 0 raiserror('Script failed', 20, -1) with log
GO

DROP Old Stuff
GO

if @@error != 0 raiserror('Script failed', 20, -1) with log
GO

PRINT 'No Errors ... Committing changes'
COMMIT TRANSACTION

Answer 2

You could try something like this... If you are using Try block... The error level 16, (or most of application error), immediately transfers the control to the CATCH block without executing any further statements in the try block...

    Begin Transaction

Begin Try

                    --  Do your Stuff

        If (@@RowCount <> 1) -- Error condition
        Begin
            Raiserror('Error Message',16,1)
        End


    Commit
End Try
Begin Catch
    IF @@Trancount > 0
    begin
        Rollback Transaction
    End

    Declare @ErrMsg varchar(4000), @Errseverity int

    SELECT @ErrMsg = ERROR_MESSAGE(),
          @ErrSeverity = ERROR_SEVERITY()

    RAISERROR(@ErrMsg, @ErrSeverity, 1)     
End Catch

Hope this helps...