SEO

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?)

"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

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