
	Search KbAlertz Advanced Search

Receive Microsoft Knowledge Base articles by E-Mail?

Every night we scan the Microsoft Knowledge Base. If technologies you're interested in are updated, we'll send you an e-mail. You only get one e-mail a day, and only when new articles are added.

Click here to create a
FREE account

Already have an account?
[Click here to Login]

Microsoft Knowledge Base Article

This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks

Ads by Lake Quincy Media

Article ID: 271524 - Last Review: October 17, 2003 - Revision: 3.0

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

Retired KB Content Disclaimer

View products that this article applies to.

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.

Back to the top

RESOLUTION

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

Back to the top

MORE INFORMATION

Steps to Reproduce Behavior

In the schema "scott" on an Oracle database ("Ora1DB", in this example), create a database link to a remote Oracle database ("Ora2DB", 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 ("Ora2sales", in this example) on the remote Oracle database "Ora2DB":
```
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

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

The synonym "synOra2sales" 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

Start the Rowset Viewer utility, which is part of the MDAC SDK samples.
Connect to Oracle server "Ora1DB" as user "scott" using the Microsoft OLE DB Provider for Oracle.
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 "scott" in which this synonym exists.

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

Back to the top

REFERENCES

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

239103Â (http://kbalertz.com/Feedback.aspx?kbNumber=239103/EN-US/ ) Synonyms of DBLinked Tables Do Not Show in Data View Window

You can obtain the ODBC Test and Rowset Viewer utilities from the MDAC SDK, which comes with Microsoft Visual Studio, or which can be downloaded from the following Microsoft Web site:

http://msdn.microsoft.com/dataaccess (http://msdn.microsoft.com/dataaccess)

Back to the top

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

Back to the top

kbfile kboracle kbprb KB271524

Back to the top

Retired KB Content Disclaimer

This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Back to the top

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

Ads by Lake Quincy Media