Selecting data from Oracle on a SQL Server.
-
I have a requirement on our Clustered SQL Server to be able to select data from an Oracle 9i database. SQL2000 SP3 on Win2003 SP1.
This is what i have done:
Installed Oracle 9i client sqlplus client on both clustered servers and configured the tnsnames file for that specific db. I can access the database on the server via SQL Plus. Then, I ran the below statements:
sp_addlinkedserver 'AIXORASVR','Oracle','MSDAORA','FILPQA.GLOBAL.ABC.COM'
sp_addlinkedsrvlogin 'AIXORASVR',false, NULL ,'LOGIN,'PASSWORD'
I get the below message when I attempt to use QA to select from the Oracle table:
select count(1) from AIXORASVR..schemaowner.binchartitem
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components were not found.
I guess I don't understand what it wants me to install as I have installed the Oracle connectivity tool. Any ideas ?
-
Unfortunately, I don't have Oracle installed on a SQL Server I can get to right now, but when I had to go through this in the past, the 'MSDAORA' provider name had to be 'MSDAORA.1', or something to that effect. Search the machine's registry for MSDAORA and see if that turns up anything.
-Eddie
Eddie Wuerch
MCM: SQL -
Have you rebooted the machines?
-
No I have not. Do you think I have to ? When I installed the Oracle client it did not ask to reboot.
-
The Oracle installation fiddles with the registry.
Perhaps just a restart of sql server service will do.
I've allways rebooted the machines after the installation of Oracle 8 just to be sure the registry is read correctly by sql server so it can access all drivers.
*a KB article on Oracle linked server
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
-
A reboot did the trick. Something so simple wow ! Thx !
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply