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

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