1 Reply Latest reply: May 7, 2013 1:26 AM by vijai RSS

    linked server "LK_SERVER_NAME" was unable to begin a distributed transactio

    vijai
      Hi,

      I have two DBs namely MS SQL 2008 and ORACLE 11g. I need to transfer data from MS SQL to ORACLE using the linked server. I did the following,

      I have a stored procedure in ORACLE as call_linked_oracle_procedure which needs to be called from the MS SQL server to transfer the data.

      So the steps I did to achieve this is provided as a sample as follows,

      /* Creating a sample table */

      create table source_tab3
      (
      id numeric,
      name varchar(MAX)
      )
      GO
      ---------------------------------------------------------
      /* Creating a insert trigger for the sample table which calls the oracle through the linked server*/


      IF OBJECT_ID ('dbo.source_tab3Trigger','TR') IS NOT NULL
      DROP TRIGGER dbo.source_tab3Trigger;
      GO
      CREATE TRIGGER source_tab3Trigger ON source_tab3
      AFTER INSERT
      AS
      DECLARE @output varchar(600)
      EXEC [dbo].[call_linked_oracle_procedure] @parameter1 =N'value1', @parameter2 =N'value2'
      select @output
      GO
      -----------------------------------------------------
      /* Insert a sample data */

      insert into source_tab3 (id,name) (1,'vijai');
      GO

      Now when I insert the data, It give the following error

      _____________________________________________________________________________________

      OLE DB provider "OraOLEDB.Oracle" for linked server "LK_SERVER_NAME" returned message "New transaction cannot enlist in the specified transaction coordinator. ".

      Msg 7391, Level 16, State 2, Procedure source_tab3Trigger, Line 11
      The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "LK_ORACLE" was unable to begin a distributed transaction.

      _____________________________________________________________________________________


      The things I did to solve this were,

      1. Enabled the MSDTC services (Distributed Transaction Coordinator is started ).

      2. Enabled the Network DTC access from the control panel, component services to 'Allow InBound' etc.

      3. My 'OracleMTSRecoveryService' is running in my services list.

      Also, I have done all these where my machine where MS SQL 2008 is installed. Dono what needs to be done in the ORACLE server.

      Also to note, that I am able to query the tables/procedure via linked server individually, but not able to do that within the triggers.

      Any tip is much appreciated.

      regards,

      Vijai
        • 1. Re: linked server "LK_SERVER_NAME" was unable to begin a distributed transactio
          vijai
          Hi,

          I made few changes in the SQL trigger code, and it seems to work. But not sure if its the right code. Any way I will leave my findings here, so others might get a tip for their tries,

          If anyone know who my code is working, please leave a comment,

          -------------------------------------------------------------------------------------------------------------------------

          IF OBJECT_ID ('dbo.source_tab3Trigger','TR') IS NOT NULL
          DROP TRIGGER dbo.source_tab3Trigger;
          GO
          CREATE TRIGGER source_tab3Trigger ON source_tab3
          AFTER INSERT, UPDATE
          AS
          commit transaction;
          set implicit_transactions on
          DECLARE @output varchar(600)
          DECLARE @p_1 varchar(600) = 'EASYLINK51_EBS_MECH'
          DECLARE @p_2 varchar(600) = 'standard'
          DECLARE @p_3 varchar(600) = 'EBS'

          EXEC [dbo].[call_linked_sys_param] @p_1 =N'EASYLINK51_EBS_MECH', @p_2 =N'standard', @p_3 =N'EBS', @output = @output OUTPUT
          select @output
          set implicit_transactions off
          begin transaction;

          GO

          -------------------------------------------------------------------------------------------------------------------------

          regards,
          Vijai