SAP Knowledge Base Article - Public

2577449 - Error: 'Failed to execute query', when refreshing a report based of a Stored Procedure in Crystal Reports

Symptom

  • Unable to refresh a report.
  • Error when creating, or refreshing a report of a Stored Procedure.
  • When refreshing a report based of a Stored Procedure in Crystal Reports, or Crystal Reports for Enterprise, it fails with an error like:       
       
     "Failed to execute query."

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
         
  • SAP Crystal Reports for Enterprise 4.0
  • SAP Crystal Reports for Enterprise 4.1
  • SAP Crystal Reports for Enterprise 4.2
  • SAP Crystal Reports for Enterprise 4.3

Reproducing the Issue

  1. In a database, create a Stored Procedure that uses SQL commdands like Insert and Update.
  2. In Crystal Reports for Enterprise, create a report off the Stored Procedure.
  3. Insert at least one database field on the report.
  4. When refreshing the report, it fails with an error like: "Failed to execute query.", or cause the application to crash.

Cause

  • Crystal Reports, and Crystal Reports for Enterprise supports SQL Query Language in a Stored Procedure that retreive data from a data source like:
    • SELECT
    • FROM
    • WHERE
    • GROUP BY
    • ORDER BY
    • UNION   
               
  • But does not support SQL Query Language for data manipulation like:
    • CREATE TABLE
    • ALTER TABLE
    • DROP TABLE
    • INSERT
    • UPDATE
    • DELETE    
         
  • For reference, see the Stored Procedure section of Crystal Reports for Enterprise User Guide 

Resolution

  • Update the Stored Procedure to only use SQL Query Language that retreive data from a data source like:
    • SELECT
    • FROM
    • WHERE
    • GROUP BY
    • ORDER BY
    • UNION   

Keywords

CR, CRE, CR4E, CR4Ent, crystal, reports, for, enterprise, stored, procedure, sql, update, insert, delete, call, view, in, out, parameter, pointer , KBA , BI-RA-CRE , Crystal Reports for Enterprise , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020