gpt4 book ai didi

c# - 与当前连接关联的事务已完成但尚未处理。 (编辑#3)

转载 作者:行者123 更新时间:2023-12-04 13:46:26 27 4
gpt4 key购买 nike

我有一个允许用户创建数据库表和字段的应用程序。
我们使用 SQL 管理对象 (SMO) (Microsoft.SqlServer.Smo) 来执行表更改。

更新现有表的代码执行以下操作:

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
}

在尝试访问表集合的行中,我们收到错误:
与当前连接关联的事务已完成但尚未处理。必须先处理事务,然后才能使用连接执行 SQL 语句。

奇怪的是,这段代码适用于我们环境和其他客户站点中的 30 台不同的机器。但是在一个客户站点,代码会生成此错误。

我们添加了在调用检索表之前将 System.Transactions.Transaction.Current.TransactionInformation.Status 显示为 Active 的跟踪。
System.Transactions.TransactionManager.MaximumTimeout 的跟踪显示 10 分钟。

客户在两台不同的机器上重复了这个问题。
客户运行我们代码的旧版本并且它可以工作。

在版本之间,我们确实更新为使用最新的 SQL Server 2016 SMO dll。但是,这同样适用于其他客户站点并使用我们的旧版本代码。

将 SMO 相关的 DLLS 复制到我们的 BIN 目录中并没有解决问题。
从 Nuget 更新到最新的 SMO 相关 DLLS 没有解决问题。

题:
有关如何确定此错误原因的任何建议?
我们不确定我们在这里缺少什么。

编辑:这是堆栈跟踪。
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)

编辑:在测试应用程序中转载

我创建了一个测试应用程序,它也可以在多个测试环境中运行,但在客户的机器上失败了。
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}");
}
}
}
}

这是输出:
-------------------------
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

编辑:更多研究

我们创建了三个版本的测试应用程序。每个都针对不同版本的 SQL 管理对象 DLL 进行编译:v12、v13 和 v14。

v12 按预期工作。
v13 在标题中抛出异常。
v14 抛出一个不同的异常: 属性 DefaultSchema 不可用于数据库“[databaseName]”。
此对象可能不存在此属性,或者可能由于在
足够的访问权限。


用户正在使用 Windows 身份验证进行身份验证。

用户存在于 SQL Server 中并与数据库相关联。具有 [dbo] 的默认架构。

该用户还属于 SQL Server 中与数据库关联的域组。域组还有一个默认架构 [dbo]。

我不确定这是线索还是“红鲱鱼”。

最佳答案

我们问题的解决方案(变通方法)是在客户端计算机和 SQL Server 计算机上配置 MSDTC(Microsoft 分布式事务协调器)。

  • 确保分布式事务协调器在服务中运行。
  • 在每台机器上,打开组件服务。展开我的电脑、分发事务协调器、本地 DTC。右键单击并查看属性。在安全选项卡上:启用网络 DTC,允许远程客户端、所有入站、所有出站,然后选择一种身份验证方法。

  • 我们的猜测是 Microsoft.SQLServer.SMO 版本 12 与 Microsoft.SQLServer.SMO 版本 13 和 14 之间发生了一些变化,这将事务提升为分布式事务。版本 12 不需要 MSDTC。

    注意:如果您不使用事务,或者使用带有抑制选项的事务范围,代码也不会失败。

    关于c# - 与当前连接关联的事务已完成但尚未处理。 (编辑#3),我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/46610176/

    27 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com