Kbase P162635: Third-party tools do not display auxiliary catalog data when connected to multiple OpenEdge database
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
03/06/2010 |
|
Status: Unverified
SYMPTOM(s):
Third-party tools do not display auxiliary catalog data when connected to multiple OpenEdge databases
Crystal Reports doesn't show tables in different catalogs from the same DSN
Crystal Reports shows ... no item found ...message when expanding catalogs for new report
Auxiliary databases are not displayed by Microsoft Query when using multi-database connectivity
Secondary catalogs are not displayed by MS Excel when connecting to multiple databases with the -mdbq parameter
Tables in auxiliary databases are not listed by Query Wizard
If there are columns in tables of an auxiliary database that have the same name as columns in tables of the primary database, those columns are listed twice in the Query Wizard under the primary database table
Database drop-down list is grayed out in MS Query Add Tables dialog
FACT(s) (Environment):
ODBC DSN configured for multiple database connections
Windows
OpenEdge 10.1x
OpenEdge 10.2x
CAUSE:
The OpenEdge SQL engine and ODBC driver are functioning as designed. These related behaviors seem to be limitations of the third-party tools.
Crystal Reports can construct a report which selects data from all the databases in a single SQL query. However, the report creator has to include table and column definitions one catalog (database) at a time.
The Microsoft Query tool, which is used to allow end users to create queries on ODBC data sources to populate Excel spreadsheets, ignores catalog data from auxiliary catalogs when retrieving catalog data for the Query Wizard.
Both Crystal Reports and Microsoft Query can execute freeform SQL queries on multiple catalogs when the queries are fully qualified with the catalog names.
FIX:
In addition to using freeform SQL queries, the following workarounds can allow more multi-catalog functionality to be obtained from these tools:
For Crystal Reports:
To add tables from multiple catalogs to a report, do so one catalog at a time:
Add the desired tables from the first catalog.
Click the Refresh option in the Connection window, which shows catalogs.
Add the desired tables from the second catalog.
Repeat as needed.
For Microsoft Query:
For each table in an auxiliary database that will need to be used in a query, create a view in the primary database using the fully qualified table name.
Set the MS Query options to show views.
Create queries on the views in the primary database in order to retrieve data from the underlying tables in the secondary database.