SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server to Oracle through Microsoft OLEDB provider


Linked Server to Oracle through Microsoft OLEDB provider

Author
Message
praveen voleti
praveen voleti
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 179
Hi All,

I have created a linked server to Oracle database. I have used the below command to create the linked server:

sp_Addlinkedserver 'ORACserv','Oracle','MSDAORA','XYZ'

where XYZ is the Database name having associated TNS entries. Oracle client tools 9.2 are installed in the system.

After the linked server connection is created, I have specified the login credentials with which to connect. I am receiving the below error while querying the data:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

Here are the things that I have tried out till now:

Connect through SQL plus locally to the oracle server with the credentials given earlier. The connection succeeded and I am able to query the data.

Created one more linked server using Oracle OLEDB provider and the same credentials. The connection is working fine.

Checked the registry entries for ORACLE_HOME as specified in other forums. The directory that is being pointed is correct.

Tried both the distributed query like - select * from ORACSERV.XYZ.SCHEMANAME.TABLENAME and OPENQUERY statement. Both are failing with the above error.

I am not sure why I am receiving this error? The credentials seems to be correct and the Oracle server and SQL are also working fine.

Could someone please help in this regard?
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4795 Visits: 3226
Are the relevant Oracle tools (SQL Net is the most important) installed on the server ?

When you say that you can connect using SQL Plus, is this from your workstation or from the (SQL) Server ? When you attempt to use the linked server, it is from the server not your workstation.



praveen voleti
praveen voleti
Valued Member
Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)Valued Member (51 reputation)

Group: General Forum Members
Points: 51 Visits: 179
Thanks for the reply.
The oracle client tools have been installed on the server only from where I am attempting linked server connection.
The oracle OLEDB provider is working fine but for the microsoft oledb provider for oracle, I am receiving the above given error.
Sakthivel Chidambaram
Sakthivel Chidambaram
Mr or Mrs. 500
Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)Mr or Mrs. 500 (535 reputation)

Group: General Forum Members
Points: 535 Visits: 838
We had similair issue.. I had call with Microsoft and they are saying that their driver does'nt support any oracle database greater than 8i. Even for us the linked server was working till last week with MSDAORA but this week it throwed this same error and then later we created the linked server through oracle driver and it was working so we left the issue. Incase you find any fix, Please let this forum know...

Regards,
Sakthi
My Blog -> http://www.sqlserverdba.co.cc
victor.olufowobi
victor.olufowobi
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 92
Edit your
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI keys to contain the following:

a) "OracleOciLib"="oci.dll"
b) "OracleXaLib"="oraclient9.dll".
c) "OracleSqlLib"="orasql9.dll"

Then reboot your server. (I'm assuming the Oracle linked server is Oracle 9i)

Victor
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search