February 3, 2012 at 2:52 pm
I am using SQL Server 2008 R2 and I was receiving the error message: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0."
I am executing something like this:
SET XACT-ABORT ON;
BEGIN TRY
BEGIN TRANSACTION;
EXEC PROC1;
EXEC PROC2;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
PROC1 and PROC2 are very similar (but operate on separate tables) and look like this:
SET XACT-ABORT ON;
DECLARE @TheID AS int;
BEGIN TRY
BEGIN TRANSACTION;
DECLARE Mommy CURSOR FOR
SELECT [ID] FROM tblServices;
OPEN Mommy;
FETCH Mommy INTO @TheID
WHILE @@FETCH_STATUS = 0
<various updates using @@TheID and the captured @@IDENTITY value>
FETCH Mommy INTO @TheID
END
CLOSE Mommy;
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
END CATCH
After much comparison between PROC1 and PROC2 I determined that they were syntactically and logically correct...except for one thing. Both PROC1 and PROC2 used the same indentifier name ('Mommy' in the example above) for the CURSOR name and neither stored procedure DEALLOCATEd the CURSOR.
So, my solution was to rename the CURSORs used in these 2 stored procedures and to use DEALLOCATE as well.
I am just posting this to document my findings, but feel free to comment if you wish. I am still curious what the connection is between using the same CURSOR name and the resulting error concerning the TRANSACTIONs, though.
February 3, 2012 at 3:05 pm
My guess is that either proc1 or proc2 encountered an exception an did a rollback, when returning to the main calling block there would be no transactions.
Take a look at this:
select @@TRANCOUNT
begin transaction
select @@TRANCOUNT
begin transaction
select @@TRANCOUNT
rollback transaction
select @@TRANCOUNT
What do you think the last @@TRANCOUNT will return? 2 transactions have been started but only 1 rollback.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 3:16 pm
Here is a greatly simplified skeleton of what you described you are running.
create procedure proc1
as begin
begin transaction
select 'Proc 1'
rollback transaction
end
go
create procedure proc2
as begin
begin transaction
select 'Proc 2'
rollback transaction
end
go
begin try
begin transaction
exec Proc1;
exec Proc2;
commit transaction
end try
begin catch
rollback transaction
end catch
Each proc here just commits a rollback, which is what yours does inside the catch block. Play around with changing some of these rollbacks to commits and such. These nested transactions can be a bit tricky. 😉
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 3, 2012 at 3:28 pm
I am guessing this would return:
0
1
2
1
But, maybe the ROLLBACK here would actually force all linked transactions to be rolled back and the last @@TRANCOUNT would return 0? Is this especially because of SET XACT_ABORT ON?
February 3, 2012 at 3:34 pm
Another option is to remove the TRY/CATCH and ROLLBACK statements. This depends on what your calling environment expects.
SET XACT_ABORT ON
BEGIN TRANSACTION
EXEC proc1
EXEC proc2
COMMIT
In this case, rollback will be automatic if either proc1 or proc2 raises an error. The calling environment will receive the error.
I have never been able to make use of TRY/CATCH because the original error can't be returned. The procedures I write that require an explicit transaction look like this:
CREATE PROCEDURE DoStuff
AS
SET XACT_ABORT ON
BEGIN TRANSACTION
-- statements
COMMIT
February 6, 2012 at 7:29 am
thayes 89705 (2/3/2012)
I am guessing this would return:0
1
2
1
But, maybe the ROLLBACK here would actually force all linked transactions to be rolled back and the last @@TRANCOUNT would return 0? Is this especially because of SET XACT_ABORT ON?
Don't guess, try it out. 😀 The results will be the same regardless of of the state of XACT_ABORT.
Read what that setting does here. http://msdn.microsoft.com/en-us/library/ms188792.aspx
If you want to conditionally rollback sections of transaction you will have to use nested transactions. See this article from bol. http://msdn.microsoft.com/en-us/library/ms189336.aspx
Hope this helps.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 7:37 am
ok see solution at below link
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/cdf5c2f8-3584-4b20-9848-9a175b4b18f4
February 6, 2012 at 9:27 am
I don't get it... I believe this is syntactically wrong, but I don't use try/catch blocks much. It seems wrong that your commit is inside the try and then a rollback is inside the catch. You can't commit and then rollback, and your syntax commits no matter what. So the rollback will never be executed, i.e. it doesn't have a begin tran associated with it because the commit is inside the try and the rollback is outside.
BEGIN TRAN
BEGIN TRY
<some statements>
END TRY
BEGIN CATCH
<check things>
ROLLBACK
END CATCH
<check something else>
COMMIT
Jared
CE - Microsoft
February 6, 2012 at 9:48 am
SQLKnowItAll (2/6/2012)
I don't get it... I believe this is syntactically wrong, but I don't use try/catch blocks much. It seems wrong that your commit is inside the try and then a rollback is inside the catch. You can't commit and then rollback, and your syntax commits no matter what. So the rollback will never be executed, i.e. it doesn't have a begin tran associated with it because the commit is inside the try and the rollback is outside.
BEGIN TRANBEGIN TRY
<some statements>
END TRY
BEGIN CATCH
<check things>
ROLLBACK
END CATCH
<check something else>
COMMIT
Jared the way you laid that out you have a commit AND a rollback when there is an error. It looked like the template was correct to me. You want the commit inside the TRY block (usually the last line) and the rollback inside the CATCH. That way if anything goes wrong during the transaction it will rollback in the catch. I make it a habit to always doublecheck @@trancount before a rollback to avoid transaction count mismatches.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 9:53 am
Sean Lange (2/6/2012)
SQLKnowItAll (2/6/2012)
I don't get it... I believe this is syntactically wrong, but I don't use try/catch blocks much. It seems wrong that your commit is inside the try and then a rollback is inside the catch. You can't commit and then rollback, and your syntax commits no matter what. So the rollback will never be executed, i.e. it doesn't have a begin tran associated with it because the commit is inside the try and the rollback is outside.
BEGIN TRANBEGIN TRY
<some statements>
END TRY
BEGIN CATCH
<check things>
ROLLBACK
END CATCH
<check something else>
COMMIT
Jared the way you laid that out you have a commit AND a rollback when there is an error. It looked like the template was correct to me. You want the commit inside the TRY block (usually the last line) and the rollback inside the CATCH. That way if anything goes wrong during the transaction it will rollback in the catch. I make it a habit to always doublecheck @@trancount before a rollback to avoid transaction count mismatches.
Got it... That makes sense to me now. Basically, the rollback occurs if the commit fails?
Jared
CE - Microsoft
February 6, 2012 at 9:57 am
SQLKnowItAll (2/6/2012)
Sean Lange (2/6/2012)
SQLKnowItAll (2/6/2012)
I don't get it... I believe this is syntactically wrong, but I don't use try/catch blocks much. It seems wrong that your commit is inside the try and then a rollback is inside the catch. You can't commit and then rollback, and your syntax commits no matter what. So the rollback will never be executed, i.e. it doesn't have a begin tran associated with it because the commit is inside the try and the rollback is outside.
BEGIN TRANBEGIN TRY
<some statements>
END TRY
BEGIN CATCH
<check things>
ROLLBACK
END CATCH
<check something else>
COMMIT
Jared the way you laid that out you have a commit AND a rollback when there is an error. It looked like the template was correct to me. You want the commit inside the TRY block (usually the last line) and the rollback inside the CATCH. That way if anything goes wrong during the transaction it will rollback in the catch. I make it a habit to always doublecheck @@trancount before a rollback to avoid transaction count mismatches.
Got it... That makes sense to me now. Basically, the rollback occurs if the commit fails?
Not JUST the commit but anything inside the try.
BEGIN TRY
BEGIN TRAN
<some statements>
select 1 / 0 --this will fail so we don't want to commit the transaction.
-- When this fails it will fall into the catch so we can rollback the transaction.
COMMIT
END TRY
BEGIN CATCH
<check things>
ROLLBACK
END CATCH
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 10:05 am
Sean Lange (2/6/2012)
SQLKnowItAll (2/6/2012)
Sean Lange (2/6/2012)
SQLKnowItAll (2/6/2012)
I don't get it... I believe this is syntactically wrong, but I don't use try/catch blocks much. It seems wrong that your commit is inside the try and then a rollback is inside the catch. You can't commit and then rollback, and your syntax commits no matter what. So the rollback will never be executed, i.e. it doesn't have a begin tran associated with it because the commit is inside the try and the rollback is outside.
BEGIN TRANBEGIN TRY
<some statements>
END TRY
BEGIN CATCH
<check things>
ROLLBACK
END CATCH
<check something else>
COMMIT
Jared the way you laid that out you have a commit AND a rollback when there is an error. It looked like the template was correct to me. You want the commit inside the TRY block (usually the last line) and the rollback inside the CATCH. That way if anything goes wrong during the transaction it will rollback in the catch. I make it a habit to always doublecheck @@trancount before a rollback to avoid transaction count mismatches.
Got it... That makes sense to me now. Basically, the rollback occurs if the commit fails?
Not JUST the commit but anything inside the try.
BEGIN TRY
BEGIN TRAN
<some statements>
select 1 / 0 --this will fail so we don't want to commit the transaction.
-- When this fails it will fall into the catch so we can rollback the transaction.
COMMIT
END TRY
BEGIN CATCH
<check things>
ROLLBACK
END CATCH
Ok, I think I get it now. Will read up more to get the full picture. Thanks Sean!
Jared
CE - Microsoft
February 6, 2012 at 2:36 pm
It kinda sounds like I do not need to embed TRANSACTIONs inside my stored procedures.
Here is what I am trying to accomplish:
1) In the (WinForms) application, start a transaction.
2) Execute each of 11 stored procedures. After each, update a progress bar. Detect if an error occurred.
3) Update the 'Last Synced' date in the local database.
4) Commit or Rollback the entire set of updates within the application.
Please note that each of the 11 stored procedures is accomplishing a synchronization between the local database and another database through a Linked Server (SQL Server both). This is why I thought I needed BEGIN TRANSACTION on each of the stored procedures.
Should I entertain removing the TRY/CATCH and TRANSACTION code from the 11 stored procedures and just let the application handle the Commit/Rollback? Is there a reason I should not do this? Will the application's Try/Catch handle an error on one of the stored procedures?
Please note that I am using Visual Studio 2010 and a NetTiers data layer to invoke each of the 11 stored procedures.
February 6, 2012 at 3:03 pm
You should probably read up on distributed transactions and MS DTC.
Here are a couple links to get started.
http://msdn.microsoft.com/en-us/library/ms188386.aspx
http://msdn.microsoft.com/en-us/library/ms190773.aspx
I can't emphasize enough the importance of testing this thoroughly in a test environment.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
February 6, 2012 at 3:17 pm
Yes, I have read both of those articles and many more associated Linked Servers and Distributed transactions, including the interaction with Firewalls. That is how I got as far as I have so far. And i have been testing as thoroughly as I can -- it is nerve racking knowing that an error could corrupt the Office database.
Anyway, the current question is about whether I even need the XACT_ABORT, TRY/CATCH or TRANSACTION processing in the individual stored procedures.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply