Msg 7391 in trigger when trying to insert a row in Oracle 10g

• sgambale

  SSCommitted

  Points: 1709

  More actions

  January 27, 2011 at 1:22 pm

  #144451

  I setup a linked server from sql server 2005 to Oracle 10.2.0.4.

  I can run queries and insert a row into a table in oracle if done from SSMS in a query session.

  However, the same insert fails if done from a trigger:

  Msg 7391, Level 16, State 2, Procedure TR_PMATT_PRJ_ID_INFORMATION, Line 15

  The operation could not be performed because OLE DB provider "MSDAORA" for linked server "ORA_FDW001D" was unable to begin a distributed transaction.

  I looked around the site for some suggestions:

  http://www.sqlservercentral.com/Forums/Topic291577-5-1.aspx

  also this one: http://www.sqlservercentral.com/Forums/Topic396282-146-1.aspx

  I followed them and still get the same error.

  I am wondering how to setup and test DTC.

  I suppose I need DTC if doing something like this, even though I am not doing a 2 phase commit or any kind of distributed transaction in the purest sense of the term.

  Would greatly appreciate any help frm someone who has setup DTC or used a trigger to run dml in Oracle 10g.

  Steve

• sgambale

  SSCommitted

  Points: 1709

  More actions

  February 1, 2011 at 10:51 am

  #1281079

  Update:

  I used sql server to sql server and the trigger works.

  There is something about the Oracle/Solaris side that is missing or not right.

  Something about needing a DTC equivalent on the Oracle box.