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?
Best Answer
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 @@errorto something likeprint '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 ONbefore you begin your transactions. Then, you can rollback your changes upon getting your first error.