开发者

Why does the Try/Catch not complete in SSMS query window?

This sample script is supposed to create two tables and insert a row into each of them.

If all goes well, we should see OK and have two tables with data. If not, we should see FAILED and have no tables at all.

Running this in a query window displays an error for the second insert (as it should), but does not display either a success or failed message. The window just sits waiting for a manual rollback. ??? What am I开发者_如何学运维 missing in either the transactioning or the try/catch?

begin try
begin transaction

create table wpt1 (id1 int, junk1 varchar(20))
create table wpt2 (id2 int, junk2 varchar(20))

insert into wpt1 select 1,'blah'
insert into wpt2 select 2,'fred',0  -- <<< deliberate error on this line

commit transaction 
print 'OK'
end try

begin catch
rollback transaction
print 'FAILED'
end catch


The problem is that your error is of a high severity, and is a type that breaks the connection immediately. TRY-CATCH can handle softer errors, but it does not catch all errors.

Look for - What Errors Are Not Trapped by a TRY/CATCH Block:

It looks like after the table is created, the following inserts are parsed (recompiled), which trigger statement level recompilations and breaks the batch.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜