1377026 - Crystal Reports using a Stored Procedure with a date parameter generates incorrect SQL

SAP Knowledge Base Article - Public

1377026 - Crystal Reports using a Stored Procedure with a date parameter generates incorrect SQL

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

  1. Create a new report using a stored procedure with a date parameter.
  2. 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 BusinessViews Manager , BI-DEV-NET , BI Software Development Kits (SDKs) - .NET or Other , Problem

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1