C# SQL Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.

Question

I'm getting this error (Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.) when trying to run a stored procedure from C# on a SQL Server 2005 database. I'm not actively/purposefully using transactions or anything, which is what makes this error weird. I can run the stored procedure from management studio and it works fine. Other stored procedures also work from C#, it just seems to be this one with issues. The error returns instantly, so it can't be a timeout issue. The code is along the lines of:

  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's really got me stumped. Thanks for any help

Answer 1

From the code it seems that you are utilizing an already opened connection. May be there's a transaction pending previously on the same connection.

Answer 2

It sounds like there is a TransactionScope somewhere that is unhappy. The _DBManager.CheckConnection and _DBManager.SqlConnection sounds like you are keeping a SqlConnection hanging around, which I expect will contribute to this.

To be honest, in most common cases you are better off just using the inbuilt connection pooling, and using your connections locally - i.e.

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

Here you get a clean SqlConnection, which will be mapped to an unmanaged connection via the pool, i.e. it doesn't create an actual connection each time (but will do a logical reset to clean it up).

This also allows much more flexible use from multiple threads. Using a static connection in a web app, for example, would be horrendous for blocking.