asked on

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

Raja Jegan R

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

Dale Burrell

ASKER

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...

lof

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

Dale Burrell

ASKER

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#.

lof

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.

Dale Burrell

ASKER

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.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Burrell

ASKER

@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