It was probably one of those
forgone conclusions that I would somehow be disappointed with
the promise of new and better error handling capabilities in
Yukon. After all, it would be a colossal leap from the
catch-me-if-you-can @@error we've learned to live with up
through SQL Server 2000 to any sort of a legitimate - read that
consistent, robust and reliable - error handling capability in
T-SQL. I have to admit that my eyes glazed and I stopped
listening way too soon when I first heard there would be
exception handling in Yukon. I understood that to mean real,
catch all the errors one could ever dream of catching and handle
expediently as if you were suddenly a programming language
exception handling. I ignored the fine print such as the word
"transaction" being peppered throughout even the earliest
blurbs. I even managed to convince myself that
fatal-to-the-batch was about to become a thing of the past. All
I needed to know was that Yukon boasted Try-Catch in T-SQL and I
was floating in the clouds. When I finally read the Books Online
pages in the beta 1 release that talk about Try-Catch my inner
helium voice took a blast of smog. Then I went back and re-read
Itzik Ben-Gan's T-SQL enhancements white paper found on the
resource CD that came with the beta and sure enough - plain as
day - he had much more accurately describe what I was to find in
the beta 1 release than what I remembered reading. I had simply
blanked out all the details that didn't match my delusion.
Still, even after getting my eyes opened a little about what we
really have with this new Try-Catch feature, I decided to give
it a go and see what I could do with the new CLR-ish error
handler. After some testing I'm still left with some questions
and some disappointments though I remain hopeful that with beta
2 and beyond Yukon will move us to the lofty heights of error
handling I had already imagined. For now at least, the balloon,
even if thin and round and red, is still blown with just a
little too much lead. Let me lay out a test case for you to show
you what I learned. First, it may be useful to begin with a
discussion of the Try-Catch rules.
The Rules
The most important rule is that
errors must occur inside the Try block and must be raised at the
"transaction abort" level in order handle them in the Catch
Block. The Try and Catch blocks must be in the same "batch" and
the Catch block must occur immediately after the Try block. The
syntax then looks like:
begin try
{sql statement | sql block }
end try
begin catch tran_abort
{sql statement | sql block}
end catch
To effect the Catch block you
have to be in a transaction. Further, to make sure all errors
are raised at the "transaction abort" level you must be running
with SET XACT_ABORT ON. My tests indicate that in the case where
you raise custom errors with the new TRAN_ABORT switch you do
not need to have XACT_ABORT on but if you want to trap system
raised errors the only option seems to be to SET XACT_ABORT ON.
Furthermore, you need to use explicit Begin Tran and Commit
[Tran] statements in the Try Block and Rollback [Tran] in the
Catch block. Being in a transaction gives us the ability to
commit and, at any time appropriate, roll back all statements in
the transaction. As we know - even in pre Yukon T-SQL - without
XACT_ABORT ON a rollback will not rollback all changes in a
multi-statement transactional chain of statements. With the
"transaction abort" requirement for Try-Catch processing the
real meaning of XACT_ABORT becomes more clear and relevant. An
error must be raised at the "transaction abort" level in order
for the the Catch block to be able to ... well... catch it.
Turns out that this is exactly what SET XACT_ABORT ON does. It
raises all errors at the "transaction abort" level. Didn't you
always wonder? As mentioned, with RAISERROR there is a new
switch: WITH TRAN_ABORT so presumably you can raise your own
application errors and still get the benefit and use of your
procedure's Try-Catch handler.
Try-Catch blocks may be nested.
Fatal to the Batch
According to the documentation:
When a transaction abort
error occurs within a TRY block, an exception is thrown and
the program control is transferred to the associated CATCH
block. The CATCH block handles the exception and the program
control is transferred to the first T-SQL statement that
follows the current TRY...CATCH construct.
I understand this to say that my
batch will continue to execute until I stop it. Interestingly
this didn't quite turn out to be my experience. What I observed
is that I could not return results or make calls to stored
procedures either in the Catch block or after the Catch block if
a "transaction abort" error occurred. I was even able to easily
find an error condition that broke the Try-Catch processing all
together and was in fact immediately fatal to the batch when it
occurred. What's worse, that error also left a transaction open.
Fortunately I could still do inserts, updates and deletes and I
could still "print" stuff. However, the only selects that were
possible once program control was passed to the Catch block were
the case where I inserted the selected result back into the
database. I couldn't get any selects in the batch to return
results to the Workbench results pane once an error had been
caught even though the line of T-SQL code was obviously
processed. Very mysterious indeed.
Incredible Possibilities
I have to say that after a little
testing I am impressed with Try-Catch processing. If you'd like
to take a closer look at my test I've posted
the script
along with the output for those that don't have a copy of the
beta software to try it out for themselves. I'm guessing the
inability to return results once the Catch block kicks in is
either a bug in my test setup or a beta 1 bug. I have to admit
I'm not seeing anything I could do to get this working as I
would expect it to work. I was also disappointed that I still
have a situation where some errors will be fatal to the batch
and some won't. In truth the most frustrating thing about that
behavior is having to figure out and remember which errors fall
into which category. As with select processing, I'm going to
keep hoping for a fix to this one before Yukon goes gold. I'm
less certain this one will change, but I wish I didn't have to
be in a transaction to use this error handling method. Makes
sense to me that it would be tough to get Try-Catch to work
without the transaction, particularly in a simple recovery
model, still I've no doubt that the minds on the SQL Development
team can come up with some way to hang on to transaction log
info for Try-Catch processing that doesn't have the contention
issues that surround a long running transaction. That's going to
be a real limiting factor for my ability and interest in
Try-Catch exception handling. Seems like they are almost there
with Snapshot Isolation so maybe I won't completely give up hope
just yet. Guess I'll take a closer look at Snapshot
Isolation and all the other cool stuff in Yukon beta 1 and give
'em a little more time on the Try-Catch error handling before I
make any final decisions about where it will and won't help me.
Oh yea, and I need to figure out why Set NOCOUNT ON isn't
working for me...
Bill