March 20, 2008 at 9:29 am
Hi,
I have a basic store procedure which performs updates on several tables, into a transaction. But i'm getting the following error when I introduce a fake error(a table name that does not exist) to test the sp:
"Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1."
Here is the skeleton of my sp
CREATE PROCEDURE [dbo].[Sp_Update_Fact] AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
-- Statement 1
-- Statement 2
-- Statement 3
-- Statement 4
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
I don't understand why sql server seems to ignore the COMMIT and ROLLBACK.
One more detail, I'm calling the sp with an Exec
Does anyone have an idea ?
Thanks in advance
Pat
March 20, 2008 at 10:56 am
I'm guessing something else in the code because I took your example, added a statement, and it's running just fine.
CREATE PROCEDURE [dbo].[Sp_Update_Fact] AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
EXEC sp_who2
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
END
GO
exec sp_update_fact;
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 20, 2008 at 11:00 am
I am reading that to say that whatever you are calling with EXEC is starting a transaction, but not performing a COMMIT or ROLLBACK there. Is that a possibility?
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 20, 2008 at 1:25 pm
In Catch statements, when I want to roll back, I usually use:
While @@trancount > 0
rollback
That gets rid of that error.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2008 at 12:44 am
Matt,
Yes I think so, but even if a put a commit or rollback right after the exec, the message remains. I'm trying to figure why...
Still, when I execute the sp right after the creation (as Grant suggested), no error. Unfortunately, it's not a solution.
March 21, 2008 at 12:52 am
patrick h (3/21/2008)
Matt,Yes I think so, but even if a put a commit or rollback right after the exec, the message remains. I'm trying to figure why...
Still, when I execute the sp right after the creation (as Grant suggested), no error. Unfortunately, it's not a solution.
Could it be there's an error occurring? Remember that not all errors can be "caught" with a CATCH (only if between 11 and 20 AND doesn't terminate the connection/batch), in which case there MAY be times when neither the commit not the rollback occur.
Also - are you SURE it's getting to the COMMIT? there are lots of possibilities with things like RETURN, BREAK, etc... for the flow of execution to not make it that far... and no error.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 21, 2008 at 1:08 am
Matt Miller (3/21/2008)
patrick h (3/21/2008)
Matt,Yes I think so, but even if a put a commit or rollback right after the exec, the message remains. I'm trying to figure why...
Still, when I execute the sp right after the creation (as Grant suggested), no error. Unfortunately, it's not a solution.
Could it be there's an error occurring? Remember that not all errors can be "caught" with a CATCH (only if between 11 and 20 AND doesn't terminate the connection/batch), in which case there MAY be times when neither the commit not the rollback occur.
Also - are you SURE it's getting to the COMMIT? there are lots of possibilities with things like RETURN, BREAK, etc... for the flow of execution to not make it that far... and no error.
Actually, I did put an error (I put a table that does not exist) just to test the sp, and that's why I get this message. But it returns a error severity of 16 and terminate the batch.
To see what's executed, I put some prints, and it doesn't get to the COMMIT nor the ROLLBACK.
How can I be sure I catch all errors ?
March 21, 2008 at 7:03 am
To catch those, put a Try/Catch in the called proc.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 21, 2008 at 7:22 am
Good discussion, only I wish to point out that I think
While @@trancount > 0
rollback
isn't necessary although it certainly doesn't hurt and it's good self-documenting code 🙂
I believe a single rollback will undo all nested transactions - you cannot do something like
begin transaction
//want to keep
begin transaction
//something which we'll cancel
rollback transaction
//more that we wish to keep
commit transaction
The inner rollback will cancel the transaction. I haven't played with try/catch too much though so there could be something I've missed.
March 21, 2008 at 7:34 am
GSquared (3/21/2008)
To catch those, put a Try/Catch in the called proc.
by putting the sp between try/catch resolves the problem, and i'm able to rollback the transaction started in the sp
thanks a lot to all you guys, it helped me knowing sql server behaviour better !
March 21, 2008 at 7:35 am
No. You haven't missed anything. It's unneccessary to use the While loop on that. I do it as a practice, because there are times when I use the opposite:
while @@trancount > 0
commit
Per BOL: "ROLLBACK TRANSACTION erases all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT."
Thus, it is redundant. I merely use it for consistency. But, actually, it doesn't do anything for the problem the OP had, and I'm not sure why I posted it for that. Wasn't paying enough attention.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply