-
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
-
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|