This discussion is archived
1 Reply Latest reply: May 6, 2013 11:26 PM by vijai RSS

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

vijai Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points