A recent upgrade to Crystal Reports XI (CRXI) showed, yet again, why I’m never happy with the Crystal Reports system. We’re largely stuck with it though because of its inclusion in so many custom and commercial .NET apps.

Crystal Reports XI - “Failed to retrieve data from the database” when using :
Following the upgrade from Crystal Reports 10 (CR10) to CRXI existing reports ran correctly, but opening some of them to make minor updates yielded fun “Failed to retreive data from the database” errors. Thankfully we’ve waited as long as possible to upgrade from CR10 and there are already hotfixes for CRXI to solve this. The error occurs in the Crystal Reports designer when adding a JDBC table. This error affects both Crystal Reports XI and BusinessObjects Enterprise XI when attached to a MySQL database.

Crystal Reports XI and MyODBC 3.51 - LEFT JOIN Errors:
We’re still using the MyODBC 2.50 (aka: MySQL Connector/ODBC) driver on any machine that runs or designs reports from MySQL-stored data. Why use such an old driver?

Version 3.51 of MyODBC apparently handles LEFT JOIN differently from other ODBC drivers (I’m not saying it’s wrong either, it could be the one driver that handles things according to standards). To fix the problem it’s necessary to change a registry key that changes how Crystal Reports sequences the query in its Query Builder - here is the registry key change to fix LEFT JOIN for Crystal Reports versions 8 - XI. The problem with making this change in the registry is that it happens to break your ability to connect to other databases, including Access and SQL Server, which we also run. While it would be nice to only run one database our vendors have other ideas, so we’re running MyODBC 2.50 - LEFT JOINS work correctly, the registry key doesn’t need to be changed, and we can still see all those other databases.

Additional problems with older versions of Crystal Reports and MyODBC 3.51 driver:
LEFT JOINs aren’t the only errors resolved by MyODBC 2.50. …When Crystal Reports 9 was first released we were already using the MyODBC 3.51 driver to connect to MySQL, but there were a number of issues in CR9 where tables weren’t listed in the designer and positive numbers were returned as negatives. Crystal Reports support was even slower than normal with any results (no excuse, but his was around the time they were acquired by Business Objects). After mistakenly using an older image to rebuild a development box we noticed that the MyODBC 2.50 driver on didn’t have those errors - and have been using it ever since. Crystal finally got around to issuing hotfixes for those issues in CR9 some time after Crystal Reports 10 was released - only to find that CR10 had the same problem of returning negative numbers via MyODBC 3.51.

Comment on this post below

You can leave a response, or trackback from your own site.