Linked Server from SQL Server 2005 to Oracle

Hi,

Oracle client is installed and TNSNAME.ORA is available on the SQL Server where I'd like to create linked server.

The Oracle provider is "Oracle Provider for OLE DB"
Product name is "OraOLEDB.Oracle"

After creating the Oracle Linked server, I tried a simple select statement by 4 part full qualified name as below.

select * from OraDB..Schema.TBName

I am getting follwoing error message.

Msg 7311, Level 16, State 2, Line 1
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "OraOLEDB.Oracle" for linked server "OraDB". The provider supports the interface, but returns a failure code when it is used.

But it works for OPENQUERY. It looks for me that in some situation, 4 part full qualifier name doesnt work while openquery works.

Could someone please advise the reason?

Thanks

Oracle is case sensitive, so change all schema, table and column names to upper case

select * from OraDB..SCOTT.TABLE1

Also I have seen Microsoft Oledb provider for Oracle giving less problem than Oracle's provider

Unfortunately it doesn't work that way, you need use EXECUTE like:

EXEC ('select * from Schema.TBName') AT OraDB;

The query executed on Oracle side and return result set.

This is not the right syntax for SQL Server.

EXEC ('select * from Schema.TBName') AT OraDB

Yes it is correct syntax, check Books Online

Using EXECUTE to query an Oracle database on a linked server

Or use openquery.

Ok, I take it back. I did not realize it is a new syntax in SQL2K5.

I ran into similar problem using ASE OLE DB Provider (Sybase). I was using a data source and had quoted identifiers enabled so Provider was sending incorrect syntax for the call to collect table information. Turned off the quoted identifiers and now it works. Does Oracle data source have a similar setting?