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?