Accessing linked server

[par60056]

I have an Oracle server that I am trying to link to and access from SQL Server 7.

I can access the Oracle server using the Oracle sqlplus command "sqlplus user/passwd@server" and "select field from schema.table"

In SQL Server osql I issued the following:
sp_addlinkedserver @server = 'myOServer', @srvproduct = 'Oracle',
@provider='MSDAORA', @datasrc='server'

sp_addlinkedsrvlogin 'myOServer', 'false', 'sa', 'user', 'passwd'


I cannot seem to find the right format for referencing the tables in the Oracle server. If in the osql I try:
select field from myOserver.schema..table
or
select field from myOserver.schema.sa.table
or
select field from myOserver.schema.user.table

I get the message "[OLE/DB provider returned message: Oracle error occurred, but error message could not be retrieved from Oracle.]"

Anybody have any help?

Peter Richardson

 

[MeanGreen]

I think you need to use the OPENQUERY statement with your linked server. Not sure, never messed with Oracle, but when I set up a linked server to Active Directory, I have to use OPENQUERY.

Hope this helps.

 

[osjohnm]

Hi

This will allow you to run queries against oracle

select * from OPENQUERY(myOServer, "select * from table&quot

You have to use double quotes around the select within the OPENQUERY statement.

Another thing I'm not sure if you will be able to access the oracle server even though the linked server is setup. We have numerous linked servers from SQL 2000 to Oracle, SQL 2000 to SQL 7, SQL 2000 to SQL 6.5 but we can't get SQL 7 to Oracle to work.

Hope this helps

John