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
- In Crystal Reports, open or create a report based of a specific MS SQL Server Stored Procedure.
- 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:
- Update the Stored Procedure code to always returns the same valid structure;
- 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