MySQL and Crystal Reports
A common problem MySQL users have with Crystal Reports 9 is the inability to browse tables and fields. While you can define a MySQL DSN, when you open it in Crystal Reports 9 you have no tables or fields in the field browser. This of course makes it very difficult to create a report.
Up until recently, the only two workarounds have been to either create the report in an older version of Crystal Reports and then open it in Crystal Reports 9, or use custom commands to return field data. Neither of these are particularly effective for creating reports.
This has been a known issue with Crystal Decisions and has now been solved. The full article is available in a knowledgebase article located at http://support.crystaldecisions.com/library/kbase/new_articles/c2013269.asp”
The short version is that you need to download this weekly hotfix: ftp://ftp.crystaldecisions.com/outgoing/EHF/CR90DBEXWIN_EN.ZIP
The other problem users often experience is that LEFT JOIN operations in Crystal Reports can be buggy. The fix for this is also simple. Simply add the following registry key:
[HKEY_CURRENT_USERSoftwareCrystal Decisions9.0Crystal ReportsDatabaseQueryBuilderOuterJoinEscSeq] “NoOuterJoinEscSeq”=”MYODBC3″
This should allow you to properly browse and utilize MySQL tables from within Crystal Reports.
The registry key on Crystal Reports 8 is:
[HKEY_CURRENT_USERSoftwareSeagate SoftwareCrystal ReportsDatabaseOptionsOuterJoin] “NoOuterJoinEscSeq”=”MYODBC3″
The registry key on Crystal Reports 10 is:
[HKEY_CURRENT_USERSoftwareCrystal Decisions10.0Crystal ReportsDatabaseQueryBuilderOuterJoinEscSeq] “NoOuterJoinEscSeq”=”MyODBC3″
November 28th, 2006 at 12:16 am
The disappearing table problem may have just reappeared in MySQL despite the above patch.
I have a development server running under VM on Windows Server 2003. I set it up a while ago and it has MySQL 5.0.15-nt-max installed. I am using Crystal 9 and the MySQL ODBC driver 3.51.12. I had no problems at all viewing tables etc.
We recently set up the production server (also running under VM) with the latest version of MySQL at the time 5.0.26-community-max. When I try to connect to this server I cannot see any tables.
I checked that permissions and system variables were identical on both servers and have granted all to root and used root in my ODBC setup.
I have checked a load of newsgroups and it seems Crystal solved this problem back in 2003, with a new version of crdb_odbc.dll, which I now have after installing the hotfix above. I can still see tables in our MySQL4 databases and the development MySQL5 database, just not the 5.0.26 live database.
So what has happened between 5.0.15 and 5.0.26??? or could it be something else, something to do with the server? I have run out of ideas, so am converting my reports to used stored procedures (most do anyway).