I am using CR 9.0 after an upgrade from CR 8.5 with an ODBC connection to a Thoroughbred database with a Thoroughbred ODBC driver. I realize this is not a supported database...but I have been using CR against this database since CR version 5.0 with no problems.
I have reports that were created in CR 8.5 against these databases with three or more tables linked with = joins that fail to run in CR version 9. When I run them I get this error:
“Failed to Return Rowset” Details 42000:[Thoroughbred Software][TS ODBC DataServer]Expected lexical element not found.
When I run them in CR 8.x - they run fine. When I try to refresh a report created in CR 8.x with three or more tables in v 9 I get the message "Database has changed, proceeding to fix up the database OK". Nothing has
changed in the database. I get that message for every table in the report. If I link only TWO tables in CR 9.0 I get the same error but if I change the link between the TWO tables to a left outer join, the report runs with no error. I don't want to use a left outer join, however, I
want an equal join. If I use the left outer join on three tables, the report still fails.
In addition, I conducted an experiment and reviewed the SQL statement for a report created in 8.x with the same report opened in version 9. Here is the SQL statement for the report created in 8.x sql stmt cr 8
SELECT
POCPOHF."PO_NUMB", POCPOHF."VENDOR_CODE", POCPOHF."DATE_ORD",
POCPODF."PO_LINE_NUMB", POCPODF."ITEM_CODE", POCPODF."QTY_ORD",
PODPOIH."APPROV_NAME", PODPOIH."FOB_CODE"
FROM
{ oj ("POCPOHF" POCPOHF INNER JOIN "PODPOIH" PODPOIH ON
POCPOHF."PO_NUMB" = PODPOIH."PO_NUMB"
INNER JOIN "POCPODF" POCPODF ON
POCPOHF."PO_NUMB" = POCPODF."PO_NUMB"}
ORDER BY
POCPOHF."PO_NUMB" ASC
Here is the statement for the same report when opened in version 9
sql stmt cr 9
SELECT "POCPOHF"."PO_NUMB", "POCPOHF"."VENDOR_CODE", "POCPOHF"."DATE_ORD", "POCPODF"."PO_LINE_NUMB", "POCPODF"."ITEM_CODE", "POCPODF"."QTY_ORD", "PODPOIH"."APPROV_NAME", "PODPOIH"."FOB_CODE"
FROM ("POCPOHF" "POCPOHF" INNER JOIN "POCPODF"
"POCPODF" ON "POCPOHF"."PO_NUMB"="POCPODF"."PO_NUMB" INNER JOIN "PODPOIH" "PODPOIH" ON "POCPOHF"."PO_NUMB"="PODPOIH"."PO_NUMB"
ORDER BY "POCPOHF"."PO_NUMB"
Why are they different? Could this be contributing to the problem.
I have no problems accessing this data in MS Excel or in Crystal QRY. I am at my wits end not to mention I have client reports that suddenly will not run due to the upgrade.
Any suggestions would be appreciated. I have installed CR 9.0 SP1 and also Monthly hotfix for database and Report designer.
I have reports that were created in CR 8.5 against these databases with three or more tables linked with = joins that fail to run in CR version 9. When I run them I get this error:
“Failed to Return Rowset” Details 42000:[Thoroughbred Software][TS ODBC DataServer]Expected lexical element not found.
When I run them in CR 8.x - they run fine. When I try to refresh a report created in CR 8.x with three or more tables in v 9 I get the message "Database has changed, proceeding to fix up the database OK". Nothing has
changed in the database. I get that message for every table in the report. If I link only TWO tables in CR 9.0 I get the same error but if I change the link between the TWO tables to a left outer join, the report runs with no error. I don't want to use a left outer join, however, I
want an equal join. If I use the left outer join on three tables, the report still fails.
In addition, I conducted an experiment and reviewed the SQL statement for a report created in 8.x with the same report opened in version 9. Here is the SQL statement for the report created in 8.x sql stmt cr 8
SELECT
POCPOHF."PO_NUMB", POCPOHF."VENDOR_CODE", POCPOHF."DATE_ORD",
POCPODF."PO_LINE_NUMB", POCPODF."ITEM_CODE", POCPODF."QTY_ORD",
PODPOIH."APPROV_NAME", PODPOIH."FOB_CODE"
FROM
{ oj ("POCPOHF" POCPOHF INNER JOIN "PODPOIH" PODPOIH ON
POCPOHF."PO_NUMB" = PODPOIH."PO_NUMB"
INNER JOIN "POCPODF" POCPODF ON
POCPOHF."PO_NUMB" = POCPODF."PO_NUMB"}
ORDER BY
POCPOHF."PO_NUMB" ASC
Here is the statement for the same report when opened in version 9
sql stmt cr 9
SELECT "POCPOHF"."PO_NUMB", "POCPOHF"."VENDOR_CODE", "POCPOHF"."DATE_ORD", "POCPODF"."PO_LINE_NUMB", "POCPODF"."ITEM_CODE", "POCPODF"."QTY_ORD", "PODPOIH"."APPROV_NAME", "PODPOIH"."FOB_CODE"
FROM ("POCPOHF" "POCPOHF" INNER JOIN "POCPODF"
"POCPODF" ON "POCPOHF"."PO_NUMB"="POCPODF"."PO_NUMB"
INNER JOIN "PODPOIH" "PODPOIH" ON "POCPOHF"."PO_NUMB"="PODPOIH"."PO_NUMB"ORDER BY "POCPOHF"."PO_NUMB"
Why are they different? Could this be contributing to the problem.
I have no problems accessing this data in MS Excel or in Crystal QRY. I am at my wits end not to mention I have client reports that suddenly will not run due to the upgrade.
Any suggestions would be appreciated. I have installed CR 9.0 SP1 and also Monthly hotfix for database and Report designer.