Stored procedure call in method with OperationBehavior attribute: problems with transactions

Question

I'm using ADO.Net's ExecuteNonQuery to call a stored procedure, works like a charm stand-alone but when implementing it where it should be called I'm running into problems concerning transactions.

For example

System.Data.SqlClient.SqlException: Transaction count after EXECUTE indicates a 
mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0.

and also a timeout right after that.

I've just found out the method which calls the stored procedure is marked with the following WCF attribute:

[OperationBehavior(TransactionScopeRequired = true, TransactionAutoComplete = true)]

How will this influence the call my stored procedure? How can I tell .Net to execute the stored procedure outside this transaction?

The stored procedure contains insert statements and also a transaction, but removing them doesn't change the behavior...

Answer 1

It's hard to know exactly what is going on without seeing the stored procedure. I suspect there is some error in the stored procedure and the transaction handling code is not being executed properly.

This SO questions seems to apply to your situation: http://stackoverflow.com/questions/2701934/transactionscope-and-transactions

To run your stored procedure outside of a transaction you would wrap your code in a TransactionScope that suppresses the ambient transaction:

using(TransactionScope scope = new TransactionScope(TransactionScopeOption.Suppress)) 
{
     // call SP
} 

As a best practice, I would recommend to not mix .NET transactions and SQL transactions in stored procedures.