May 10, 2007 at 8:48 pm
Hi All,
Im using the TRY....CATCH errorhandling technique for SQL Server 2005. Im trying to simulate if my errorhandler will work if I pass on an error insaide a transaction. I try to pass on an invalid table (object) so that I may test if the transaction works. But unfortunately I get this error:
Msg 208, Level 16, State 1, Procedure uspDelBaselineLookup, Line 17
Invalid object name 'STGMAPPING2'. (-- THIS IS OK SINCE THIS IS WHAT IM SIMULATING)
Msg 266, Level 16, State 2, Procedure uspDelBaselineLookup, Line 17
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1. -- (THIS IS MY PROBLEM)
BELOW IS MY CODE:
DECLARE
@TABLENAME SYSNAME
BEGIN
TRY
SET
NOCOUNT ON;
BEGIN TRANSACTION
SELECT @TABLENAME = 'STGMAPPING'
DELETE FROM STGMAPPING2
WHERE BASELINE_FLAG = 1
COMMIT
TRANSACTION
END
TRY
BEGIN
CATCH
ROLLBACK TRANSACTION
-- LOG THE ERROR
INSERT INTO ERRORLOG (ERRTABLENAME, ERRNUMBER,
ERRSEVERITY
, ERRSTATE, ERRPROCEDURE, ERRLINE, ERRMESSAGE, ERRRUNDATE)
VALUES (@TABLENAME, ERROR_NUMBER(),
ERROR_SEVERITY
(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(), GETDATE())
END
CATCH
May 10, 2007 at 11:38 pm
The TRY...CATCH block in sql server 2005 needs SET XACTABORT set to be on so that the rollback transaction can be handled in the catch block of the TSQL statement. Do you have SET XACT_ABORT ON if not try using that and you will succeed.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
May 11, 2007 at 9:37 pm
Some errors are not handled by TRY/CATCH. If you have an invalid object or column name, the batch will fail and the CATCH block will not execute.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply