SAP Knowledge Base Article - Public

2707503 - Error: 'Invalid cursor state. [Database Vendor Code: -99999 ]' when refreshing a report based off DB2 in Crystal Reports

Symptom

  • DB2 reports fails to refresh.
  • The same reports based off DB2 used to refresh successfully, before changing the DB2 configuration: CursorHold=0
  • When attempting to refresh reports connecting to an IBM DB2 data source in Crystal Reports, it fails with the error:

    Database Connector Error: '[IBM][CLI Driver]Invalid cursor state. [Database Vendor Code: -99999 ]'

Environment

  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
         
  • IBM DB2

Reproducing the Issue

  1. Set the IBM DB2 option from CursorHold=1, to 0
  2. In Crystal Reports, create a report off IBM DB2 data source.
  3. Add at least one field on the report.
  4. When refreshing the report, it fails with the error: 
        
    Database Connector Error: '[IBM][CLI Driver]Invalid cursor state. [Database Vendor Code: -99999 ]'

Cause

    • Crystal Reports require the cursor to be preserved, and it is failing because when CursorHold is set to zero, it does not preserve the cursor.
            
    • In more details: The IBM IBM DB2 option: CursorHold, controls the effect of a transaction completion on open cursors, and accept the following values:
         1 = SQL_CURSOR_HOLD_ON, the cursors are not destroyed when the transaction is committed (default).
         0 = SQL_CURSOR_HOLD_OFF, the cursors are destroyed when the transaction is committed.
          
      And as per IBM DB2 documentation, you can set the value to zero, only if the application used:
        
      1. Does not have behavior that is dependent on the SQL_CURSOR_COMMIT_BEHAVIOR or the SQL_CURSOR_ROLLBACK_BEHAVIOR information returned via SQLGetInfo(), and
          
      2. Does not require cursors to be preserved from one transaction to the next.
         
        Note: For more details, see the IBM DB2 documentation on the option: CursorHold.          

    Resolution

    • Set the IBM DB2 option: CursorHold to the default value of 1 ( CursorHold=1 )

    Keywords

    CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

    Product

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