SAP Knowledge Base Article - Public

1219721 - Error: 'Cannot obtain error message from server.' when refreshing a report based of a MS SQL Server Stored Procedure in Crystal Reports

Symptom

  • Database Connector Error: 'Cannot obtain error message from server.'
  • When refreshing a report in Crystal Reports, based of a MS SQL Server Stored Procedure, it fails.
  • When attempting to create a report of a MS SQL Server Stored Procedure in Crystal Reports, it fails with the errors:
      
        "Failed to retrieve data from the database" 
      
    Followed by:
      
        "Database Connector Error: Cannot obtain error message from server"

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
  • SAP Crystal Reports 2020
           
  • MS SQL Server  

Reproducing the Issue

  1. In Crystal Reports, open or create a report based of a specific MS SQL Server Stored Procedure.
  2. When refreshing the report, it fails with the Database Connector Error: 'Cannot obtain error message from server.'

Cause

  • This error message appears because the MS SQL Server Stored Procedure returns an invalid result set.
           
    For example:
       
    A MS SQL Server Stored Procedure that contains a conditional EXIT() statement will not return a valid result set if the condition is met, like:

         Myval = false
         IF myval = true THEN
         SELECT * FROM MyTable
         ELSE
         EXIT()
       
    A Stored Procedure when used with reporting application like Crystal Reports always need to return the same and valid result set. The result set can be empty, but it must have a valid structure, like:
       
         Myval = false
         IF myval = true THEN
         SELECT * FROM MyTable
         ELSE
         SELECT * FROM MyTable WHERE 1 = 0
      
    In the modified sample code above, the WHERE clause will not be true for any records, so an empty result set with a valid structure will be returned.

Resolution

  • To be able to report of the MS SQL Server Stored Procedure:
         
    1. Update the Stored Procedure code to always returns the same valid structure;
            
    2. Ensure the Stored Procedure meets all the Stored Procedure Requirements to be used in Crystal Reports as listed in the SAP Knowledge Base Article: 1892878

Keywords

SQL RESULTSET DATASET RECORDSET DATASOURCE SP PROC ZERO BusinessObjects Enterprise InfoView Stored procedures , c2018913, CR , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem

Product

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