Hi,

When running Crystal Reports XI, using DBISAM ODBC (on v3.30 data
tables) I get the following Error:

[Crystal]
Failed to retrieve data from database
Details: HY000: [Elevate Software][DBISAM] DBIsam Engine Error 11949 SQL
error - End of SELECT Statement expected, but instead found '.'
[Database Vendor Code 11949]

Searching thru the ElevateSoft support database, I found the following
reply from Tim to someone that was getting the same error on 7/18/2006:

>This is an issue with the new Crystal and the path separator used in
>catalog names.  It appears that they've broken the catalog name
>handling once again, so a fix will be needed from Crystal on this.

So my question is, does anybody know if this has been resolved, and if
there is a fix available from Business Objects?? Or a work-around?? (I
have installed the Crystal service pack 2, and the latest monthly fixes,
but still get the error).

Thanks!
Mark

Mark,

<< So my question is, does anybody know if this has been resolved, and if
there is a fix available from Business Objects?? Or a work-around?? (I have
installed the Crystal service pack 2, and the latest monthly fixes, but
still get the error). >>

This was the final response from Kevan regarding how he finally managed to
get it to work:

http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=14&msg=1253&page=1#msg1253

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> Mark,
>
> << So my question is, does anybody know if this has been resolved, and if
> there is a fix available from Business Objects?? Or a work-around?? (I have
> installed the Crystal service pack 2, and the latest monthly fixes, but
> still get the error). >>
>
> This was the final response from Kevan regarding how he finally managed to
> get it to work:
>
> http://www.elevatesoft.com/scripts/newsgrp.dll?action=openmsg&group=14&msg=1253&page=1#msg1253
>

Hi Tim,

It appears that the Kevan response you refer to above was in regard to
an earlier issue with Crystal Reports in Delphi2005Architect. His issue
a couple of months later looks like the same problem I am running into.

Sorry, I should have explained things better:

I'm using the standard Crystal Reports XI Pro product. I run the
'Standard Report Wizard' and create a new database connection, and
select the DBISAM ODBC driver/database. It lists the data tables, I
select the customer table (ARCusts) and a couple of fields. When I
finish that dialogue, and it starts working on the report it comes with
this error (when it is trying to verify the database):

The database table "ARCusts" cannot be found. Remove it from the report?

When I answer 'No' to that, it gets the following error:

Failed to retrieve data from database
Details: HY000: [Elevate Software][DBISAM] DBIsam Engine Error 11949 SQL
error - End of SELECT Statement expected, but instead found '.'
[Database Vendor Code 11949]

Here is the SQL that Crystal generated:

 SELECT "ARCusts"."CustAcct", "ARCusts"."CustomerName",
 "ARCusts"."City", "ARCusts"."State", "ARCusts"."Zip"
 FROM "Step1LiveData"."ARCusts" "ARCusts"

The problem is caused by the specific reference to the odbc database
"Step1LiveData" in the SQL. If I remove that and try that same SQL thru
the ODBC driver, it works fine. (I was not able to find any Crystal
Reports option anywhere to have it NOT include that database reference
in the SQL).

So is this the same problem you referred to below in your response to Kevan?
>This is an issue with the new Crystal and the path separator used in
>catalog names.  It appears that they've broken the catalog name
>handling once again, so a fix will be needed from Crystal on this.

I'm trying to understand what the problem is before talking to Business
Objects. If this is the same problem, at least I can go back to them and
point out that they fixed it in prior version(s), so hopefully they will
fix it again, or maybe they have some new option setting to avoid the
problem...

Thanks,
Mark

Mark,

<< Failed to retrieve data from database
Details: HY000: [Elevate Software][DBISAM] DBIsam Engine Error 11949 SQL
error - End of SELECT Statement expected, but instead found '.'
[Database Vendor Code 11949]

Here is the SQL that Crystal generated:

 SELECT "ARCusts"."CustAcct", "ARCusts"."CustomerName",
 "ARCusts"."City", "ARCusts"."State", "ARCusts"."Zip"
 FROM "Step1LiveData"."ARCusts" "ARCusts"

The problem is caused by the specific reference to the odbc database
"Step1LiveData" in the SQL. If I remove that and try that same SQL thru the
ODBC driver, it works fine. (I was not able to find any Crystal Reports
option anywhere to have it NOT include that database reference in the SQL).
>>

Yes, that's an issue that they have repeatedly re-introduced into Crystal
Reports with each new release.  There is a setting in the ODBC driver for
the catalog name separator, and the ODBC client application is supposed to
use a special function in order to query this information and determine how
to generate a proper catalog name in an SQL statement.  Unfortunately, they
have made it so that Crystal always uses the period and doesn't use the
backslash as DBISAM needs.  The workaround is to edit the SQL and make it
look like this:

 SELECT "ARCusts"."CustAcct", "ARCusts"."CustomerName",
 "ARCusts"."City", "ARCusts"."State", "ARCusts"."Zip"
 FROM "Step1LiveData"\"ARCusts" "ARCusts"

<< So is this the same problem you referred to below in your response to
Kevan? >>

Yes.

<< I'm trying to understand what the problem is before talking to Business
Objects. If this is the same problem, at least I can go back to them and
point out that they fixed it in prior version(s), so hopefully they will fix
it again, or maybe they have some new option setting to avoid the problem...
>>

Yes, they fixed it before, as evidenced here:

http://www.elevatesoft.com/bulletin_10.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Ok, I've opened up a support incident with Business Objects to see if I
can get this resolved. I was able to get a test case setup for them, so
they could replicate the error.

While doing that we discovered that it works just fine when using the
ODBC driver in Local mode! (It does not include a catalog/database name
in the generated SQL when running in Local mode). So the error only
occurs when using the ODBC driver in the Remote(Client/Server) mode,
when it inserts the catalog/database name into the generated SQL.

So I have two questions:
- Is it possible that their previous fix to CR v9 was only for the Local
mode? or was it definitely fixed when running in Remote mode as well?
- When running in Remote mode, would it work if they just didn't include
the catalog/database name in the generated SQL? or does it have to have
the catalog/database name in the SQL (with the '\' seperator) to work
properly with dbsrvr?

Thanks,
Mark

Tim Young [Elevate Software] wrote:
> Mark,
>
> Yes, that's an issue that they have repeatedly re-introduced into Crystal
> Reports with each new release.  There is a setting in the ODBC driver for
> the catalog name separator, and the ODBC client application is supposed to
> use a special function in order to query this information and determine how
> to generate a proper catalog name in an SQL statement.  Unfortunately, they
> have made it so that Crystal always uses the period and doesn't use the
> backslash as DBISAM needs.  The workaround is to edit the SQL and make it
> look like this:
>
>   SELECT "ARCusts"."CustAcct", "ARCusts"."CustomerName",
>   "ARCusts"."City", "ARCusts"."State", "ARCusts"."Zip"
>   FROM "Step1LiveData"\"ARCusts" "ARCusts"
>

Mark,

<< Ok, I've opened up a support incident with Business Objects to see if I
can get this resolved. I was able to get a test case setup for them, so they
could replicate the error. >>

Thanks.  If you get a positive result, then please let me know.  Kevan did
not get a positive result, but perhaps a little more complaining from
customers will get their attention.

<< - Is it possible that their previous fix to CR v9 was only for the Local
mode? or was it definitely fixed when running in Remote mode as well? >>

It was local also.  CR has generally had issues with backslashes as
path/name separators for tables since version 8.  Surprisingly, version 8
worked perfectly.

<< -  When running in Remote mode, would it work if they just didn't include
the catalog/database name in the generated SQL? >>

Sure, that would work fine provided that the table being selected from
didn't actually exist in another database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Ok, good news on this!...Bussiness Objects has fixed this bug in their
latest hotfix for Crystal Reports XI.
So now it uses the '\' for the catalog separator as it should.

I tested it out to make sure, and it works fine using the DBISAM ODBC
driver in either 'local' or 'remote' mode.


Tim Young [Elevate Software] wrote:
> Mark,
>
> << Ok, I've opened up a support incident with Business Objects to see if I
> can get this resolved. I was able to get a test case setup for them, so they
> could replicate the error. >>
>
> Thanks.  If you get a positive result, then please let me know.

Mark,

<< Ok, good news on this!...Bussiness Objects has fixed this bug in their
latest hotfix for Crystal Reports XI.
So now it uses the '\' for the catalog separator as it should.

I tested it out to make sure, and it works fine using the DBISAM ODBC
driver in either 'local' or 'remote' mode. >>

Cool, thanks very much for the update.

--
Tim Young
Elevate Software
www.elevatesoft.com