Symptom
- Reports created off of a Stored Procedure will generate a SQL or Database Connector error.
- Using stored procedure with a date parameter results in incorrect SQL syntax.
Environment
- Crystal Reports 2008
- MS SQL Server 2008
- Oracle 9i and above
Reproducing the Issue
- Create a new report using a stored procedure with a date parameter.
- Run the report and it will generate an error.
Cause
- Crystal Reports 2008 is passing the SQL as "CONVERT(DATETIME, '2009-07- 01 00:00:00', 120)" which is invalid for a stored procedure parameter.
- The issue has been identified and logged under Problem Report ID ADAPT01285561.
- The change was introduced in ADAPT01120372 - Oracle 9i client (Native) connection in CR XIR2 fails to verify database when a the main report and sub report connect to stored procedures
Resolution
- Use the MS SQL Server ODBC database driver instead of OLE DB SQL.
- Alter each reports Report Option - ‘Verify Stored Procedures on First Refresh’ and check this option off
The above solution will work and we are currently looking into resolving the issue with - ADAPT01648036 Parameters are passed in the wrong order
Keywords
Crystal Reports 2008, ms sql server 2008, ms sql ole db native 10, datetime, CONVERT , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem
Product
Crystal Reports 2008 V0 ; Crystal Reports 2008 V1