1574977 - How to add a SQL Query larger than 64KB in Command Object in Crystal Reports?

• Unable to enter large SQL Query in a Command Object.
• Error: 'Failed to retreive data from the database.' when attempting to add a large SQL Query to a report in Crystal Reports.
• How to add a SQL Query in a Command Object in Crystal Reports, when the size of the SQL Query is greater than 64 KB? 

• SAP Crystal Reports 2013
• SAP Crystal Reports 2016
• SAP Crystal Reports 2020

1. In Crystal Reports, create a new report off any relational data source.    
2. In the "Database Expert", under the database connection, double click on "Add Command"
3. In the window "Add Command To Report", enter a SQL Query that is larger than 64,000 characters:
   
      SELECT 'Field Name 1', 'Field Name 2',  'Field Name 3' ... 
      FROM 'Table 1'
      WHERE 'Field Name 1' = 'ABC' or 'Field Name 1' = 'DEF or  'Field Name 1' = 'GHI' 
       ...
      
4. After clicking "OK", it fails with an error like: 'Failed to retreive data from the database.'
        
   How to add a SQL Query larger than 64KB in Command Object in Crystal Reports?

• The number of characters limit that can be entered for a SQL Query in a Command Object is 64KB 

• If the size of the SQL Query exceeds 64KB, then it is suggested to create a Stored Procedure on the database side, then report off the Stored Procedure in Crystal Reports.
         
  Note: For information on how to write a Stored Procedure, contact your database administrator, or consult your database documentation.

CR, Command Object, SQL Query , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

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