TransactionScope questionOld forum URL: forums.lhotka.net/forums/t/4905.aspx
Valentin_Valve posted on Wednesday, May 28, 2008Hello.
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?
~Valentin Vasiliev (Valve).
RockfordLhotka replied on Wednesday, May 28, 2008I thought that stored procedure transactions and TransactionScope were incompatible - you had to use one or the other?
Valentin_Valve replied on Wednesday, May 28, 2008Hi 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
if @@trancount>1 -- call from TransactionScope
save tran -- saving tran
else -- called independently
commit or rollback.
Valentin Vasiliev (Valve)
Copyright (c) Marimer LLC