none
Error using System.Transaction when DB Server is a different machine other than application server machine. RRS feed

  • Question

  • Hi,

     

    I'm facing a very strange problem while accessing my database which is deployed on a different win 2003 machine other than the web server application machine (having win 2003 OS). The error is as follows:

     

    The transaction has already been implicitly or explicitly committed or aborted.
    SOURCE: System.Transactions
    TARGETSITE: Void ProxyException(System.Runtime.InteropServices.COMException)
    STACKTRACE:    at System.Transactions.Oletx.OletxTransactionManager.ProxyException(COMException comException)
       at System.Transactions.TransactionInterop.GetExportCookie(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()

     

    I know that this error is something to do with the Transaction but it would be of great help if some one can help me to get rid of this error.

     

    Thanks,

    -AP

    Thursday, October 11, 2007 7:43 PM

All replies

  • Are you using System.Transactions in your code? It looks like your code tried to commit or rollback transaction, which was already committed or rolled back. Can you post the code that shows how your transaction works?

    Friday, October 12, 2007 10:38 AM
    Moderator
  • yes, i m using System.Transaction.

     using (TransactionScope tranScope = new TransactionScope())
                {
                    using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
                    {
                        conn.Open();
    }

    I'm just copying a small snippet of the code i m using. The error occurs when we try to connect i.e. Conn.Open.

    Please help me to get through with this issue.

    Thanks,
    -Awaneesh
    Friday, October 12, 2007 1:26 PM
  • Why do you use System.Transaction to work with local transaction? Try to create local transaction using SqlTransaction class. In this case you would need to commit or rollback transaction explicitly.

     

     

    Monday, October 15, 2007 10:04 AM
    Moderator
  • Hi VMazur,

    Thanks for youe response.

    I'm using SQL Server 2k as my database so using SQT(SqlTransaction) over TX (System.Transaction) does not make any difference becuase in Sql 2k, the transaction will be managed by MSDTC even if you have ONE single database involved & for Sql2k5, the transaction is managed by LTM(Lightweight transaction manager) until the second SqlConnection connected to a different database comes into picture. I know that we should use SQT for single database & TX for multiple possibly. But I think that TX is recommended for SQL Server 2k5.

    And above all using Tx benefits in code maintainablity, less code and many other benefits which all can't be listed in comparison to using SQT in this post. Also, even if i will use SQT as you are suggesting it will logically invoke MSDTC for maintaing the transaction & we might get the same error. Please advise me if its not the case!!!

    Anyways, i have overcome the above mentioned error using some MSDTC settings after googling a lot. But after that I start getting a different error which is not very regular & occurs one in every 30 transaction. The error is..

     Import of MSDTC transaction failed: Result Code = 0x8004d00e.
    SOURCE: .Net SqlClient Data Provider
    TARGETSITE: Void OnError(System.Data.SqlClient.SqlException, Boolean)
    STACKTRACE:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)

    Again this error occurs while opeing the connection when i use the TX. I m really surprised after watching its behavior becuase of its uneven nature of occurence.

    Any idea, how to actually get rid of this error. May be it will help in future to over come this error.

    Thanks,
    -AP
    Monday, October 15, 2007 11:24 AM
  • By using local transaction you should avoid using MSDTC. I do not see any reason why SQT would use MSDTC and I do not see any real benefit of using System.Transaction unless there is a real case of participating in distributed transaction.

     

    Monday, October 15, 2007 5:44 PM
    Moderator
  • Our DB Server & application server are on different machine, so in that case if MSDTC is not used then how the transaction will take place?

    And the reason behind usage of Trsnacstion to avoid the any data loss due to any unexpected error when the site is going thru with heavy online transactions.
    Tuesday, October 16, 2007 12:04 PM
  • But you do not have distributed transactions even if your application server is on a different PC than database server. From what I see in your code it is always local database transaction. Distributed transaction is when you update data on one database server and then update data on another database server or some other source that could participate in distributed data manipulation. In a case when one database server is involved it is always local transaction, since all the actions with the data are local to that server even if data comes from another server. I believe you create additional workload in your case for no benefit at all.

     

    Wednesday, October 17, 2007 10:24 AM
    Moderator
  • This is a known issue with work arround solutions.  That said System.Transaction transactions are distributed transactions to SQL Server 2000 and below because they are none atomic transactions meaning more than one transaction is passed in without something to covert the transactions in a TransactionScope to a unit of work.  Check the links below for solutions to your error.  Hope this helps.

     

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=124293&SiteId=1


    http://parmeshwar.blogsome.com/2005/10/05/com-exceptionthe-transaction-has-already-been-implicitly-or-explicitly-committed-or-aborted/

     

    Wednesday, October 17, 2007 4:07 PM
  • VMazur,

    As you suggested to use SqlTransaction instead of System.Transaction but still i m getting the same error which i found using transaction "Import of MSDTC transaction failed: Result Code = 0x8004d00e."

    So, i was correct in my perception that whether you use System.Transaction or SqlTransaction if you have your database server other than the application server then MSDTC comes into picture for all kind of query execution started under a transaction.

    I hope now you do agree with me as i have done it practically.

    The very funny thing about this error is that it occurs occasionally & not all the time. I'm able to determine this behavior becoz of my error log file entires. And failed to understand this behavior becoz if it happens then should happen to each and every execution but not occasionally.


    -Awaneesh
    Saturday, October 27, 2007 7:46 PM
  • Could you post current code?

    Monday, October 29, 2007 10:57 AM
    Moderator