Linked server MSSQL to Oracle 9i - need help!

[maddave]

Im trying to create a linked server in MSSQL query analyser to connect the MS SQL database running on Windows 2000 to an Oracle 9i system running on Linux. I have managed to get Oracle to talk to MS SQL but now need it working the other way around so that I can select data from MS SQL.

The Oracle 9i system user and password are orauser and orapass

The MS sql user and password are msuser and mspass

I have oracle 9i client installed on the MS box, which was needed for the oracle to MS connectivity. I have created a system DSN on the MS box to Oracle and tested it using the oracle9i odbc driver. It connects fine.

I created a linked database by using query analyzer and doing:

EXEC sp_addlinkedserver
@server = 'Oracle_server_IP_Address',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'SYSTEM_DSN_NAME'

I then added logins for the server by doing:
Exec sp_addlinkedsrvlogin
@rmtsrvname='Oracle_server_IP_Address',
@rmtuser='orauser',
@rmtpassword='orapass'

I then try to see the tables in Ent Manager but it just gives an error:
Error 7399: OLE DB Provider 'MSDAORA' reported an error. The provider did not give any information about the error. OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returne 0x80004005: the provider did not give any information about the error.].

Hmm not very helpful message!

I think I may need to open a port on the firewall between ms sql and oracle as I had to do this when connecting the other way around. But if its connecting through the DSN which works, surely I dont have to do this?

Any help would be great as I am confused and new to this hole interconnectivity malarky!

Thanks.

 

[thendrickson]

I am having a similar problem with Informix running under HP Unix

I finally used the following:

exec sp_addlinkedserver @Server = 'traintcp',
@srvProduct = 'IBM Informix 3.82 32 bit',
@Provider='MSDASQL',
@PROVSTR= 'DRIVER={IBM Informix 3.82 32 bit};UID=me;PWD=12345;DATABASE=jtest;
HOST=server.name.comcom;
SRVR=traintcp;SERV=1527;PRO=olsoctcp;'

I found I still needed to set the user id and password seperately though

I am not quite certain exacly why, but the connection string needed to be on one line in QA to work.

it has been many years since I worked UNix or Linux, but I do recall thar carrage returns and line feeds are handled differently under UNIX and that may be why the connection string failed at first. (do not take that as gospal though!)

You might consider modifying my string for your use.

Please be aware, that in my case, performance is horribly slow though.

I have another thread posted and I will post my final solution there once I figure out what I am doing

 

[maddave]

Many thanks, will play around with your code to see whats what and how it all ties together.

 

[thendrickson]

good luck.