title: The transaction associated with the current connection has completed but has not been disposed. (edit #3)
body: <p>I have an application that allows users to create database tables and fields.
  We use SQL Management Object (SMO) (Microsoft.SqlServer.Smo) to perform the table changes.</p>
  
  <p>The code that updates an existing table does the following:</p>
  
  <pre><code>using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, new TimeSpan(0, 15, 0)))
  {
      using (SqlConnection sqlConnection = new SqlConnection(connectionSettings.ConnectionString))
      {
            ServerConnection serverConnection = new ServerConnection(sqlConnection);
            SMO.Server server = new SMO.Server(serverConnection);
            SMO.Database database = server.Databases[sqlConnection.Database];
            SMO.Table table = database.Tables[targetTableName];
  
            // perform table operations such as add new columns
      }
  </code></pre>
  
  <p>In the line that attempts to access the tables collection, we receive the error: 
  <strong>The transaction associated with the current connection has completed but has not been disposed.  The transaction must be disposed before the connection can be used to execute SQL statements.</strong> </p>
  
  <p>What is odd is this code works on 30 different machines in our environment and at other customer sites.  But at one customers site, the code generates this error.</p>
  
  <p>We added tracing which shows the System.Transactions.Transaction.Current.TransactionInformation.Status as Active right before the call to retrieve the table.
  A trace of System.Transactions.TransactionManager.MaximumTimeout shows 10 minutes.</p>
  
  <p>The customer duplicated the problem on two different machines.
  The customer runs an older version of our code and it works.  </p>
  
  <p>Between versions, we did update to use the latest SQL Server 2016 SMO dlls.  But again, this is working at other customer sites and using the old version of our code.</p>
  
  <p>Copying the SMO related DLLS into our BIN directory did not resolve the problem.
  Updating to the latest SMO related DLLS from Nuget did not resolve the problem.</p>
  
  <p>Question:<br>
  Any advice on how to determine the cause of this error?
  We are not sure what we are missing here.</p>
  
  <p>Edit:  Here is the stack trace.</p>
  
  <pre><code>at System.Data.SqlClient.SqlInternalConnectionTds.CheckEnlistedTransactionBinding()
  
  at System.Data.SqlClient.TdsParser.TdsExecuteSQLBatch(String text, Int32 timeout, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean sync, Boolean callerHasConnectionLock)
  at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task&amp; task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task&amp; task, Boolean asyncWrite)
  at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
  at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
  at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
  at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
  at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
  at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)
  at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query)
  at Microsoft.SqlServer.Management.Smo.ExecuteSql.Execute(StringCollection query)
  at Microsoft.SqlServer.Management.Smo.ExecuteSql.ExecuteWithResults(String query, Object con)
  at Microsoft.SqlServer.Management.Smo.PostProcessDatabaseInsideAttribs.InitializeRowCollection(DataProvider dp)
  at Microsoft.SqlServer.Management.Smo.PostProcessDatabaseInsideAttribs.GetColumnData(String name, Object data, DataProvider dp)
  at Microsoft.SqlServer.Management.Smo.DataProvider.ManipulateRowDataPostProcess()
  at Microsoft.SqlServer.Management.Smo.DataProvider.GetTable()
  at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillData(ResultType resultType, StringCollection sql, Object connectionInfo, StatementBuilder sb)
  at Microsoft.SqlServer.Management.Smo.SqlObjectBase.FillDataWithUseFailure(SqlEnumResult sqlresult, ResultType resultType)
  at Microsoft.SqlServer.Management.Smo.SqlObjectBase.BuildResult(EnumResult result)
  at Microsoft.SqlServer.Management.Smo.DatabaseLevel.GetData(EnumResult res)
  at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
  at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
  at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
  at Microsoft.SqlServer.Management.Smo.ExecutionManager.GetEnumeratorDataReader(Request req)
  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetInitDataReader(String[] fields, OrderBy[] orderby)
  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ImplInitialize(String[] fields, OrderBy[] orderby)
  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.Initialize(Boolean allProperties)
  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.OnPropertyMissing(String propname, Boolean useDefaultValue)
  at Microsoft.SqlServer.Management.Smo.PropertyCollection.RetrieveProperty(Int32 index, Boolean useDefaultOnMissingValue)
  at Microsoft.SqlServer.Management.Smo.PropertyCollection.GetPropertyObject(Int32 index)
  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetPropertyOptional(String propName)
  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.GetDbComparer(Boolean inServer)
  at Microsoft.SqlServer.Management.Smo.SqlSmoObject.InitializeStringComparer()
  at Microsoft.SqlServer.Management.Smo.AbstractCollectionBase.get_StringComparer()
  at Microsoft.SqlServer.Management.Smo.SchemaCollectionBase.InitInnerCollection()
  at Microsoft.SqlServer.Management.Smo.SmoCollectionBase.GetObjectByKey(ObjectKeyBase key)
  at Microsoft.SqlServer.Management.Smo.TableCollection.get_Item(String name)
  at UpdateForm(FormInfo updatedForm)
  </code></pre>
  
  <p>Edit: Reproduced in test app</p>
  
  <p>I have created a test application that also works in several test environments, but fails on the customer's machine.</p>
  
  <pre><code>using Microsoft.SqlServer.Management.Common;
  using System;
  using System.Data.SqlClient;
  using System.Reflection;
  using System.Transactions;
  using SMO = Microsoft.SqlServer.Management.Smo;
  
  namespace TestSMO
  {
      class Program
      {
          static void Main(string[] args)
          {
              Console.WriteLine($"{"-".PadLeft(25, '-')}");
              Console.WriteLine("Test Start");
  
              string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["DEFAULT"].ConnectionString;
  
              TraceTransactionStatus();
  
              TimeSpan timeSpanOption = new System.TimeSpan(0, 15, 0);
  
              TransactionOptions options = new TransactionOptions();
              options.IsolationLevel = IsolationLevel.ReadCommitted;
              options.Timeout = timeSpanOption;
  
              Console.WriteLine("  Start Transaction");
              using (System.Transactions.TransactionScope ts = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.Required, options))
              {
                  TraceTransactionStatus();
  
                  Console.WriteLine("  Using SQL Connection");
                  using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                  {
                      try
                      {
                          TraceTransactionStatus();
  
                          Console.WriteLine("  New SQL Connection");
                          ServerConnection serverConnection = new ServerConnection(sqlConnection);
                          Console.WriteLine("  New SQL Connection...done");
  
                          TraceTransactionStatus();
  
                          Console.WriteLine("  New SMO Server");
                          SMO.Server server = new SMO.Server(serverConnection);
                          Console.WriteLine("  New SMO Server...done");
  
                          TraceTransactionStatus();
  
                          Console.WriteLine("  Access database");
                          SMO.Database database = server.Databases[sqlConnection.Database];
                          Console.WriteLine("  Access database...done");
  
                          TraceTransactionStatus();
  
                          Console.WriteLine("  Access document table");
                          SMO.Table table = database.Tables["Document"];
                          Console.WriteLine("  Access document table...done");
  
                          TraceTransactionStatus();
                      }
                      catch(Exception e)
                      {
                          Console.WriteLine($"{"*".PadLeft(25, '*')}");
                          Console.WriteLine($"Exception: {e.Message}");
                          Console.WriteLine($"{"*".PadLeft(25, '*')}");
  
                          TraceTransactionStatus();
                      }
                  }
  
                  GetAssemblies();
              }
  
              Console.WriteLine("Test Complete");
              Console.WriteLine($"{"-".PadLeft(25, '-')}");
  
              Console.WriteLine("Press enter to continue");
              Console.ReadKey();
          }
  
          private static void TraceTransactionStatus()
          {
              if (System.Transactions.Transaction.Current != null)
              {
                  Console.WriteLine($"      Current Transaction Status: {Enum.GetName(typeof(System.Transactions.TransactionStatus), System.Transactions.Transaction.Current.TransactionInformation.Status)}");
                  Console.WriteLine($"      Current Transaction Distributed ID: {System.Transactions.Transaction.Current.TransactionInformation.DistributedIdentifier}");
                  Console.WriteLine($"      Current Transaction Local ID: {System.Transactions.Transaction.Current.TransactionInformation.LocalIdentifier}");
              }
              else
              {
                  Console.WriteLine($"      Current Transaction Status: None");
              }
          }
  
          public static void GetAssemblies()
          {
              Assembly[] asms = AppDomain.CurrentDomain.GetAssemblies();
              foreach(Assembly assembly in asms)
              {
                  Console.WriteLine($"{"-".PadLeft(25, '-')}");
                  Console.WriteLine($"Fullname:  {assembly.FullName}");
                  Console.WriteLine($"CodeBase:  {assembly.CodeBase}");
                  Console.WriteLine($"ImageRTV:  {assembly.ImageRuntimeVersion}");
                  Console.WriteLine($"Location:  {assembly.Location}");
              }
          }
      }
  }
  </code></pre>
  
  <p>Here is the output:</p>
  
  <pre><code>-------------------------
  Test Start
        Current Transaction Status: None
    Start Transaction
        Current Transaction Status: Active
        Current Transaction Distributed ID: 00000000-0000-0000-0000-000000000000
        Current Transaction Local ID: 7377e927-d977-414e-b0ca-69368bb27f58:1
    Using SQL Connection
        Current Transaction Status: Active
        Current Transaction Distributed ID: 00000000-0000-0000-0000-000000000000
        Current Transaction Local ID: 7377e927-d977-414e-b0ca-69368bb27f58:1
    New SQL Connection
    New SQL Connection...done
        Current Transaction Status: Active
        Current Transaction Distributed ID: 00000000-0000-0000-0000-000000000000
        Current Transaction Local ID: 7377e927-d977-414e-b0ca-69368bb27f58:1
    New SMO Server
    New SMO Server...done
        Current Transaction Status: Active
        Current Transaction Distributed ID: 00000000-0000-0000-0000-000000000000
        Current Transaction Local ID: 7377e927-d977-414e-b0ca-69368bb27f58:1
    Access database
    Access database...done
        Current Transaction Status: Active
        Current Transaction Distributed ID: 00000000-0000-0000-0000-000000000000
        Current Transaction Local ID: 7377e927-d977-414e-b0ca-69368bb27f58:1
    Access document table
  *************************
  Exception: The transaction associated with the current connection has completed
  but has not been disposed.  The transaction must be disposed before the connecti
  on can be used to execute SQL statements.
  *************************
        Current Transaction Status: Aborted
        Current Transaction Distributed ID: 00000000-0000-0000-0000-000000000000
        Current Transaction Local ID: 7377e927-d977-414e-b0ca-69368bb27f58:1
  -------------------------
  Fullname:  mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  CodeBase:  file:///C:/Windows/Microsoft.NET/Framework/v4.0.30319/mscorlib.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.NET\Framework\v4.0.30319\mscorlib.dll
  -------------------------
  Fullname:  TestSMO, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null
  CodeBase:  file:///E:/testAppDir/TestApp/TestApp/TestSMO.exe
  ImageRTV:  v4.0.30319
  Location:  E:\testAppDir\TestApp\TestApp\TestSMO.exe
  -------------------------
  Fullname:  System.Transactions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Transactions/v4.0_4.0.0.0__b77a5c561934e089/System.Transactions.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_32\System.Transactions\v4.0_4.0.0.0__b77a5c561934e089\System.Transactions.dll
  -------------------------
  Fullname:  System, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System/v4.0_4.0.0.0__b77a5c561934e089/System.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System\v4.0_4.0.0.0__b77a5c561934e089\System.dll
  -------------------------
  Fullname:  System.Configuration, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Configuration/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Configuration\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.Configuration.dll
  -------------------------
  Fullname:  System.Core, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Core/v4.0_4.0.0.0__b77a5c561934e089/System.Core.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Core\v4.0_4.0.0.0__b77a5c561934e089\System.Core.dll
  -------------------------
  Fullname:  System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.Data/v4.0_4.0.0.0__b77a5c561934e089/System.Data.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_32\System.Data\v4.0_4.0.0.0__b77a5c561934e089\System.Data.dll
  -------------------------
  Fullname:  Microsoft.SqlServer.ConnectionInfo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
  CodeBase:  file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.ConnectionInfo/13.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.ConnectionInfo.dll
  ImageRTV:  v2.0.50727
  Location:  C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.ConnectionInfo\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.ConnectionInfo.dll
  -------------------------
  Fullname:  Microsoft.SqlServer.Smo, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
  CodeBase:  file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.Smo/13.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Smo.dll
  ImageRTV:  v2.0.50727
  Location:  C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll
  -------------------------
  Fullname:  Microsoft.SqlServer.Management.Sdk.Sfc, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
  CodeBase:  file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.Management.Sdk.Sfc/13.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.Management.Sdk.Sfc.dll
  ImageRTV:  v2.0.50727
  Location:  C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.Sdk.Sfc\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.Sdk.Sfc.dll
  -------------------------
  Fullname:  Microsoft.SqlServer.SqlEnum, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
  CodeBase:  file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.SqlEnum/13.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.SqlEnum.dll
  ImageRTV:  v2.0.50727
  Location:  C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlEnum\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlEnum.dll
  -------------------------
  Fullname:  System.Xml, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Xml/v4.0_4.0.0.0__b77a5c561934e089/System.Xml.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Xml\v4.0_4.0.0.0__b77a5c561934e089\System.Xml.dll
  -------------------------
  Fullname:  System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_32/System.EnterpriseServices/v4.0_4.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_32\System.EnterpriseServices\v4.0_4.0.0.0__b03f5f7f11d50a3a\System.EnterpriseServices.dll
  -------------------------
  Fullname:  Microsoft.SqlServer.SqlClrProvider, Version=13.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91
  CodeBase:  file:///C:/Windows/assembly/GAC_MSIL/Microsoft.SqlServer.SqlClrProvider/13.0.0.0__89845dcd8080cc91/Microsoft.SqlServer.SqlClrProvider.dll
  ImageRTV:  v2.0.50727
  Location:  C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.SqlClrProvider\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.SqlClrProvider.dll
  -------------------------
  Fullname:  System.Numerics, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
  CodeBase:  file:///C:/Windows/Microsoft.Net/assembly/GAC_MSIL/System.Numerics/v4.0_4.0.0.0__b77a5c561934e089/System.Numerics.dll
  ImageRTV:  v4.0.30319
  Location:  C:\Windows\Microsoft.Net\assembly\GAC_MSIL\System.Numerics\v4.0_4.0.0.0__b77a5c561934e089\System.Numerics.dll
  Test Complete
  -------------------------
  Press enter to continue
  </code></pre>
  
  <p>Edit:  More research</p>
  
  <p>We created three versions of the test application.  Each compiled against a different version of the SQL Management Objects DLL: v12, v13, and v14.</p>
  
  <p>v12 works as expected.
  v13 throws the exception in the title.
  v14 throws a different exception:  <strong>Property DefaultSchema is not available for Database '[databaseName]'.
  This property may not exist for this object, or may not be retrievable due to in
  sufficient access rights.</strong></p>
  
  <p>The users are authenticating using Windows Authentication.  </p>
  
  <p>The user exists in SQL Server and is associated with the database.  Has a defaults schema as [dbo].</p>
  
  <p>The user also belongs to a domain group which is in SQL Server and is associated with the database.  The domain group also has a default schema of [dbo].</p>
  
  <p>I am not sure if this is a clue or a "red herring".</p>