Rollback group of DDL statements

Question

Working in SQL Server 2008 R2, I am trying to rollback a set of DDL statements as a group (think of an upgrade script for a database), but am running into trouble.

Take the following code:

begin try

begin tran

create table foo (i int)
alter table foo add x dog
insert into foo select 1,1
insert into foo select 1,1,1

commit tran

end try

begin catch

rollback tran
print @@error

end catch

I'm expecting the try to fail on the alter table statement, drop to the catch, rollback the transaction, and print the error message. However, if you check your objects/tables, you'll see that foo is still there (so the create table didn't rollback properly).

select * from sys.objects where name = 'foo'

What am I doing wrong here?

Answer 1

The reason you're seeing this result is that SQL Server is not actually catching your ALTER TABLE error. You'll notice that when you run this, you see the red error message rather than a printed line--you can verify this by changing print @@error to something like print 'HELLO!'; in that case, you will NOT see 'HELLO!' printed; you will see the error instead. Books Online has a list of cases of errors which you cannot catch.

An alternative here is to SET XACT_ABORT ON before you begin your transactions. Then, you can rollback your changes upon getting your first error.

Answer 2

The error is happening because the error being thrown part of a recompile error due to deferred name resolution. Looking at SQL BOL those aren't trapped when they happen at the same level as the try...catch. However, if it's happening at a different level, either as dynamic SQL or a SP call, then it will get caught and rolled back.

Using Profiler you can see that the "alter table foo add x dog" statement recompiles before executing and then errors and bypasses the catch block.

If you wrap the statements in dynamic SQL the error isn't returned to Profiler and the transaction is rolled back

Answer 3

Things are a bit simpler imho. Your catch block is not invoked because your code never executes. SQL Server receives a block of text:

blah blah
blah
blah blah
blah

It then tries to make sense of it so it parses it, which transforms it into a syntactic tree, but it still not yet runnable. To make it runnable it needs to compile it and at this stage it bombs and an exception is thrown. At no time was the request actually executing, so your try/catch block did not even start. This is always the case with syntax and compile errors vs. execution errors. You can push the transaction and exception handling up a frame on the stack, either by starting the transaction from the client (eg. wrap it in a TransactionScope) or by adding a frame in the batch (ie. use sp_executesql).

But alas this is a lost cause. Doing a one-transaction migration is never going to work in practice. At the very least think about all the DDL that doe snot support transactions and second think about what will happen to your log when you issue DDL which is size-of-data (eg. ALTER TABLE ... ADD COLUMN ... WITH VALUES).

Take a backup, run your migration, if it bombs restore from backup. Is the only viable option.