April 9, 2012 at 12:58 pm
I'm trying to troubleshoot a stored procedure error and not having much luck so far.
I have a procedure that is set up as
DECLARE @errormessage varchar(1000) = ''
BEGIN TRAN
BEGIN TRY
<do stuff, including other stored procedures>
END TRY
BEGIN CATCH
SELECT @errormessage = 'Bad stuff happened'
PRINT @errormessage
END CATCH
IF @errormesage = ''
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
This procedure normally gets ran within other stored procedures that have their own transactions.
The other procedures usually do this for error handling:
BEGIN TRY
BEGIN TRAN
<do stuff>
COMMIT TRAN
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK;
<rethrow error message>
END CATCH
What I find happening fairly regularly however, is that if the first procedure is called within any of the other procedures, or is even called with an BEGIN TRAN/COMMIT TRAN, it throws a "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."
It doesn't look like XACT_ABORT is on any any of the procedures in the chain that I can see, which was my first thought. That top procedure is an older one that I can't just alter even though I don't like that its error handling is different from everyone else's (which was my first instinct). I might be able to make a case for changing it if it really is something in that error handling design, but if it is, I'm not seeing what it would be yet.
April 9, 2012 at 5:13 pm
This is a very small amount of info to go on....but I'll take a guess. It looks like your inner proc is not raising an exception whenever it rolls back so your outer proc is still trying to call COMMIT, causing the error you're seeing about the tran counts.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 9, 2012 at 5:16 pm
Actually, the inner rollback rolls back all open transactions.
April 9, 2012 at 5:23 pm
opc.three (4/9/2012)
This is a very small amount of info to go on....but I'll take a guess. It looks like your inner proc is not raising an exception whenever it rolls back so your outer proc is still trying to call COMMIT, causing the error you're seeing about the tran counts.
Yeah, that's exactly what it seems to be.
If I do this:
create proc #a
as
begin try
begin tran
exec #b
commit tran
end try
begin catch
IF @@TRANCOUNT > 0 ROLLBACK;
exec usp_RethrowError;
END catch
go
create proc #b
as
BEGIN TRAN
BEGIN TRY
DECLARE @er varchar(max) = ''
select @er = 'Blah'
END TRY
BEGIN CATCH
select @er = 'error!'
END CATCH
IF @er = ''
BEGIN
COMMIT TRAN
END
ELSE
BEGIN
ROLLBACK TRAN
END
go
exec #a;
I get the transaction count error. If I add a RAISERROR(@er, 16,1) to #b in the ELSE statement with the rollback, it errors as I would want it to.
Don't know how I didn't see that. Thank you!
April 9, 2012 at 5:24 pm
Lynn Pettis (4/9/2012)
Actually, the inner rollback rolls back all open transactions.
Right...what I was thinking is the COMMIT in the outer proc would be called when @@TRANCOUNT was 0.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 10, 2012 at 8:41 am
jwhisman (4/9/2012)
opc.three (4/9/2012)
This is a very small amount of info to go on....but I'll take a guess. It looks like your inner proc is not raising an exception whenever it rolls back so your outer proc is still trying to call COMMIT, causing the error you're seeing about the tran counts.Yeah, that's exactly what it seems to be.
<truncated>
Don't know how I didn't see that. Thank you!
No problem, happy to assist. Curious, any plans on how to provide for the scenario in your environment since you cannot change what we're calling "the inner proc"?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 10, 2012 at 3:20 pm
opc.three (4/10/2012)
No problem, happy to assist. Curious, any plans on how to provide for the scenario in your environment since you cannot change what we're calling "the inner proc"?
Well, while I can't change the "inner" proc in this case, I'm hoping to convince the people who can to do so, so that it matches what the company wants to use for their error handling.
For now, I'm going to user ERROR_NUMBER() in my proc's catch to look specifically for error 266 and handle it as a special case. The inner proc has its error message parameter as an output parameter, and it looks like it's being set appropriately. I'll just raise an error based off whatever's in the output parameter and go from there. Looks like it's working in my initial testing, at least.
April 10, 2012 at 3:29 pm
Tough spot. I was thinking of checking @@TRANCOUNT immediately after the call to said "inner proc" and then explicitly raising your own error if things were not as expected after a successful call...there are lots of ways. Sounds like you have it covered. Thanks for posting back.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply