Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Question

Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements

Roust_m

SSCoach

Points: 17410
More actions
May 21, 2009 at 11:17 pm

#137965

Hi,
I am trying to implement some error handling. Inside the stored procedure I have the below code. When the transaction is successful, it all goes fine. When I try to imitate the transaction failure by renaming the table the query refers to, apart from the original error message, it gives me a very strange error about the transaction count:
Msg 208, Level 16, State 1, Procedure MySP, Line 43
Invalid object name 'WrongTable'.
Msg 266, Level 16, State 2, Procedure MySP, Line 43
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
What wrong am I doing?
Thanks.
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);
BEGIN
SET NOCOUNT ON
DECLARE @MaxCount AS INT
SELECT @MaxCount = MAX(Id)
FROM
dbo.MyTable1
SET @MaxCount = ISNULL(@MaxCount,0)
IF @MaxCount = 0
RETURN 0
BEGIN TRANSACTION
BEGIN TRY
INSERT MyTable1_archive(...)
SELECT ...
FROM
MyTable1
WHERE Id <= @MaxCount DELETE FROM WrongTable WHERE Id <= @MaxCount END TRY
BEGIN CATCH
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.
RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);
END CATCH
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Check out this blog post. I think it explains why you are seeing this behavior.

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

Eichpeel Ten Centuries Points: 1079 More actions · Answer 2

I have been using the template below and it has worked great for me. The article is really good too.

http://www.sqlservercentral.com/articles/Development/anerrorhandlingtemplatefor2005/2295/

Hope this helps.

"Any fool can write code that a computer can understand. Good programmers write
code that humans can understand." -Martin Fowler et al, Refactoring: Improving the Design of Existing Code, 1999

Roust_m SSCoach Points: 17410 More actions · Answer 3

Ok, what I was missing is:

SET XACT_ABORT ON

After I put it into my SP all worked as expected.

Lee Timms Valued Member Points: 51 More actions · Answer 4

I had the very same problem, the solution was when i found the following.

I was doing an insert into a table which caused a trigger to fire on that table. The trigger was trying to insert data into another table to a column that did not exist and the triggers failure caused the error. I hope this was a help