Problem - In Linked Servers

• Syed Moiz-208330

  Newbie

  Points: 1

  More actions

  April 24, 2006 at 2:19 am

  #91720

  Hi,

  Iam new to exploring the linked servers of MS SQL SERVER 2000, and i had created linked server (Oracle DB) and trying to insert record into linked server whenever a new record is inserted into a SQL Server database table. For that reason i have created the below described trigger.

       CREATE TRIGGER TEST_TRIG ON ORACLE_TEST

              FOR INSERT

              AS

              DECLARE @EMPNO VARCHAR(10)

              DECLARE @REMAKR VARCHAR(30)

              SELECT @EMPNO = (SELECT EMPNO FROM INSERTED)

              SELECT @REMAKR = (SELECT REMAKR FROM INSERTED)

              SET XACT_ABORT ON

              insert into sedco5..APPS.TEST_TIME

              values (null,@EMPNO, null,@REMAKR)

              PRINT @EMPNO + ' ' + @REMAKR

  This trigger fails to execute whenever i insert record into my SQL table and displays the below shown error message in Query Analyzer.

  Error:

  Server: Msg 7391, Level 16, State 1, Procedure TEST_TRIG, Line 9

  The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

  My Question is how to resolve this error and make my trigger insert record into tables in linked server.

  Thanks in advance for your prompt response.

  Regards.

   

   

   

   

• Ronzo

  Hall of Fame

  Points: 3744

  More actions

  April 25, 2006 at 11:32 am

  #634127

  Quick and cheesy answer is remove SET XACT_ABORT ON.

  This is what is trying to begin the distributed transaction with Oracle using the MSDORA provider.

   

  ---

  Have Fun!
  Ronzo
  

• Marco Arce

  Newbie

  Points: 1

  More actions

  June 17, 2006 at 7:39 am

  #644459

  HELLO, i have the same problem, when try to execute a insert in a oracle linked server from sqlserver,  

  Error:

  Server: Msg 7391, Level 16, State 1, Procedure MITRIGGER, Line 10

  The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

  I have, try with another ways to resolve this, like

  1.restart de DTC service.

  2.change the regitry, HKLM\Software\Microsoft\MSDTC\MtxOCI..., and then update the entries to oracles's dll.

  Have you try another way to resolve this?, please can you help me...