English

Problem: Why can't I preview the contents of an OLE DB table I can see listed in the TOC window?

Description

The contents of some OLE DB tables listed in the TOC window in ArcCatalog cannot be previewed.

Cause

AN OLE DB connection will return a list of table names which includes:
a) Any table(s) you own.
b) Any table(s) you have been granted "object privileges" on (select, delete, insert, update, index, references, and alter).
c) Any table(s) where any of the object privileges have been granted to a role assigned to you, for example, the "Public" user group or role.

OLE DB tables are returned as unqualified database objects when you try to view the contents of a table not owned by you, the table cannot be identified correctly to return all the rows.

Solution or Workaround

1. Create a public synonym for the table.

Examples:

For DB2:

Code:
CREATE NICKNAME <nickname> FOR <table>

For Oracle:
Code:
CREATE PUBLIC SYNONYM <schema>.<synonym> FOR <SCHEMA>.<OBJECT>;

2. Assign the public role to the user who is accessing the tables.

Examples:

For DB2:

Code:
GRANT <user> ON <nickname> TO PUBLIC

For Oracle:
Code:
GRANT PUBLIC TO <user>;

Note:
Unqualified database objects are identified only by the object name and not by the fully qualified database name of <object owner.object name> e.g. user1.mytable, or <database name.object owner.object name> in the case of DBMS packages which support multiple databases.