May 18, 2004 at 7:44 am
Hi,
I'm testing a simple sproc. Basically, I'm trying to wrap the entire sproc into a transaction and roll back everything if there's an error. Nothing fancy. The code below checks @@error after each statement and if there was an error, it calls the ERR_HANDLER which performs a ROLLBACK.
The sproc works as expected if another user holds a lock to one of the records that we are trying to update.
However, I've added some code that triggers a runtime error (the table doesn't exist). This triggers the expected error (invalid object), but it also reports that the transaction count is mismatched.
Worse, the changes are committed eventhough there was an error! I suspect that there's an implicit COMMIT somewhere but have no idea where.
Any ideas what's going on here?
Thanks in advance
Here are the error messages when a runtime error occurs:
Server: Msg 208, Level 16, State 1, Procedure usp_t4, Line 16
Invalid object name 'test40'.
Server: Msg 266, Level 16, State 1, Procedure usp_t4, Line 26
Transaction count after EXECUTE indicates that
a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.
Here's the sproc:
CREATE PROCEDURE usp_t4
AS
BEGIN TRANSACTION
UPDATE test4 SET cValue='sproc' WHERE nId=2
IF (@@ERROR <> 0) GOTO ERR_HANDLER
UPDATE test4 SET cValue='sproc' WHERE nId=3
IF (@@ERROR <> 0) GOTO ERR_HANDLER
UPDATE test4 SET cValue='sproc' WHERE nId=4
IF (@@ERROR <> 0) GOTO ERR_HANDLER
-- the following line triggers a runtime error since
-- table test40 doesn't exist
UPDATE test40 SET cValue='sproc' WHERE nId=5
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
ERR_HANDLER:
--RAISERROR('Stored procedure failed. Transaction has been rolled back.', 16, 1)
ROLLBACK TRANSACTION
RETURN 1
GO
May 18, 2004 at 1:30 pm
You are never getting to your error handling routine because the SQL Statement with the error is never being executed so the procedure terminates at that point without hitting a rollback or commit. @@ERROR only works when the SQL Statement is executed and then gets an error like a constartint violation. If you are testing in QA or another sql tool and still have access to the session that is creating the error you can issue a rollback transaction and the changes will be rolled back. If you have a situation where you may try to update a table that does not exist you should use IF Exists (select * from sysobjects where name = table_name) to catch it.
To see the open transaction(s) you can run DBCC OPENTRAN("db").
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 19, 2004 at 7:21 am
Jack,
Thanks for your explanation. This makes sense now. I didn't know that @@ERROR doesn't trap runtime errors and that the sproc would be terminated when a runtime error occurs.
Anyway, I solved my problem by wrapping the call to the sproc with a transaction in the calling method.
Thanks again.
Daniel
May 19, 2004 at 2:58 pm
if IMPLICIT_TRANSACTIONS is on, then the explicit tran plus the implicit tran started by the first update would make the trancount 2. by using the optional "TRANSACTION" keyword with "ROLLBACK", the trancount is decremented by only one and hence the error. you can check IMPLICIT_TRANSACTIONS status with this:
SELECT @@OPTIONS & 2
2 = on
0 = off
this issue can be avoided by using only "ROLLBACK". this will rollback all uncommited transactions and decrement trancount to zero.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply