Msg 7391 : OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" was unable to begin a distributed transaction.

Question

Hi

I have encountered a problem in MSSQL trigger that creates the transaction in Oracle database using linked server. When trigger invokes it gives the following message

Msg 7391, Level 16, State 2, Procedure demotrg, Line 6 [Batch Start Line 0]
The operation could not be performed because OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" was unable to begin a distributed transaction.

All operations like insert,update,delete are working fine in ORACLE database when I issue command from MSSQL, but it is not working in MSSQL trigger.

following is the simple trigger code.

ALTER trigger [dbo].[demotrg] on [Efftronicsrtwms].[dbo].[screendata20] after insert
as
begin
begin distributed tran
SET XACT_ABORT ON;
INSERT INTO [ORACLE]..[TAMS].[DEMO] VALUES (1,'DSR',SYSDATETIME());
-- INSERT OPENQUERY (ORACLE, 'SELECT * FROM TAMS.DEMO') VALUES (12,'DSR',getdate());
commit tran;

end;

I tried all combinations with begin transaction, set exact_abor on, but no vain.

Requesting the solution if you had encountered.

Regards
Subbarao Dasari

Answer 1

You have to enable the RPC = "Remote Procedure Call" setting in the linked server properties, a trigger is nothing else then a procedure.

Answer 2

Hi SubbaRaoDasari-5162,

Here is an article which might help.

Is Microsoft Distributed Transaction Coordinator (MSDTC) running?
Please try to enable Allow Remote Clients and Allow Outbound on Component Services -> Distributed Transaction Coordinator ->Local DTC Properties-> Security tab.
And please check the registry key value on the client computer that is running SQL Server is correct.

If your Oracle database is 8.0, your client dll file will be “Oraclient8.dll” and if you are using Oracle 11g, the filename would be “Oraclient11.dll”. Please remember to backup registry key first.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

I am having exact same problem. My linked server has following settings:

Like original poster, i can manually run insert queries to linked server fine, trigger procedures don't work.