0

I have to update an Oracle table from SQL Server 2008. When any row is inserted or updated in the SQL Server table, the same effect needs to be applied to the Oracle table using a trigger.

I am getting this error:

OLE DB provider "OraOLEDB.Oracle" for linked server "linked_testprod_custom" returned message "New transaction cannot enlist in the specified transaction coordinator. ".
Msg 7391, Level 16, State 2, Procedure uspSQLToOracle_UpdateDelegationTables, Line 32
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "linked_testprod_custom" was unable to begin a distributed transaction.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
taj
  • 185
  • 1
  • 1
  • 6
  • 2
    First of all check this SO thread regarding distributed transactions and make sure to go over the list in the answer. I think it's the exact same issue. http://stackoverflow.com/questions/6999906/distributed-transaction-error – Amir Pelled Aug 23 '16 at 13:31
  • 1
    You haven't asked a question. Also, the error message is pretty clear, the distributed transaction coordinator (MS-DTC) couldn't contact/engage with the Oracle transaction manager for some reason. – RBarryYoung Aug 23 '16 at 13:33
  • 1
    Also, please note: putting direct (synchronous) Linked Server dependencies in a trigger is a really bad idea. What if that other server is down? What if its just really slow? What if the network connection to it is down? Things like this are really better handled with asynchronous processes like Service Broker. – RBarryYoung Aug 23 '16 at 13:35
  • 1] Microsoft Distributed Transaction Coordinator service is running. 2] Also inside SSMS - Linked Server's property 'Enable Promotion of Distributed Transactions' is set to 'False' – taj Aug 24 '16 at 05:03

0 Answers0