I'm having trouble querying from my SQL server 2000 db on my local machine to a remote Oracle server 9i.

I can get the server link setup with both MS OLE DB and the Oracle OLE DB drivers and can get a list of tables.

EXEC sp_addlinkedserver
   @server = 'DB',
   @srvproduct = 'Microsoft OLE DB Provider for Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'servername',
GO

EXEC sp_addlinkedsrvlogin
   @rmtsrvname = 'DB',
   @useself = 'false',
   @rmtuser = 'login',
   @rmtpassword = 'password'
GO

EXEC sp_tables_ex DB
GO

and viola, I get tables upon tables...:D


BUT when I do something simple like this:

SELECT * FROM DB...table where item = something or

SELECT * FROM DB..user.table where item = something


I get this with either OLE DB driver:

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'DB' does not contain table 'table'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='DB', TableName='table'].

The tables do exist and the login has read-only rights to all tables... I'm totally stumped because the DB genius here sounded extremely relieved when I go the table listing like everything was going to be gravy from there.. any ideas would be amazingly appreciated... am I trying to query wrong? Should I be using some sort of db function or procedure to send the queries to the Oracle DB to get a result on the SQL server. I'm doing some testing for use in a Stored Procedure...

Hi,

I don't have the exact answer but I did find some interesting links that might apply to your situation. Of course, you probably already know that you can connect to the server and the problem is most likely in how you fashion the query. Anyway, here are some examples of how to query linked servers. Hope this helps. Also, if you find the answer please post it.

Thanks,
Richard


http://www.codeproject.com/cs/databa...ed_Servers.asp

http://msdn.microsoft.com/library/de...uted_query.asp

http://www.devx.com/vb2themax/Tip/18623

http://www.sql-server-performance.com/linked_server.asp

Thanks Rich,
     Actually, about an hour later I found a post to use openquery through the MSDAORA provider and was able to query the DB properly. But, trying it on my dev box, I am receiving a 7399 error saying the oracle client hasn't been installed, when I am using the msdaora provider.. still stumped, but glad I can query on my personal test machine... : P

Sounds like progress. So the OPENQUERY works?! Did you have the Oracle client installed on your personal test machine?

Yes, *much* progress. The only thing keeping me from getting this into QA is knowing what to install on the QA machines for the link to function correctly.

I have so much crap installed on this machine, I don't know what's not allowing me to make the connection with MSDAORA, since it is a MS provider. I do have the oracle 10g dev suite installed on my machine.

I tried installing the Oracle provider for OLE DB on a dev machine(win2k3 server) that I have access to, but still got the error. I even setup the net config. I tried to switch to the Oracle provider, but now the enterprise manager hangs when I try to view the server. Not good...

Server: Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 13
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

Provider is unable to function until these components are installed.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize returned 0x80004005: ].

Hmmmm.. You said you installed them right? Is your test box a server too? Could just need to uninstall and reinstall the client on the server. I don't know really. Just fishing. Are you running these tests in QA while logged on locally to both boxes? Sorry if this is a distraction to you since I don't really have THE answer.

On the Error:
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.

I got the error (using client 10g), and was finally able to fix it by changing the following registry keys to the values indicated:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
"OracleXaLib"="oraclient10.dll"
"OracleSqlLib"="orasql10.dll"
"OracleOciLib"="oci.dll"

These were set to some old values (probably for Oracle client 8 which was there on my machine earlier).

Hope this helps..