cancel
Showing results for 
Search instead for 
Did you mean: 

SQL2008 Date type field giving my Crystal Reports XI R2 some heartburn...

Former Member
0 Kudos

Hello everyone.

I am attempting to change a database connection from RedBrick to SQL2008 in a series of reports. I have the SQL Native Client 10 driver installed on my Development PC.

I open a report in Crystal Reports XI R2 and go into "Set Datasource Location". I then go to "Create New Connection", choose "OLE DB (ADO) and then select "SQL Server Native Client 10.0". I provide my appropriate credentials and am able to highlight the old RedBrick and the new SQL2008 database tables and update to the new SQL2008 database. I am then able get the report to run off of the SQL2008 server successfully.

I can save the report, close it, then open it and run it again successfully without closing Crystal Reports XI R2.

But, If I close and then reopen Crystal Reports XI R2, and then open my saved report, it seems to have some heartburn about field mapping. It looks like my "date" type fields fromt SQL2008 are now being interpreted as strings. I suspect that my SQL Server Native Client 10.0 driver connection is getting lost when I close Crystal Reports XI R2 and it seems like it is defaulting to some my older SQL driver.

Per some recommendations I have found in other posts on this subject, I have tracked down SP 6 and have installed it. I could not find Fix Pack 6.2 (also recommended), even though my employer does have a support agreement with SAP/BO.

After the SP6 installation, I continue to experience the same issues with newly modified reports working during the session they are modified, then not working when I close and then open another session of Crystal Reports XI R2.

Also, after uploading the modified report to our BO XI server (which has SQL Server Native Client 10.0 installed), they continue to have an issue with "date" type fields being interpreted as strings.

Any assistance with this would be very much appreciated!

Thanks in advance!

Matt

Accepted Solutions (0)

Answers (2)

Answers (2)

0 Kudos

Install Crystal Reports XI R2 SP 6 and Fix Pack 6.4 and then the Native 10 client will work. Ms changed the name of their driver so CR doesn't recognise it until you apply the patches.

Former Member
0 Kudos

Following from [Problem with SQL date type field|;

Matt,

The issue you're dealing with is a little quirky thing with CR when it comes to the field mappings when you use Set Datasource Location. I first found it when I was moving a report from a SQL Server 2000 to a SQL Server 2005 database. The developers had changed one of the string fields from a VarChar(1000) field to a VarChar(MAX) field.

Turns out that VarChar(1000) is seen as a String[1000] field and a VarChar(MAX) is seen as memo. Sooo... Even though both fields had the same name, CR wasn't able to map the fields correctly.

At some point in the process you should have been presented with the "Map Fields" dialog box. It's telling you that it couldn't correctly map a field. The problem is that it's not giving you any options to choose form... Turns out that you have to uncheck "Match type" to see the the new field(s).

Here's a screen shot. [Map Fields Dialog Box.doc|https://docs.google.com/document/edit?id=19YwoYkFuOMgX3c_xY_CC84ILKzzo4AWzXFkahkJkmWg&hl=en&authkey=CI6OzP4K#]

Once yo have the fields mapped correctly, you shouldn't have a problem. CR will now be expecting a string field and not a date.

If you continue to have a problem getting the field mapped follow these steps:

1) Make a copy of your report. (Working off the original copy is just an invitation for tragic thing to happen)

2) Delete ALL references to the field in your report. You can use // marks to comment out entire formulas if needed.

3) Run through the whole Set Datasource Location thing again.

4) Make sure the report runs properly (allowing for the fact that the date field has been removed of course)

5) Add the Date field back to the report.

HTH,

Jason