Oracle Linked Server Query - SQL Server Forums

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 Development (2000)

Oracle Linked Server Query

Author

Topic

jsmith
Starting Member

24 Posts

Posted - 2003-07-08 : 08:13:35

I've configured an linked server (from SQL 2000) as follows:

sp_addlinkedserver @server = 'MyServer',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'MyConnection'

I've added the following login:

sp_addlinkedsrvlogin 'MyServer',
False,
'sa',
'Sys',
'OracleSystemPassword'

I'm assuming the above means that I can connect to the 'MyServer' linked server from my SQL Server SA Account, and have 'System' privileges on my Oracle Server.

I see the following error when I try to run a query however. can anyone help? Thanks in advance.

select * from Anise..Sales.Customers ;

OLE DB provider 'MyServer' does not contain table '"Sales"."Customers"'. The table either does not exist or the current user does not have permissions on that table.

Connecting to Oracle through SQL Worksheet using the above credentials and running the above query works fine - so I expect the problem to be at the SQL Server end.

mfemenel
Professor Frink

1421 Posts

Posted - 2003-07-09 : 08:19:52

Try this and see what happens

Select * from openquery(myserver,'your sql code here')

Mike
"oh, that monkey is going to pay"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources