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.
The code that updates an existing table does the following:
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
}
In the line that attempts to access the tables collection, we receive the error: 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.
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.
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.
The customer duplicated the problem on two different machines. The customer runs an older version of our code and it works.
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.
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.
Question:
Any advice on how to determine the cause of this error?
We are not sure what we are missing here.
Edit: Here is the stack trace.
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& 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& 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)
Edit: Reproduced in test app
I have created a test application that also works in several test environments, but fails on the customer's machine.
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}");
}
}
}
}
Here is the output:
-------------------------
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
Edit: More research
We created three versions of the test application. Each compiled against a different version of the SQL Management Objects DLL: v12, v13, and v14.
v12 works as expected. v13 throws the exception in the title. v14 throws a different exception: 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.
The users are authenticating using Windows Authentication.
The user exists in SQL Server and is associated with the database. Has a defaults schema as [dbo].
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].
I am not sure if this is a clue or a "red herring".
The solution (workaround) to our problem was to configure MSDTC (Microsoft Distributed Transaction Coordinator) on both the client machine and the SQL Server machine.
Our guess is something changed between Microsoft.SQLServer.SMO version 12 and Microsoft.SQLServer.SMO version 13 and 14 which elevated the transaction to a distributed transaction. Version 12 didn't required MSDTC.
Note: Code also does not fail if you do not use transactions, or if you use a transaction scope with the suppress option.