Robin Tucker (idontwanttobespammedanymore@reallyidont.com) writes:
> I have a query batch "update" script that upgrades my users database
> from, say version 0 to version 1, or from version 1 to version 2. I
> would like to know how I can wrap the entire script in a transaction, so
> that either the whole thing succeeds or none of it does.
>
> For example:
>
> BEGIN TRANSACTION
> .....
> ..... Alter some tables
> .....
> GO
> .....
> ..... Alter a stored procedure
> .....
> GO
> .....
> ..... Create a new stored procedure
> .....
> GO
>
> COMMIT TRANSACTION
> or
> ROLLBACK TRANSACTION
> GO
>
> (how do I get to the "ROLLBACK TRANSACTION" if an error occurs in the
> update script?)

You would have to do something like:

BEGIN TRANSACTION

EXEC('ALTER TABLE ...')
IF @@error <> 0 GOTO panic
...
EXEC('ALTER PROCEDURE ....')
IF @@error <> 0 GOTO panic
...
COMMIT TRANSACTION
GOTO out

panic:
ROLLBACK TRANSACTION

out:

You cannot use separate batches like above, because some errors may
lead to batch-abortion, and in this case any open transactions are
rolled back. This would mean that the rest of the script jogged along
without any transaction. That's really bad!

With the scheme above, batch-aborting errors termintates the entire script,
because it is all one batch, and in case of an error that does abort
the batch, you go to the emergency exit.

The above is not very fun to code, since you must cate for embedded
codes and all that. If you are to run this in OSQL, there is an
alternative. After each real batch, add:

IF @@error <> 0
BEGIN
ROLLBACK TRANSACTION
RAISERROR('I''m outta of here!', 127, 16),
END

The important thing is the state 127, which causes OSQL to exit. But
note that this does not work in Query Analyzer. And, I am not 100% sure
that you can rely on that @@error is set after a batch-terminating error.
It should be, but I'm not sure that I would like to trust it.

So in the end, a better alternative is run all from some client program,
written in Perl, VBscript or similar. Then you can have the error checking
in one place, and have the program to find the code snippets you want
to load in a directory, and you would reuse that client program for
future updates as well.


For more information about batch-aborting errors etc, see
http://www.sommarskog.se/error-handl...ml#whathappens

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp