SQL Server Error..Please Help

[manohar143]

I am Using OLE DB for Oracle (MSDAORA) for connecting to Oracle Database form SQL Server.
I followed the steps given below for same:

1) Created Linked Server with sp_addlinkedserver
2)Created mapping between logins on the local server running MS SQL Server and remote logins on the linked (Oracle) server.
After Configuring logged into MS SQL server and executed the following query
select * from OrclDB..SCOTT.EMP
and then SQL Server throws following error

*****1MS SQL Server Error******

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.]

If anybody have done this and faced the same problem, please share there experiences.


Thanks in Advance,
Manohar

 

[tlbroadbent]

Check the MS Knowledgebase article at the following link.

HOW TO: Set Up and Troubleshoot a Linked Server to Oracle in SQL Server

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q280106

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[martynh]

A couple of things you could read up on:

MS knowledge base article: Q820106
Oracle bug: 1397927

These both affect Oracle as a linked server. Note also that MSDAORA was written for Oracle 7, and had a few 8 features added. It hasn't been changed for Oracle 9 (so far as I know). You need the Oracle 8 client installed since MSDAORA uses some of the dll's.

Regards

Martyn
martyn.hodgson@cdesolutions.com