Microsoft KB Archive/271524

= PRB: Oracle Synonyms Associated with Database Links Not Listed by OLE DB Provider or ODBC Driver =

Article ID: 271524

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.0
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * Microsoft Open Database Connectivity 2.5
 * 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

-



This article was previously published under Q271524



SYMPTOMS
The Microsoft OLE DB Provider for Oracle and the Microsoft ODBC driver for Oracle do not display Oracle synonyms that are associated with database links created for remote database objects.



RESOLUTION
One possible workaround is to create Oracle Views instead of synonyms for the database links.



Steps to Reproduce Behavior
  In the schema &quot;scott&quot; on an Oracle database (&quot;Ora1DB&quot;, in this example), create a database link to a remote Oracle database (&quot;Ora2DB&quot;, in this example): CREATE DATABASE LINK Ora1DBToOra2DB CONNECT TO demo IDENTIFIED BY demo using 'Ora2DB';   Using the database link that you just created, create a synonym for a remote table (&quot;Ora2sales&quot;, in this example) on the remote Oracle database &quot;Ora2DB&quot;: CREATE SYNONYM synOra2sales FOR Ora2sales@Ora1DBToOra2DB; 

Then, using the database objects created above, you can reproduce the behavior by using either the ODBC driver or OLE DB provider as follows.

Microsoft ODBC for Oracle Driver

 * 1) Start the ODBC Test utility, which is part of the Microsoft Data Access Components (MDAC) SDK.
 * 2) Connect to Oracle server &quot;Ora1DB&quot; as user &quot;scott&quot; using the Microsoft ODBC driver for Oracle.
 * 3) On the Catalog menu, click SQLTables. To minimize the search criteria, specify the schema name &quot;scott&quot; in which this synonym exists.
 * 4) From the Results menu, click Get Data All to display the results.

The synonym &quot;synOra2sales&quot; does not appear in the list of tables/synonyms/views. You can see the synonyms associated with local Oracle tables only.

Microsoft OLE DB Provider for Oracle

 * 1) Start the Rowset Viewer utility, which is part of the MDAC SDK samples.
 * 2) Connect to Oracle server &quot;Ora1DB&quot; as user &quot;scott&quot; using the Microsoft OLE DB Provider for Oracle.
 * 3) On the Session menu, click IDBSchemaRowset, and then click GetRowset. In the IDBSchemaRowset dialog box, click Schemas and then select DBSCHEMA_TABLES. To minimize the search criteria, specify the TABLE_SCHEMA &quot;scott&quot; in which this synonym exists.

The synonym &quot;synOra2sales&quot; does not appear in the list of tables/synonyms/views. You can see the synonyms associated with local Oracle tables only.

