PRB: SQL Distributed Query with Oracle Causes "Could not open table" Error

• Microsoft SQL Server version 7.0
• Microsoft OLE DB Provider for Oracle, version 2.0

SYMPTOMS

Attempting to use a query with an Oracle linked server using the MSDAORA OLE DB provider, for example:

select * from OracleServer..demo.Product 

Server: Msg 7306, Level 16, State 2, Line 1
Could not open table '"demo"."Product"' from OLE DB provider 'MSDAORA'. The specified table does not exist.
[OLE/DB provider returned message: The specified table does not exist]
[OLE/DB provider returned message: ORA-00942: table or view does not exist]

CAUSE

The query may not work because of case sensitivity in Oracle since SQL Server uses quoted names when it ships queries to a provider.

WORKAROUND

Use the following rules when referencing tables in an Oracle linked server:

• If the table and column names were created in Oracle without quoted identifiers, use all uppercase names. For example, the following should work OK:

  
  select * from OracleServer..DEMO.PRODUCT 




• If the table and column names were created in Oracle with quoted identifiers, use the same case for all letters of the names as was used when the names were created in Oracle.

SELECT * FROM OPENQUERY(OracleServer, 'select * from PrOdUct')
go
SELECT * FROM OPENRowset('MSDAORA', 'myOracleServer';'demo';'demo', 'select * from PrOdUct' )
go 

MORE INFORMATION

Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is accomplished by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four part names including a linked-server name. For example:

sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'
SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1') 

Steps to Reproduce the Behavior

1. Set up a linked server to Oracle server:

   
   EXEC sp_addlinkedserver   'OracleServer',  'Oracle',  'MSDAORA',  'myOracleServer'
   go
   EXEC sp_addlinkedsrvlogin 'OracleServer', 'FALSE', NULL, 'demo', 'demo'
   go 




2. Specify a four part name query:

   
   SELECT * FROM OracleServer..demo.Product 

   Which reports the error described in the SYMPTOMS section.

REFERENCES

Q220915 PRB: SQL Distributed Query with Oracle Needs Oracle Client

Q221552 PRB: SQL Distributed Query with Oracle NUMERIC Column Gives Error

Additional query words:

Keywords : kbDatabase kbOLEDB kbSQLServ kbVC
Version : WINDOWS:2.0; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbprb