failed to open a rowset

[weiwei]

Using Crystal Reports 10 with outer joins produces syntax error Failed to open Rowset ODBC progress driver Progress Syntax error (7587).
How can I fix this?
Thanks

 

[Lee Curzon]

There is a knowledge base entry for this error:

P84577
Title: "Crystal Reports 10 requires Registry update for Outer Joins"

http://tinyurl.com/pvat9

HTH

Lee

 

[JPPowers]

Hi

The tinyurl link brings up a blank page here. Could someone please check it ? I am having a similar issue. Please see "Crystal Reports/ Progress - Error w/ 3 tables thread"

Joe P

 

[Lee Curzon]

It works for me with Firefox and IE.

However, you can go directly to the knowledgebase / Search by solution ID section:

http://progress.atgnow.com/esprogress/openSolution.do

And type in the KB number I gave (P84577).

But to save you the bother:

Title: "Crystal Reports 10 requires Registry update for Outer Joins"
Created: 06/16/2004 Last Modified: 05/06/2005
Status: Unverified


Facts:
# Progress 9.1D
# Progress 9.1E
# OpenEdge 10.x
# Crystal Reports 10
# MERANT 3.60 32-BIT Progress SQL-92
# DataDirect 4.1 32-Bit Progress SQL92 9.1D
# DataDirect 4.20 32-Bit OpenEdge SQL92 10.0A
# DataDirect 4.20 32-Bit OpenEdge SQL92 10.0B
# Windows


Symptoms:
# Using Crystal Reports 10 with outer joins produces syntax error
# Failed to open Rowset
# Syntax error (7587).
# Failed to open a rowset.
Details: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Provider for ODBC Drivers
Description: [DataDirect-Technologies][ODBC Progress driver][PROGRESS]Syntax error (7587)
SQL State: 37000
Native Error: -20003


Cause:
When running Progress 9.x with a PGPro driver, the syntax is PlusEqual.


Fixes:
1. Backup the Registry.

2. Create a text file called "OracleJoinBuilder.txt".

3. Copy the following text into the text file:

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USER\Software\Crystal Decisions\10.0\Crystal Reports\Database\QueryBuilder\JoinBuilder]
"DB2JoinBuilder"="PGPRO915"
"OracleJoinBuilder"="PGPRO915"

4. Rename the text file (TXT) file with the Registry Key file name extension (REG). For example, if the text file name is “Joinbuilder.txt”, then rename it to “Joinbuilder.reg”. Or, if the text file name is “Oraclejoinbuilder.txt”, then rename it to “Oraclejoinbuilder.reg”.

5. Double-click the REG file to enter the updated Registry Key setting.

Upon completing the above steps, the Registry Key setting on the computer will be updated to include the correct Left-Outer-Join SQL syntax.

NOTE:
The PGPRO** are the SQL-92 drivers. The name changes depending on the driver Version. Please verify which ODBC driver is used by going to the "Drivers" tab of the ODBC Data Source Administrator. At the time of this writing, the possible settings are as follows:

Progress 9.1D / Merant 3.60 = PGPRO915
Progress 9.1D / DataDirect 4.1 = PGPRO918
Progress 9.1E / DataDirect 4.1 = PGPRO918
OpenEdge 10.0x / DataDirect 4.2 = PGPRO1019


Notes:
References to Written Documentation

Progress Solutions:
P70903: Is it possible to adjust how Crystal Reports 10 generates OUTER JOIN syntax via the Registry?

Crystal Articles:
How to resolve the join syntax errors in Crystal Reports
This article applies to both Crystal Reports 9 and Crystal Reports 10

There seems to be something rather odd going on with the text formatting in my reply. Sorry about that.

 

[vdennis]

Something else to look at. While Progress will support any char length regardless of how the 'display' is formatted (i.e. format "X(10)"), CR will not and you can get the same type of error. So be sure that none of your fields are longer than reported by CR (Usuall 2 times the size reported by Progress.) Also, in some cases, in using the left outer join, ALL tables may need to be set that way. Try it and see if that helps.

 

[zollinme]

I'm using Progress 91D with OpenLink Generic ODBC 5.10 driver and Crystal Reports 10 and XI.

When I use DB2JoinBuilder=olod5032, inner joins work correctly but when I try to use left outer joins Crystal generates incorrect SQL syntax. Instead of the words left outer join, a parenthesis plus sign parenthesis (+) is used. Here's an example.

SELECT A1.field1, B1.field2, C1.field3, D1.field4
FROM pro1.D D1, pro1.B B1, pro1.A A1, pro1.C C1
WHERE (D1.field1=B1.field1 (+)) AND (D1.field1=A1.field1 (+)) AND (D1.field1=C1.field1 (+))

Does anyone know what registry key I can use for Progress 91D, OpenLink and Crystal Reports for left outer joins?

Thanks in advance.

 

[vdennis]

There are know issues with left outer joins and Progress 9.1. Have you check the KB on this? Also, have you tried using the ODBC driver that came with Progress?

-Dennis-

 

[zollinme]

I have searched the knowledge base many, many times. I can't be the only person with this issue because I found this article.

Title: Left outer join created by Crystal Reports contains...
Answer: Certain Left Outer Join generated by Crystal Reports fails.
Location: http://progress.atgnow.com/esprogress/Group.jsp?bgroup=progress&id=P50484

The fix here is to update to 9.1D07 and modify the SQL. I checked what version I was using. It's 9.1D09 and since I'm using Crystal 10 and XI, I can't edit the generated SQL.

I want to post this link too. It might help someone else. Here's the official documentation from Crystal, "How to resolve join syntax errors in Crystal Reports 10".

http://support.businessobjects.com/communitycs/technicalpapers/cr10_left_outer_join.pdf

The same information will work for Crystal XI but the registry structure has changed from HKEY_CURRENT_USER\Software\Crystal Decisions\10.0\Crystal Reports\Database\QueryBuilder\JoinBuilder to HKEY_CURRENT_USER\software\Business Objects\Suite 11.0\Crystal Reports\Database\QueryBuilder\JoinBuilder.

Also, here is another good reference I found about the correct registry key to use.
http://aspadvice.com/blogs/crystal/archive/2004/03/22/2185.aspx

I'm still looking for the correct registry settings for Progress 91D left outer joins.

Megan