2

enter image description here When I try to close the SS Management Studio window below is the error

I understand that DDL statements are implicit COMMIT and the below WARNING message is because BEGIN TRAN never COMMIT or ROLLBACK. Alter DDL statement aborted and never looped through CATCH block. Unable to understand why the control is not passed to CATCH block after the error (this does happen with any other DML statement) Can you please explain why the statements in the CATCH never executed for this DDL error?

  • 1
    "I understand that DDL statements are implicit COMMIT" - I think you are confusing Oracle (or MySQL) and SQL Server. – a_horse_with_no_name Nov 24 '14 at 10:00
7

Why isn't it caught? Because TRY / CATCH doesn't catch all types of errors. But you can get it to catch the non-system-critical stuff by wrapping it in an EXEC(), such as:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY

The error in the ALTER will fail out to the EXEC, which in turn will return to the TRY / CATCH block reporting a simple, catchable error.

For more detail, the MSDN page for TRY...CATCH states:

The following types of errors are not handled by a CATCH block when they occur at the same level of execution as the TRY…CATCH construct:

  • Compile errors, such as syntax errors, that prevent a batch from running.

  • Errors that occur during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

That second bullet point, about object name resolution errors, is what is causing the ALTER statement to fail and not be eligible to get handled by the CATCH block.

EDIT:
Regarding your statement about "DDL statements are implicit COMMIT": I am not sure what you mean by this exactly, but all (well, nearly all) queries are transactions by themselves. Meaning, single-query batches have no use for explicit BEGIN TRAN / COMMIT / ROLLBACK. If the statement fails it automatically rolls back, and if it succeeds then it automatically commits. You would only use the BEGIN TRAN / COMMIT / ROLLBACK construct if you want to conditionally rollback for some reason. It is still a good idea to use the TRY...CATCH structure to properly handle errors, but you gain nothing (at least with the code posted in the question) with the explicit transaction handling. There is absolutely no difference between the posted code and the following:

BEGIN TRY
   EXEC('ALTER TABLE...');
END TRY
BEGIN CATCH
   SELECT 'I''m in Catch';
END CATCH;
  • In fact doesn't the error occur during compile time, not run time? In which case, there is no TRY, CATCH, or spoon. – Aaron Bertrand Nov 22 '14 at 13:25
  • @AaronBertrand actually no. The part of the 2nd bullet that I didn't paraphrase states "after compilation because of deferred name resolution." You can test this by running just the following two statements together: SELECT 1; SELECT * FROM dbo.bob;. The SELECT 1; query still executes and returns 1 and then the batch fails. – Solomon Rutzky Nov 22 '14 at 13:50
  • @AaronBertrand : To clarify, it would be a compile error if it were an existing object but an invalid field reference as that name resolution is not deferred. For example, run the following by itself: CREATE TABLE #bob (col1 int);. And then run the following two statements together: SELECT 1; SELECT b.Col2 FROM #bob b;. That second set won't return 1 prior to erroring. – Solomon Rutzky Nov 22 '14 at 14:03
  • Yes, you're right, sorry. Deferred name resolution is something I wish there were a global option to just turn off. sommarskog.se/strict_checks.html – Aaron Bertrand Nov 22 '14 at 14:29
  • 1
    @AaronBertrand : and to be very clear ;), even compilation errors can be trapped if placed into a sub-process. Run this by itself: CREATE TABLE #bob (Col1 INT);. Now run this entire thing by itself: BEGIN TRY SELECT 1; EXEC('SELECT b.Col2 FROM #bob b;'); END TRY BEGIN CATCH SELECT 23; END CATCH;. Here the batch is not aborted and the CATCH block is entered, even though the SELECT within the EXEC is a compilation error. – Solomon Rutzky Nov 22 '14 at 20:29
0

This error came because ALTER command was not satisfied , it could be because of any reason. Table doesn't exists ,etc. In this case Begin Tran started but it didn't went through ..so it was not able to commit. So when you close the session, SSMS ask that " There are uncommitted transactions. Do you wish to commit these transactions before closing the window? " .. As Transaction was begin but neither committed nor rollback.

In SQL Server, Transactions are auto committed , but in case you have mentioned any query specifically in "Begin Tran -Commit " , it should be finish with commit or rollback

  • This does not answer the O.P.'s question. He knows why it is saying "there are uncommitted transactions". His question is (as stated in the question): "Can you please explain why the statements in the CATCH never executed for this DDL error?" – Solomon Rutzky Nov 24 '14 at 6:15
  • CATCH never caught error for "non-existent table error". Please refer to below link: msdn.microsoft.com/en-IN/library/ms175976.aspx It has the list of those where TRY-Catch doesnot effect. But if any stored procedure is been executed with Try-Catch , then Catch part will be executed. – Priya K Nov 24 '14 at 7:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.