Sometime, we will miss out to have the proper coding.. For handling exceptions in SQL Server, we usually making use of TRY..CATCH blocks along with TRANSACTIONs. What i would like to say is, if there is no ROLLBACK statement in the CATCH blcok, the T-SQL / Stpred Procedure will throw error (Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.)
Try the below code snippet, you will get the error message 3998
CREATE TABLE TEMP ( A INT )
ALTER TABLE TEMP
DROP COLUMN author
-- Execute the error retrieval routine.
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() as ErrorState,
ERROR_PROCEDURE() as ErrorProcedure,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage;
PRINT ' *****Value of XACT_STATE ****'+CONVERT(VARCHAR,XACT_STATE())
--ROLLBACK TRAN -- Uncomment this line for avoiding error
To resolve the issue, uncommnet the ROLLBACK TRAN statement in the CATCH block and run the T-SQL code..