C # SQL Distributed transaction completed. Either commit this session to a new transaction, or to transaction NULL

Question

I get this error (distributed transaction completed. End this session in a new transaction or transaction NULL.) When I try to start a stored procedure with C # in a SQL Server 2005 database. I am not actively / purposefully using transactions or anything else that does this mistake is strange. I can run the stored procedure from the management studio, and it works great. Other stored procedures also work with C #, it looks like this is a problem with the problems. The error returns instantly, so it cannot be a timeout problem. The code corresponds to the lines:

SqlCommand cmd = null; try { // Make sure we are connected to the database if (_DBManager.CheckConnection()) { cmd = new SqlCommand(); lock (_DBManager.SqlConnection) { cmd.CommandText = "storedproc"; cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.Connection = _DBManager.SqlConnection; cmd.Parameters.AddWithValue("@param", value); int affRows = cmd.ExecuteNonQuery(); ... } } else { ... } } catch (Exception ex) { ... } 

It really puzzled me. Thanks for any help

Answer 1

There seems to be a TransactionScope somewhere that is unfortunate. _DBManager.CheckConnection and _DBManager.SqlConnection sound like you are holding an SqlConnection around you, which I hope will help.

Honestly, in most common cases, you'd better use only the built-in connection pool and using your local connections - for example,

 using(var conn = new SqlConnection(...)) { // or a factory method // use it here only } 

Here you get a pure SqlConnection that will be mapped to an unmanaged connection through the pool, i.e. it does not create the actual connection every time (but will do a logical reset to clear it).

It also allows for much more flexible use from multiple threads. Using a static connection in a web application, for example, would be terrible for blocking.

Answer 2

From the code, it seems that you are using an already open connection. There may be a transaction waiting earlier for the same connection.