Run a Query from Linked Server (Oracle) in SQL Server2008 R2

Question

I have the linked server set up in SQL Server 2008. But I could not run any query against the linked server.

I tried to run this simple command but it's not working

SELECT * FROM MYSERVER..ALANH.TEMP_UPDATE1

This is the error I got when I run the above command.

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "OraOLEDB.Oracle" for linked server "MYSERVER" reported an error. The provider did not give any information about the error.
Msg 7312, Level 16, State 1, Line 1
Invalid use of schema or catalog for OLE DB provider "OraOLEDB.Oracle" for linked server "MYSERVER". A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.

Could anyone help me to connect to the OracleLinkedServer? Thanks very much.

Answer 1

you can be that way too:

**SELECT * FROM OPENQUERY(MYSERVER, 'SELECT * FROM ALANH.TEMP_UPDATE1')**

Answer 2

You can write the query like this:

select * FROM [MYSERVER]..[ALANH].[TEMP_UPDATE1]

Important: In this case, the fully qualified table name must be written in upper case.