question

sqlrookie avatar image
sqlrookie asked

Open Transaction Try Catch Block

Hello, I am using Try Catch block to execute few DDL and DML statements. DDL in separate Try Catch and DML in separate Try Catch block. Few DDL statements such as Alter statements errors are not captured in CATCH block and hence leaves the transaction open. Also i want to audit the errors into a table, so as the error is captured by Catch block i am unable to insert the failed script number into audit table. How can i resolve it? Your valuable comments and ideas are highly appreciated. Below are the sample scripts i am using for demo. 1. First TRY CATCH block creates a table and successfully the script number is inserted into audit table. 2. Second Try Catch block purposely using wrong statement which will raise error and here it leaves the transaction open. BEGIN BEGIN TRY BEGIN TRAN; create table dbo.test1 (ID int); COMMIT; PRINT '"Script number Executed"' If @@ERROR = 0 begin Insert Into dbo.ExecutedScriptTrace(Script) Values('123.SQL'); end END TRY BEGIN CATCH PRINT ERROR_MESSAGE(); ROLLBACK; Insert Into dbo.ExecutedScriptTrace(Script,Result) Values('123.SQL','Failed'); RETURN; END CATCH END BEGIN BEGIN TRY BEGIN TRAN; Alter table dbo.test1 add ID int; COMMIT; PRINT '"Script number Executed"' If @@ERROR = 0 begin Insert Into dbo.ExecutedScriptTrace(Script) Values('1234.SQL'); end END TRY BEGIN CATCH PRINT ERROR_MESSAGE(); ROLLBACK; Insert Into dbo.ExecutedScriptTrace(Script,Result) Values('1234.SQL','Failed'); RETURN; END CATCH END
transactionerror-handling
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
The alter table statement is a perfectly valid statement, but at run-time it causes an error as the column already exists - this statement is only compiled at run time, as the query parser has no idea whether this is valid until run-time. This is known as deferred name resolution. As BOL states, these kind of errors at the same level as a TRY-CATCH block, will not be caught. You can demonstrate this by creating a proc : create proc AddDuplicateColumn as begin Alter table dbo.test1 add ID int end then calling that within a TRY-CATCH BEGIN BEGIN TRY BEGIN TRAN; exec AddDuplicateColumn COMMIT; PRINT '"Script number Executed"' If @@ERROR = 0 begin Insert Into dbo.ExecutedScriptTrace(Script) Values('1234.SQL'); end END TRY BEGIN CATCH PRINT ERROR_MESSAGE(); ROLLBACK; Insert Into dbo.ExecutedScriptTrace(Script,Result) Values('1234.SQL','Failed'); RETURN; END CATCH END this time the error is caught and handled
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Riley, Thanks for the idea. I googled a bit and came to know that these kind of run time errors cant be captured, but this leaves the transaction open. How am i suppose to rollback such errors?
0 Likes 0 ·
I believe there is no other way to capture run time deferred name resolution errors, but using SET XACT_ABORT ON there wont be any open transaction hanging around.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.