Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

vijaiMay 6 2013 — edited May 7 2013
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

Comments

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
1 - 1
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 4 2013
Added on May 6 2013
#oracle-providers-for-asp-net, #server, #transaction, #windows-and-net
1 comment
5,475 views