Error "New transaction cannot enlist in the specified transaction coordinator."

Question

We have oracle database and we need to push data to Oracle system via linked server using .Net windows application. there are more than one table to insert and we use transaction Scope to call each method to save data.

 public void m1(){
      try{
         using(TranasctionScope scope = new TransactionScope()){
            M1();
            M2();
            M3();
            scope.complete();
         }
      }catch(exception ex){
           throw ex;
       }
    }

Above M1(),M2() and M3() is used to insert data to oracle,But here i get error,

The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "EBS_PREPROD" was unable to begin a distributed transaction. OLE DB provider "OraOLEDB.Oracle" for linked server "EBS_PREPROD" returned message "New transaction cannot enlist in the specified transaction coordinator. ".

Can anyone solve this???? Thanks

Wouldn't you be surprised if this issue could be solved without providing info about the code in M1,M2,M3 ? ;-) — collapsar, Jun 03 '16 at 10:49
@collapsar : But when i remove Transaction Scope it runs well , there are no sql errors. — Roshan, Jun 03 '16 at 12:24
You might want to do some research on DTC (Distributed Transaction Coordinator) but it's highly unlikely you'll get this working. Does the transaction have to include any data in SQL Server also? If not I strongly suggest you just connect straight to Oracle rather than going through SQL Server and linked SQL Servers. — Nick.Mc, Jun 03 '16 at 12:25
@Nick.McDermaid Yes , i have some SPs that update sql server table inside the transaction. — Roshan, Jun 03 '16 at 12:30
So what you have is: Data changes in SQL and Oracle that need to be part of the same transaction, data changes in Oracle are occurring on Oracle through linked servers and you want to orchestrate it through an programming language (C# .Net- I'm just guessing). I'd be very suprised if you get this to work. Some system simplification might be in order here. In your original question you say M1,M2,M3 change data in Oracle. Which one of these changes the data in SQL that needs to be part of the transaction? And are M1,M2,M3 run against SQL Server only? — Nick.Mc, Jun 05 '16 at 00:44
@Nick.McDermaid think M1() and M2() change the data in oracle and M3() change the data in SQL why it can't do? — Roshan, Jun 05 '16 at 09:48
Do M1 and M2 connect directly to Oracle to change data or do they connect to SQL Server and change data in oracle through linked servers? I'm really not familiar with `TransactionScope()` but assuming it is starting and committing transactions on both SQL Server and Oracle through linked servers, it's just too complicated to work. Anyway maybe this will help: http://dba.stackexchange.com/questions/60858/how-can-i-enable-distributed-transactions-for-a-linked-server — Nick.Mc, Jun 05 '16 at 10:06
@Nick.McDermaid yes they use linked server to connect to oracle , thank you i will refer provided link. — Roshan, Jun 05 '16 at 10:18
i think this might help https://stackoverflow.com/questions/6999906/distributed-transaction-error — yenfang chang, Dec 28 '17 at 02:38

Error "New transaction cannot enlist in the specified transaction coordinator."

0 Answers0