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

oracle sql-server-2008 linked-server
TTCG picture TTCG · Jun 16, 2011 · Viewed 41.7k times · Source

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

enter image description here

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

fasr picture fasr · Oct 24, 2013

you can be that way too:

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

Related questions

Run SQL Server Stored Procedure via Database Link from Oracle

Referring to How to execute an Oracle stored procedure via a database link, it does not work in my case. I don't know what I am missing. I have a SQL Server instance and Oracle database in the same computer. …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More