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.

Solution 1

you can be that way too:

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

Solution 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.

Solution 3

You might try the fix from this article.

Also, this could be a problem with naming. From an MS KB article

If you receive these error messages, a table may be missing in the Oracle schema or you may not have permissions on that table. Verify that the schema name has been typed by using uppercase. The alphabetical case of the table and of the columns should be as specified in the Oracle system tables.

On the Oracle side, a table or a column that is created without double quotation marks is stored in uppercase. If the table or the column is enclosed in double quotation marks, the table or the column is stored as is.

Comments

[Nick.McDermaid]

This is syntax from the Oracle side