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.

As I’ve griped about before: two databases on one server is a bad idea. Virtualization and watered-down heavy iron hardware make for exceptions to this, but it’s a setup to use tons of RAM and throttle specific OS systems if you run multiple instances on one server.

All that being said - Microsoft has a new upgrade path for SQL Server 2005. MSDN says we can “install SQL Server 2005 side by side with SQL Server 2000 or SQL Server 7.0″ and then synchronize the data between the two instances. Users can then be switched to the new database or gradually transferred to it during testing and backup processes.

The MSDE, Microsoft SQL Desktop Engine, is a stripped down version of SQL Server used as a backend in a number of software packages (some major examples: Symantec Backup Exec, McAfee’s ePolicy Orchestrator management console, and Microsoft’s own Small Business Accounting package). With a license for free re-distribution and the data storage and T-SQL power of SQL Server the MSDE 2000 is likely to be increasingly common as applications manage increasing amounts of data and with development cycles leaving a good amount of time before SQL Server Express 2005, the MSDE 2000 replacement, will be included. Unfortunately the MSDE is so stripped down that there isn’t either a managment or programming interface included - not a good thing with a complex Microsoft program that wasn’t originally built with a command line interface.

Previous recommendations for managing MSDE used Access as a programming front-end and the Osql command line utility to configure users, permissions, and light management. Now, with SQL Express 2005 and SQL Server Management Studio Express available for free download, all you need to manage MSDE is the instance-name of your MSDE engine and these connection instructions for the SQL Management Studio. To find the MSDE instance name, open the Services control panel on your workstatoin or server and find a service starting with “MSSQL$” - everything after the “$” is the instance name.

Using SQL Express saves you from the limitations of Osql, but here is information on managing MSDE via the Osql utility.

Using the “SELECT TOP” pharse in a query on SQL Server 2005 can take a very long time to return results when the query sorts on a column with no index. In those instances, instead of SELECT TOP, use the deprecated “SET ROWCOUNT” to return results in a reasonable time. Originally discovered by Davide Mauri, he has a SQL test script available and also notes that the bug won’t be fixed in the first MS-SQL 2005 production release, but in the first service pack.

It’s not a surprise that there are some strange bugs in MS-SQL 2005, but I am surprised that Microsoft will wait until the first service pack to patch them. Let’s hope that the service pack comes soon after the database server goes on sale.

Arthur Fuller is a well-known SQL guru and developer, but his system administration skills don’t reflect enough respect of his server. In this account of a bad SQL Server 2005 Express install he foo-bars his server by installing SQL Server 2005 Express on the same box that runs SQL Server 2000.

Database servers, the programs that provide the database functionality, don’t share resources well. These programs aren’t related to simple file-based Access or Borland databases. Those file-based programs are glorified text files and don’t require the resources that ACID compliant databases do. To be and ACID database requires real time access to server memory, logs files, and OS resources. By running two databases on the same system the most basic requests for resources from the OS can conflict, causing greater queueing and resource demands than splitting the instances to two equivalent servers, which drive up processor load and dramatically impact performance.

From experience, running both SQL Server and MySQL on the same development system, with no active users, does cause delays in both programs. That being said, multiple SQL Server versions on the same system definitely cause conflicts - something to remember if you’re going to demo MSSQL 2005 (virtualized OS’s via VMWare or Microsoft Virtualization Server are a different story, but if you’re smart enough to install enough RAM to support them then you can make up your own mind on running multiple SQL Servers).

Carry this one step further and try to keep Exchange, which uses its own databases, any other databases (Advantage, MySQL, Oracle, etc.), and any beta software on separate system and never have to recover from Mr. Fuller’s problem.

Update: “You can also install SQL Server 2005 side by side with SQL Server 2000 or SQL Server 7.0″ according to Microsoft MSDN. It “must” be OK if Microsoft says it will work, just remember that RAM use could double once that second instance starts allowing connections. (added 11/17/2005)