Focal Point
Join External SQL Synonyms

This topic can be found at:

February 13, 2017, 07:20 PM
Join External SQL Synonyms
I am trying to create a join between two synonyms that each describe an external SQL query, on separate database connections. Both connections are MSSQL 2008 adapters. Report can be created from each synonym individually with no problems. However, a join attempt produces an FOC1406 SQL OPEN CURSOR ERROR.

Error is produced whether attempting the join in the report (via IA+) or creating the join in a clustered synonym.

If the synonyms are regular table descriptions (i.e. not from external SQL query), the join works correctly.

I have SUBQUERY=Y in the access file for each synonym. SQL Trace output confirms that Focus-managed join is enabled.

Is this scenario supported in BUE? Any help, info, or reference documentation would be appreciated.
February 15, 2017, 10:20 AM
Richard Sitt
Dear Edward,

The dynamic join will not work with external SQL synonyms. Try creating a report in IA+ using these steps:

Refer to the online help topic: Creating Content -> Creating Hold Files

Hope this helps.
Richard Sitt

This message has been edited. Last edited by: Richard Sitt,
February 15, 2017, 03:21 PM
Thanks for the information, Richard. Which file format should the HOLD file be saved in?

Windows, All Outputs
February 15, 2017, 05:41 PM
Richard Sitt
Binary format is fine. As for location, put it in the application directory that matches your domain name.