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 between sql server and oracle

171564Jun 20 2005 — edited Aug 19 2008
I have set up a linked server between sql 2000 and oracle9i database using MSDAORA. I have installed oracle9i client on sql box and modified the registry keys. Linked server works fine for selecting data from oracle database and I can insert a row into oracle table directly into oracle table from SQL query analyzer. I created a trigger on sql table so that when new data is inserted into this table, trigger should fire up and this new data should be inserted into oracle side. this is where it fails and give following error:
Server: Msg 7391, Level 16, State 1, Procedure tr_banister, Line 16
The operation could not be performed because the OLE DB provider
'MSDAORA'
was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'MSDAORA'
ITransactionJoin::JoinTransaction returned 0x8004d01b].

Comments

463544
I have the similar problem. I am using livelink server and sql server together to access oracle 9i. But I am getting this same error 7391

thanks
san
401827
Hi,
I am also in the same situation. I want to insert a record into the remote Oracle Database when there is a row inserted in the SQL Server Database. I am also getting the same error. Were you able to resolve the same? Please let me know.

Thanks
525167
Hi,
I have exactly the same problem.
I have SQL Server 2000 linked to ORACLE under Unix and I am able to do all queries between the servers from Query Analizer.Stored procedures work OK.

But as soon as I put query statesment into trigger I have error 7391 and the
following message: "The operation could not be performed because OLE DB
provider 'MSDAORA' for linked server 'oradb' was unable to begin a
distributed transaction."

The same trigger works fine if you try it on the same SQL SERVER server.

If you have any solution, please reply.

Marina.
96052
I believe that I read somewhere that you need to make sure that the "Distributed Transaction Coordinator" is active on the machine hosting the SQL Server.

Hope that helps.

BG...
545499
I have the similar problem.but i use sql server 2005.
630391
Is Oracle Client must needed while using Linked Server in Sql Server 2005 to connect ORACLE.

Do we have any other means

Can't Oracle OLEDB Provider for Oracle help us in establishing Linked Server Connection to Oracle

Pardon me If am deviating from the path

Regards,
Palani
user641259
I have exactly the same problem than all of you, but with SQL Server 2005.
I try to do an INSERT into a table in the linked server, and it works perfectly OK except if the sentence is in a trigger, which is actually what I need to do.

The error I get is the 7391 again.

Have anyone of us solved the problem?
Any help would be appreciated.
(Sorry for my English)

Elena
451222
I don't know if this is much help, but I came across this on the MS site:
http://support.microsoft.com/kb/280106

Message 9
Error 7391: The operation could not be performed because the OLE DB provider 'MSDAORA' does not support distributed transactions. OLE DB error trace [OLE/DB Provider 'MSDAORA' ITransactionJoin::JoinTransaction returned 0x8004d01b]
Verify that the OCI versions are registered correctly as described earlier in this article.

Note If the registry entries are all correct, the MtxOCI.dll file is loaded. If the MtxOCI.dll file is not loaded, you cannot perform distributed transactions against Oracle by using Microsoft OLE DB Provider for Oracle or by using Microsoft ODBC Driver for Oracle. If you are using a third-party provider and you receive Error 7391, verify that the OLE DB provider that you are using supports distributed transactions. If the OLE DB provider does support distributed transactions, verify that the Microsoft Distributed Transaction Coordinator (MSDTC) is running.
1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Sep 16 2008
Added on Jun 20 2005
#oracle-provider-for-ole-db, #windows-and-net
8 comments
23,326 views