Problem calling stored procedure through local webservice. "The operation is not valid for the state of the transaction."

Question

0.00/5 (No votes)

See more:

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

The webservices and database sits on a server but, for debugging, the webservices are also on my local IIS.

I'm experiencing a problem on my workstation ONLY when using my local IIS webservices. It is the exact same services and structure as on the server and fellow developers.

this is the exception I get:

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:

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.

Any help/ suggestions?
Thank you

Posted 30-Nov-09 18:50pm

diePopster

Updated 1-Dec-09 7:19am

Sean Ewington

v8

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

raelango · Answer 1 · 2009-12-02T22:37:00

Solution 1

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.

Posted 2-Dec-09 22:37pm

raelango

gmtzgtz · Answer 2 · 2011-10-19T05:20:00

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);

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