Geek asks and answers

Running a query from a linked server (Oracle) in SQL Server2008 R2

I have a linked server configured in SQL Server 2008. But I was not able to run any query against the linked server.

enter image description here

I tried to run this simple command, but it does not work

SELECT * FROM MYSERVER..ALANH.TEMP_UPDATE1

This is the error I received while executing 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.

Can someone help me connect to OracleLinkedServer? Many thanks.

+3
source share
6 answers

you can also be like this:

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

You can write a query as follows:

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

Important: in this case, the full name of the table must be written in upper case.

+2
source

.

, . MS KB

, Oracle . . Oracle.

Oracle , . , .

+1

- :

SELECT * FROM ALL_TABLES@"SOME.SERVER.NAME";

ALL_TABLES SOME.SERVER.NAME.

0

.

v12 ODP.NET odbc , " " , , . .

Use the query below to determine what the correct table name is, although you need to specify the schema name in the right case for the query to work. Try everything in uppercase, try everything in lowercase, try a mixed case or even better get the actual name from dba (I heard that only the names of the tables / schemas that are specified in "" will be resolved by the mixed case, otherwise in oracle everything is uppercase letters.)

sp_tables_ex @table_server = InsertLinkedServerHere , @table_schema = InsertSchemaNameHere

0
source

More articles:


All Articles