Awaneesh Pandey

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




Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

VMazur

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






Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

Awaneesh Pandey

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





Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

VMazur

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.






Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

Awaneesh Pandey

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





Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

VMazur

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.






Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

Awaneesh Pandey

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.





Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

VMazur

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.






Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

Caddre

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/






Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

Awaneesh Pandey

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





Re: .NET Framework Data Access and Storage Error using System.Transaction when DB Server is a different machine other than application server machine.

VMazur

Could you post current code