Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved.
Terms
of Use |
Trademarks
Article ID: 240340 - Last Review: December 5, 2003 - Revision: 4.2
PRB: SQL Distributed Query with Oracle Causes "Could not open table" Error
This article was previously published under Q240340
Attempting to use a query with an Oracle linked server using the MSDAORA OLE DB provider, for example:
select * from OracleServer..demo.Product
may cause an error similar to the following:
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]
The query may not work because of case sensitivity in Oracle since SQL Server uses quoted names when it ships queries to a provider.
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.
Case-sensitivity is not an issue when using OpenQuery or OpenRowset function. The following should work OK:
SELECT * FROM OPENQUERY(OracleServer, 'select * from PrOdUct')
go
SELECT * FROM OPENRowset('MSDAORA', 'myOracleServer';'demo';'demo', 'select * from PrOdUct' )
go
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
- 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
- Specify a four part name query:
SELECT * FROM OracleServer..demo.Product
REFERENCES
For additional information on SQL Server distributed queries with Oracle data source, see the SQL 7.0 Books Online topic "OLE DB Provider for Oracle" and the following articles in the Microsoft Knowledge Base:
220915Â
(http://kbalertz.com/Feedback.aspx?kbNumber=220915/EN-US/
)
PRB: SQL Distributed Query with Oracle Needs Oracle Client
221552Â
(http://kbalertz.com/Feedback.aspx?kbNumber=221552/EN-US/
)
PRB: SQL Distributed Query with Oracle NUMERIC Column Gives Error
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft SQL Server 2000 Standard Edition
| kbdatabase kboracle kbprb KB240340 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate