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
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
14 People are following this question.
