Symptom
- Error: "Failed to retrieve data from the database..." when executing some reports.
- When refreshing some reports connecting to DB2 via ODBC in Crystal Reports, it fails with the error:
"Failed to retrieve data from the database.
Details: 08S01:[DataDirect][ODBC Shadow driver]Socket closed."
Environment
- SAP Crystal Reports 2020
- IBM DB2
Reproducing the Issue
- In Microsoft ODBC Data Source Administrator, create an ODBC DSN to IBM DB2 using the Shadow client ODBC driver.
- In Crystal Reports, create a new report connecting to DB2 via ODBC
- Add multiple Tables to the report, and link it.
- Add many Database Fields to the report.
- Refresh the report, and it fails with the error:
"Failed to retrieve data from the database… [ODBC Shadow driver]Socket closed."
Cause
- All reports that are failing have a large SQL Query, and uses the DataDirect Shadow Client ODBC driver to connect to IBM DB2.
- Reports with large SQL Query fails because the DataDirect Shadow Client ODBC have a limited buffer size.
- DataDirect Shadow Client ODBC is not a supported ODBC driver to connect to IBM DB2 from Crystal Reports.
- For reference, see the supported platforms document for Crystal Reports: 2859510
Resolution
- Use a supported ODBC driver to connect to IBM DB2 from Crystal Reports, like the CR DB2 Wire Protocol ODBC Driver:
-
In Microsoft Data Source Administrator, rename the current ODBC DSN the report uses to connect to IBM DB2:
-
In MS Windows, in the search bar, type: ODBCAD
-
In the list of application displayed, right click on: ODBC Data Sources (64bit), and select: "Run as Administrator"
-
In "ODBC Data Source Administrator (64bit)", select the tab: "System DSN"
-
In the System Data Sources list, select the ODBC DSN the report uses to connect, and click the button: "Configure..."
-
In ODBC DSN, rename the Data Source Name, and add to the end of the DSN, something like: _OLD
-
Click "OK" to accept the change.
-
-
Create a new ODBC DSN to IBM DB2 using a supported ODBC driver, like: CR DB2 Wire Protocol ODBC Driver, or the IBM ODBC driver:
-
Still in the window "ODBC Data Source Administrator (64bit)", under the tab "System DSN", click the button: "Add..."
-
In the window: "Create New Data Source", select the ODBC Driver: "CR DB2 Wire Protocol ODBC Driver", or the IBM ODBC Driver, and click "Finish"
Notes:
The CR DB2 Wire Protocol ODBC Driver is installed by default with Crystal Reports, but if it is not installed, you can install it by modifying the installation of Crystal Reports, by going to the Add / Remove Programs in MS Windows. For more details, see the SAP Knowledge Base Article: 1218152 -
In the window "ODBC DB2 Wire Protocol Driver Setup", under the tab "General", enter the Data Source Name the report is using.
-
Then enter the information for your DB2 server and database. ( This information can be obtained from your DB2 Database Administrator. )
-
At the bottom left corner of the window, click the button "Test Connect"
-
Enter the database user name, and password to confirm it connects successfully to your IBM DB2 database.
-
Once you confirm it is connecting to the IBM DB2 database successfully, click: "OK"
-
-
In Crystal Reports, refresh the report based on IBM DB2
-
Keywords
CR, IBMDB2, ShadowODBC , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem