通过本地Web服务调用存储过程时出现问题. “该操作对于交易状态无效." [英] Problem calling stored procedure through local webservice. "The operation is not valid for the state of the transaction."

查看：118
本文介绍了通过本地Web服务调用存储过程时出现问题. “该操作对于交易状态无效."的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我们有一个.NET应用程序，该应用程序包含3个部分:


  • 前端
  • Web服务(调用存储的proc以读取和访问数据库/从数据库中访问)
  • SQL Express数据库

    • Hi, we''ve got an .NET application that consists of 3 parts:


    • Front-end
    • Webservices(calls stored procs in to read and wite in/out of the database)
    • SQL Express Database

      • System.Transactions.TransactionException was caught
  Message="The operation is not valid for the state of the transaction."
  Source="System.Transactions"
  StackTrace:
       at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx,
           IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(
           IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
           DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
       at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
       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 AVAF.Server.DataAccess.SqlStoredProcedure.ExecuteDataSet() in 
           E:\TCS\AVAF.Server\DataAccess\SqlStoredProcedure.cs:line 191
  InnerException: System.TimeoutException
       Message="Transaction Timeout"
       InnerException: 


System.Transactions.TransactionException was unhandled
  Message="The operation is not valid for the state of the transaction."
  Source="System.Transactions"
  StackTrace:
       at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx,
           IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(
           IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
           DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
  InnerException: System.TimeoutException
       Message="Transaction Timeout"
       InnerException: 




System.Transactions.TransactionException was unhandled
  Message="The operation is not valid for the state of the transaction."
  Source="System.Transactions"
  StackTrace:
       at System.Transactions.TransactionState.EnlistPromotableSinglePhase(InternalTransaction tx,
           IPromotableSinglePhaseNotification promotableSinglePhaseNotification, Transaction atomicTransaction)
       at System.Transactions.Transaction.EnlistPromotableSinglePhase(
           IPromotableSinglePhaseNotification promotableSinglePhaseNotification)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
           DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
  InnerException: 


System.Transactions.TransactionManagerCommunicationException was unhandled
  Message="Network access for Distributed Transaction Manager (MSDTC) has been disabled. Please enable DTC for 
           network access in the security configuration for MSDTC using the Component Services Administrative tool."
  Source="System.Transactions"
  StackTrace:
       at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)
       at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(
           Byte[] propagationToken)
       at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
       at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
       at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
       at System.Transactions.Transaction.Promote()
       at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
       at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
       at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction,
           Byte[] whereAbouts)
       at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
       at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
       at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
       at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
       at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection,
           DbConnectionFactory connectionFactory)
       at System.Data.SqlClient.SqlConnection.Open()
  InnerException: System.Runtime.InteropServices.COMException
       Message="The transaction manager has disabled its support for remote/network 
                 transactions. (Exception from HRESULT: 0x8004D024)"
       Source="System.Transactions"
       ErrorCode=-2147168220
       StackTrace:
            at System.Transactions.Oletx.IDtcProxyShimFactory.ReceiveTransaction(UInt32 propgationTokenSize,
                Byte[] propgationToken, IntPtr managedIdentifier, Guid& transactionIdentifier,
                OletxTransactionIsolationLevel& isolationLevel, ITransactionShim& transactionShim)
            at System.Transactions.TransactionInterop.GetOletxTransactionFromTransmitterPropigationToken(
               Byte[] propagationToken)
       InnerException:



      它是这样的:


      • 登录时，应用程序将连接到Web服务，并检查用户是否在数据库中并检索配置文件. (这意味着第一个存储过程调用有效)
      • 然后有一系列存储的proc调用，用于填充搜索功能中使用的组合框.这一切都成功了.
      • 完成搜索并选择并打开一个项目后，将进行更多调用以检索该项目信息.出于某种原因，这就是我遇到的问题.第一个调用成功，但是第二个失败.
        *请注意，如果我交换2，它们都会失败.
        使用立即窗口并手动打开连接(在此阶段已关闭)时，它将立即失败.
        超时设置为2分钟，但是只要拨打电话，我就会立即收到错误消息.



        • It happens like this:


        • when you log in, the application connects to the webservice and checks if the user is on the database and retrieves profile. (which means that the first Stored procedure call works)
        • Then there is a series of Stored proc calls to populate comboboxes used in a search function. This all succeeds.
        • When the search is done and an item is selected and opened, more calls are made to retrieve th item information. and for some reason this is where i get the problem. the first call succeeds but the second one fails.
          *note that if I swap the 2, both of them fail.
          When using the immediate window and mannually open the connection (which at this stage is closed) it fails immediately.
          Time-out is set to 2 minutes but i get the error instantly as soon as the call is made.

          • 推荐答案

          您的本地IIS服务器是否有机会启用了保持活动"功能?问题看起来更像是SQL Server或WCF服务拒绝连接，结果，随后的事务失败.
          Does your local IIS Server have the "Keep Alive" enabled by any chance? The problem looks more like the SQL Server or WCF Service is rejecting the connection and as a result, subsequent transactions are failing.


          这就是我的处理方法，它可能会为您提供帮助弄清楚.

          This is how I''ll do it, it may help you to figure it out.

          SqlConnection lSQLConn = null;
SqlCommand lSQLCmd = new SqlCommand();
//Declare a DataAdapter and a DataSet
SqlDataAdapter lDA = new SqlDataAdapter();
DataSet lDS = new DataSet();
 
//...Execution section
 
// create and open a connection object
lSQLConn = new SqlConnection(connStr);
lSQLConn.Open();
//The CommandType must be StoredProcedure if we are using an ExecuteScalar
lSQLCmd.CommandType = CommandType.StoredProcedure;
lSQLCmd.CommandText = "sp_YourSPName"; 
lSQLCmd.Parameters.Add(new SqlParameter("@Parm1", aParm1));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm2", aParm2));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm3", aParm3));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm4", aParm4));
 
lSQLCmd.Connection = lSQLConn;
//Fill the DataAdapter with a SelectCommand
lDA.SelectCommand = lSQLCmd;
lDA.Fill(lDS);



          有关更多信息:
          从WCF服务方法C#执行存储过程



          For more Info:
          Executing a Stored Procedure from a WCF Service Method C#


          这篇关于通过本地Web服务调用存储过程时出现问题. “该操作对于交易状态无效."的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发语言最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆