"Robin Tucker" <idontwanttobespammedanymore@reallyidont.com> wrote in
message news:cs3h7l$6pk$1$830fa7b3@news.demon.co.uk...
>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?)
>

One option is to use a permanent or temp table as a log table - check
@@ERROR after each action, and then INSERT/UPDATE whatever information you
want to capture in the table. That could just be a simple flag value, or you
could add details of what failed and when if that would be useful for
diagnosis. At the end of the script, you can then check to see if there are
any rows in the table, and rollback if there are (or whatever logic you
prefer, based on what you logged).

Simon