SQL Server Performance Forum – Threads Archive

ROLLBACK MISSING

Im executing a stored proc to test it. It works fine but when I diliberately cause in error it sends me this message. Server: Msg 2627, Level 14, State 1, Procedure Pa_RM10201RM20101_IN, Line 12
Violation of PRIMARY KEY constraint ‘12132’. Cannot insert duplicate key in object ‘Table1’.
The statement has been terminated.
Server: Msg 266, Level 16, State 2, Procedure usp_myproc, Line 69
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0. The part that concerns me is the second message. Is this telling me that the rollback transaction part is not showing. I know the commit works because it inserted what i wanted. Here is my error handling. IF @@ERROR <> 0
BEGIN
SET @ErrorSave = @@ERROR
ROLLBACK TRANSACTION Insert_Data
RETURN @ErrorSave
END
ELSE
BEGIN
COMMIT TRANSACTION Insert_Data
END
Thanks

As per the first error check whether the transaction is trying to insert duplicate rows on the table. You can code in following way..commit tran,create temp #table, begin tran,statement,commit tran.
Or check your application code, somewhere the application developer might have coded begin tran. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Here is the whole sp. I know the first message is caused by a primary key constraint. What Im trying to figure out is why the second message. I diliberately caused the first error so that I can test my error handling. I just cant figure out why it is trowing the second message. CREATE PROCEDURE usp_myproc @empidchar (12) AS
–Variable declaration for error handling
DECLARE @ErrorSave int
SET @ErrorSave = 0
BEGIN TRANSACTION Insert_Data INSERT INTO mytable (Fname,
Lname,
ssn,
empid)
SELECT
Fname,
Lname,
ssn,
empid
FROM mytable2
WHERE empid = @empid IF @@ERROR <> 0
BEGIN
SET @ErrorSave = @@ERROR
ROLLBACK TRANSACTION Insert_Data
RETURN @ErrorSave
END
ELSE
BEGIN
COMMIT TRANSACTION
END Thanks

There are a couple of problems [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] One is that your use of @@ERROR is not quite right. The first thing you need to do is save it to a local variable. With your current code, @ErrorSave will always be 0 because @@ERROR will be reset right after the IF @@ERROR <> 0 statement completes thus by the time you save it, it is 0. You need to do something like SELECT @ErrorSave = @@ERROR IF @ErrorSave <> 0 BEGIN ROLLBACK TRANSACTION RETURN @ErrorSave END ELSE BEGIN COMMIT TRANSACTION END Also, from you first post it seems like usp_myproc is actually an inner procedure called by your outer procedure Pa_RM10201RM20101_IN and there may be an issue with your handling of errors in the outer procedure leading to the trancount mismatch HTH Jasper Smith

This can then be simplified a little bit further SELECT @ErrorSave = @@ERROR
IF @ErrorSave <> 0
ROLLBACK TRANSACTION
ELSE
COMMIT TRANSACTION RETURN @ErrorSave be aware though that some errors cause the whole batch to be aborted (rather than just the current statement) so the rollback tran may not always get called. The application also needs to check for the result of its SQL call and if it encounters a failure then it should execute something like if @@trancount > 0
rollback tran if it doesn’t then it can find itself leaving locks on objects eventhough it thinks that the SQL call has completed. Cheers
Twan

I changed the code like you said Twan and it worked thank you. Thanks

]]>