TRY/CATCH with Open Transaction

Hi,

I have recently starting making use of the TRY?CATCH statement in SQL Server 2005 - simple example in attached code.

Now I have run into a small problem... if the executed SP uses a transaction, and if the error occurs within that transaction, then after the catch block I end up with a "Uncommittable transaction is detected at the end of the batch. The transaction is rolled back." error.

So I thought I'd be clever and check for uncommitted transactions and close them, as per the second example in the attached code.

But then I got this error "The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.".

So it appears that even though the transactions are still open, because I am outside the scope of the begin trans I am unable to close it. I assume I could add a try/catch block within the transaction and then roll it back. But to me that defeats the purpose of using the try/catch - I purposely placed my try/catch at the point where I would like to handle the errors - and yet if there is still an open transaction my try/catch doesn't work unless I then put a try/catch inside every transaction that is called within my outer try/catch block (and there are a lot of these... I have simplified by showing a single SP... in fact there are a lot of them called in this context).

Have I misunderstood how to use this? Is there a way to accomplish what I am trying to accomplish?

Cheers,

Dale
-- Example 1
begin try
  exec [dbo].[Daily_Maintenance]
end try
begin catch
  exec [dbo].[Email_Send]
end catch

-- Example 2
declare @TransCount int

begin try
  exec [dbo].[Daily_Maintenance]
end try
begin catch
  select @TransCount = @@trancount
  while @TransCount > 0
  begin
    rollback tran
    set @TransCount = @TransCount - 1
  end
  exec [dbo].[Email_Send]
end catch

Open in new window

LVL 21
Dale BurrellDirectorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
If you are going to use either ROLLBACK tran or COMMIT tran, then you need to have appropriate BEGIN tran statements. Else you would be receiving errors as you mentioned above.
And the modified code below:
-- Example 1
begin try
BEGIN TRAN
  exec [dbo].[Daily_Maintenance]
END TRAN
end try
begin catch
ROLLBACK TRAN
  exec [dbo].[Email_Send]
end catch

-- Example 2
declare @TransCount int

begin try
begin tran
  exec [dbo].[Daily_Maintenance]
  commit tran
end try
begin catch
  select @TransCount = @@trancount
  while @TransCount > 0
  begin
    rollback tran
    set @TransCount = @TransCount - 1
  end
  exec [dbo].[Email_Send]
end catch

Open in new window

0
Dale BurrellDirectorAuthor Commented:
OK, maybe I wasn't clear enough - in my SP's that I am calling from within the try/catch block I have many many transactions... and they can't be moved outside the try/catch block. That code I posted my extremely simplified code to illustrate my question.

As I said maybe I've misunderstood how best to use it, but my previous experience with try/catch blocks in code I use the try/catch block at a high level to catch any errors that might occur with that code. However from what you are saying there is no way I can recover from an error within a transaction inside the SP which is inside the try/catch block - in which case it defeats the purpose of me having a try/catch block at all...
0
lofCommented:
Hi

You've got at least three options. First one you simply will have one try/catch block for each transaction (probably named). This option is the best if you have multiple steps in your stored procedure and you want to carry on with next one if one fails. Another option would be to use save points with SAVE TRAN tansaction_name. That will work well if you have steps in your stored procedure and if one fails you want to simply commit whatever was done before, roll back current and quit without trying the rest.

Yet another option would be to use named transactions and query sys.dm_tran_active_transactions to see if your transaction is still open but that will slow you down.

Hope it helps
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Dale BurrellDirectorAuthor Commented:
ok - but at the end of the day it seems to have to visit each inner transaction because by the time I am back at in the scope of my outer try/catch block as per my example, I'm not allowed to rollback or commit an inner transaction? Or are you saying I can if I name them?

And actually even if I can commit/rollback a named transaction from my outer try/catch block, that still requires inner information to seep out to this level, so I might as well do it where it belongs.

Seems a pain, but I guess my mistake was assuming that I could use this try/catch in the same way that I would use one in C#.
0
lofCommented:
You usually do thing very differently in T-SQL and C#, or at least you should.
In my work I hardly ever have to have nested transactions and if so it is not more then 2 levels. If it is more than that I'd started thinking about changing the approach.

If you want to use one general try/catch block maybe one general transaction will do just fine? If something goes wrong then roll everything back?

and as for taking C# approaches to T-SQL world: it will usually work, but it may be painful and most likely it will be slow. In C# you use Object Oriented or Flow Based programming while databases work on sets and therefore Set Based programming is best.
0
Dale BurrellDirectorAuthor Commented:
Hi Lof - yeah thanks for that - I am in fact very familiar and proficient with both C# and T-SQL and their associated approaches. This is just the first time I've got around to using try/catch in T-SQL so my only reference was C#.

We have a modularised set of SP's because we may need to access the same functionality from many different access points, and hence each SP may have its own transaction to allow it to protect its own operation, however in some cases that leads to nested transactions. Which is certainly not a problem in its own right.

It seems strange to me that at the point where I 'catch' an error I am then not able to close all open transactions to allow me to continue - practically it means that should there be a transaction deeper inside any code I run within a try/catch block, my try try/catch block becomes useless because the uncommitted transaction error stops any further attempt at recovery.

But hey ho... its still better than no try/catch - I just have to go through and try/catch all my transactions.
0
chapmandewCommented:
to roll backup ALL transactions in a given session:

Rollback tran

There is no reason to loop through, because a rollback "undoes" everything.  Do not do this:
  while @TransCount > 0
  begin
    rollback tran
    set @TransCount = @TransCount - 1
  end

It will only ever loop once.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale BurrellDirectorAuthor Commented:
@chapmandew - brilliant - you spotted the flaw in my plan where no one else did! This code does work...
begin try
  exec [dbo].[Daily_Maintenance]
end try
begin catch
  if @@trancount > 0 rollback tran
  exec [dbo].[Email_Send]
end catch

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.