TransactionScope question

Valentin_Valve posted on Wednesday, May 28, 2008

Hello.
I wrote a stored procedure that contains try\catch blocks (raiserror is thrown from the catch block). When I try to call this proc inside a transaction scope I end up with 'Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0'.
I get this message only when sproc db transaction is rolled back in catch block and original error is rethrown by 'raiserror' statement. What am I doing wrong?
Please help.

~Valentin Vasiliev (Valve).

RockfordLhotka replied on Wednesday, May 28, 2008

I thought that stored procedure transactions and TransactionScope were incompatible - you had to use one or the other?

Valentin_Valve replied on Wednesday, May 28, 2008

Hi Rocky,
The reason why I need this is to create a sproc which would 'know' if it is being called from TransactionScope or independently. I think I managed to do this by checking the current trancount. If it is greater than zero, then we're called from withing the transaction scope.
So the code would look like:
create proc MyProc
as
if @@trancount>1 -- call from TransactionScope
  save tran -- saving tran
else -- called independently
  begin tran
bla
bla

commit or rollback.

Thanks,

Valentin Vasiliev (Valve)