Kbase P183705: Is there a way to force the DataServer for MS SQL Server to perform a server-side join when querying
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
3/8/2011 |
|
Status: Unverified
GOAL:
Is there a way to force the DataServer for MS SQL Server to perform a server-side join when querying multiple databases?
GOAL:
Is there a way to force the DataServer for MS SQL Server to perform a server-side join when performing a multi-database query?
GOAL:
How to use server-side joins when querying across multiple MS SQL Server databases using DataServer for MS SQL Server?
FACT(s) (Environment):
Progress 9.1x
OpenEdge 10.x
Windows
FIX:
Per the OpenEdge® Data Management: DataServer for Microsoft® SQL Server manual, a number of conditions must be met in order to determine whether a join by SQLDB is possible. The first criteria assessed is whether all tables in the join are in the same logical OpenEdge database; that is, they are contained in the same DataServer schema.
To meet this criteria, it is possible to pull all the foreign definitions from different SQL databases on the same server into one logical schema holder database. Note, all SQL server databases exist within the same MS SQL Server instance as OpenEdge does not store server names.
For example, using "Sportsldb" as the logical schema holder database with two SQL Server databases, "Sports102A" and "Sports102B":
- Use ODBC DSN for "Sports102A" to pull its definitions into "Sportsldb" as standard with the Create DataServer Schema utility in Data Administration.
- Use the Edit Connection Information dialog in Data Administration to change the ODBC DSN to one which points to "Sports102B".
- Reconnect to the schema holder and pull the definitions into "Sportsldb".
In the schema definitions, each table has a Qualifier entry which identifies its corresponding SQL database. This allows the writing of standard ABL queries as if both tables existed within the same foreign database which can result in server-side joins. For example, in the following query, the Customer table is located in "Sports102A" and Order_ in "Sports102B".
FOR EACH customer,
EACH order_ WHERE order_.custnum = customer.custnum:
DISP customer.custnum order_.custnum order_.ordernum order_.orderDate.
END.
Note that other criteria for server-side joins detailed in the manual still apply.