4

I have a web server and two DB servers on two different networks, Db1 and Db2(Remote Database).

  • DB1: SQL Server 2008 R2, operating system: Windows Server 2003 SP2
  • DB2: SQL Server 2000, operating system: Windows Server 2003 R2
  • Web server: Windows Server 2003 R2

I want to insert two different records in these databases and I'm using a TransactionScope.

using (TransactionScope tscope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    //open connection db 1
    //insert into db1

    //open connection db2 -- the problem is here
    //insert into db2

    tscope.Complete();
}

When I trace the source code, inserting in the first databse is done successfully but when the second connection wants to be opened I encounter below error.

Error:

The transaction has already been implicitly or explicitly committed or aborted.

I have configured MSDTC, Distributrd transaction coordinator and every thing is ok. I can have distributed transaction in My DB server and I have no problem. What's wrong with TransactionScope? Please help me.

the first connection is LINQ TO SQL:

DataClassesDataContext Dac = new DataClassesDataContext();
Dac.Connection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionCs"].ConnectionString;
tbl_KargahDistribution Workshop = new tbl_KargahDistribution();
Workshop.WpCode = Bodu.WpRealCode;
Workshop.State = Bodu.BduState;
Workshop.City = Bodu.BduCity;
Workshop.Town = Bodu.BduTown;
Workshop.SubSystem = Bodu.BduSubSystem;
Dac.tbl_KargahDistributions.InsertOnSubmit(Workshop);
Dac.SubmitChanges();

the second connection is:

Queries Qu = new Queries();
SqlCon = new SqlConnection(BoBaz.BazConnectionString);
SqlCon.Open();
string sq = Qu.InsertWorkshopBaseInfo();
SqlCom = new SqlCommand(sq, SqlCon);
abatishchev
  • 98,240
  • 88
  • 296
  • 433
Raymond Morphy
  • 2,464
  • 9
  • 51
  • 93
  • 2
    Can you include the details on how your connection is opened? – Kane Nov 02 '11 at 12:15
  • Have you tried play around `SqlConnection.BeginTransaction()`, `Transaction.Current.EnlistVolatile()`, etc.? – abatishchev Nov 02 '11 at 13:18
  • I just used enlist=false; in second connection string an I didn't get any error message an the problem solved. But I think when I set enlist=false; my second connection won't be a part of transaction so I didn't used it. – Raymond Morphy Nov 02 '11 at 13:22

3 Answers3

1
Dac.SubmitChanges();

causes

The transaction has already been implicitly or explicitly committed or aborted.

See How to use transactions with a datacontext

Community
  • 1
  • 1
abatishchev
  • 98,240
  • 88
  • 296
  • 433
0

Do you only notice this when you are stepping through the code?

It is possible that the transaction is timing out and automatically rolling back because the time taken for you to step through the code exceeds the TransactionScope's timeout limit. I think the default timeout value is 60 seconds.

Andy Hames
  • 619
  • 1
  • 4
  • 19
0

I have configured MSDTC, Distributrd transaction coordinator and every thing is ok. I can have distributed transaction in My DB server and I have no problem.

That may be true, but with multi-machine DTC it is best to double-check. This error often has it's root in network issues. The DB may not be able to communicate with the web server (DNS or IP change issues) or the client's own firewall may be blocking DTC (check Windows Firewall setings on the web server).

Marc L.
  • 3,296
  • 1
  • 32
  • 42