Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2000 Forums  SQL Server Administration (2000)  Linked server MSSQL - Oracle 9i.

Author  Topic  djheath Starting Member 7 Posts Posted - 2006-05-18 : 09:36:37 Hi, I hope someone can help or point me in the right direction. 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.