Crystal converting string fields to number fields

[Former Member]

I'm attempting to add a SQL stored procedure (SQL Server 2005) to a new report using the Database Expert wizard. The procedure has fields defined as number, datetime and string. It adds into the report fine, but all the fields end up defined as numbers in VS. Dragging a datetime or string field onto the report workspace clearly shows them to be number fields. I cannot convert them to anything else. Why is Crystal overriding the datatype definition from my SQL result set and making every field a number?

I'm using Crystal Reports that is integrated with Visual Studio 2005 (Version 8.0.50727.42) with MS .NET Framework (Version 2.0.50727 SP2).

[former_member183750 · Accepted solution]

This is a rather strange behavior. See if doing a repair on .NET 2005 helps. Also, download SP 1 for CR 10.2 from here:

https://smpdl.sap-ag.de/~sapidp/012002523100006007872008E/crvs05sp1.exe

Ludek

[Former Member]

Thank you for your response. VS2005 and CR are up to date, but the problem persists. I left out of my original post some potentially important information. The report's new stored procedure resides in database A, but it is reading database B. Both databases are on the same server in the same SQL 2005 instance. Yes, there is a valid reason for this, but I won't get into that here.

With continued troubleshooting I discovered additional unusual behavior. I commented out the part of the script that read database B and added script that read database A to see if the problem occured reading either database. It does not. When the script reads database A it can be added to the CR report and all field datatypes appear as defined in the script. Restoring the script to read from database B caused the datatypes to appear as all numbers despite the script's datatype definitions.

I also dropped the new stored procedure from the report and added another stored procedure, one that reads from the new database, and all datatypes are correct. Whatever the problem is it appears to be related this new stored procedure and nothing else.

[former_member11696]

Hello,

I would be interested in seeing how you wrote your stored procedure? Crystal requires an IN/OUT Cursor type and we expect the last SELECT statement is what we get the results from.

If you can copy the Pubs sample database in SQL Server and then modify your SP to use those 2 databases it would help to determine if it is a DB driver issue in CR. We can then try to use your SP and our test DB.

Oh and also need to know what driver you are using, OLE DB, ODBC or their native driver?

Thank you

Don

Edited by: Don Williams on Jun 17, 2009 12:30 PM

[Former Member]

The SQL script was the problem and this is what happens when we leverage someone elses code.

The script creates a temp table and used an IF-THEN-ELSE construct to produce a result set from that temp table. If no rows found it performed a SELECT returning NULL AS <field name> as a single row to ensure some values were included in the result set, otherwise it performed a SELECT * for the temp table.

The solution was to insert a NULL row with the specific values needed into the temp table when no rows found. Then all I had to do was perform a SELECT * against the temp table outside the IF-THEN-ELSE construct and all is well. Lots of tinkering around to figure it out as I've never seen this before. Thx to all who contributed toward a solution.