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?
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;
SELECT 1; SELECT * FROM dbo.bob;. The SELECT 1; query still executes and returns 1 and then the batch fails.
Commented
Nov 22, 2014 at 13:50
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.
Commented
Nov 22, 2014 at 14:03
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.
Commented
Nov 22, 2014 at 20:29
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
