Several recent projects have had me creating and automating data processes using Crystal Reports and some related tools.   One key tool is the ODBC Export provided by Crystal Reports.  To a Crystal user ODBC is normally used to pull data out of the database.  But the ODBC export option allows you to export the report data back into an ODBC connection and create a new table in your database.  This may sound dangerous, but Crystal does not allow the export to take place if that table name already exists in the database.  This prevents you from accidentally damaging your data.

But in working with  a customer recently I found a pretty serious bug in this export.  I wanted to add a new calculated column to a table so I created a report reading the existing table, added the new calculated column, and then exported that to a new table.  It ran fine and the new column was correct, but one of the existing date columns was largely empty.  Roughly two thirds of the dates in this column were Null, even though the column was 99% complete in the original data.  All the other columns were fine (including another date column) and even a large chunk of the faulty column was still fine.  But after nearly an hour of experiments, nothing I tried would create a complete column from that one field.

So I compared the records that were losing their dates to those that weren’t and could see no pattern.  I brought the file to my own PC and tested a different version of CR and even a different ODBC database target.  I still got the exact same result.   I finally discovered the problem when I tried sorting the data.  I noticed that if I exported from an ascending sort I got the exact reverse of the result of a descending sort.  That told me that the problem was probably triggered by a specific record.  And when I checked that specific record I found a Null date.  Apparently when an ODBC export encounters a Null date value in a column, it not only exports that record’s value as a Null, but exports null values from that point on in that column.   When I filtered out Null values in the selection formula the export was fine.

So I created my own small table in MS Access and put in a single null value out of 10 in a DateTime column.  When I exported to ODBC I got the same behavior.  With further tests I confirmed that:

1) It only affects DateTime fields (not numbers or strings)
2) It happens for ODBC exports that go to SQL Server 2005, SQL Server 2008 and MS Access so it likely affects all databases.
3) It happens for reports reading both SQL Server (ODBC) and MS Access (DAO) data, so it likely affects any data source.
4) It happens in CR versions 8.5,  10 and 12, so it probably happens in all versions of CR.

If anyone sees a different behavior please let me know.