Take the 2-minute tour ×

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?

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted

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.

share|improve this answer
If I intend to have a series of, say, 100 DDL statements similar to my sample, what is the best method to handle errors then? Basically I want to attempt to execute all 100 and, if an error occurs, log that error to a table and rollback everything. The DDL is stored in a table and executed via sp_executesql. –  Derek Kromm Nov 29 '12 at 21:14
Actually, I found a pseudo-answer in your BOL link. If I create a proc that is simply "exec sp_executesql @param_stmt", and pass my DDL statements into that procedure, I can wrap them in a TRY/TRAN and appears to correctly handle the COMMIT/ROLLBACK/CATCH. Very weird behavior, but I guess the failing of the proc is catchable whereas the DDL statement alone is not. Thanks for pointing me in the right place :) –  Derek Kromm Nov 29 '12 at 21:19
@Derek Red Gate SQL Compare does this by setting XACT_ABORT ON, creating a #temp table, and storing any errors there (checking after every statement). You can see a sample here. It's a pain but depending on how you are generating your DDL statements you should be able to automate something similar. –  Aaron Bertrand Nov 29 '12 at 21:20
add comment

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.

Profiler Trace With Error

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

Profiler Trace Without Error

share|improve this answer
The severity is actually 16 according to Profiler. I'm updating my answer with that info and a correction due to some SQL oddness (same error thrown to SSMS but different behavior in Profiler because the table existed.) –  cfradenburg Nov 29 '12 at 21:12
+1 good detective work, I would not have expected a re compile here. –  Aaron Bertrand Nov 29 '12 at 21:22
The test I based the compile version on wasn't accurate. The foo table existed when I started the run so what Profiler gathered wasn't accurate. What's interesting is that the error shown in SSMS was just the one you quote above so I didn't catch that I hadn't cleaned up properly. I'm not sure why it didn't return the first error and tell me that foo already existed. –  cfradenburg Nov 29 '12 at 21:24
add comment

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

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.

share|improve this answer
add comment

Your Answer


