Symptom
- Error when refreshing reports based on MS SQL Server Stored Procedure with Date Parameters.
- When refreshing reports based on MS SQL Server Stored Procedure with Date Time Parameters in Crystal Reports using an OLEDB Connection, it fails with the error:
Database Connector Error: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Driver 19 for SQL Server
Description: Incorrect syntax near the keyword 'CONVERT'
SQL State: 42000
Native Error: 156 [Database Vendor Code: 156]
Environment
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
- MS SQL Server 2017
- MS SQL Server 2019
Reproducing the Issue
- In Crystal Reports, create a report based on a Microsoft OLE DB Driver for SQL Server 19
- Select a MS SQL Server Stored Procedure that have at least one Date Time Parameter.
- Refresh the report, and after entering the date time prompt value, it fails with the error:
Database Connector Error: ADO Error Code: 0x80040e14
Source: Microsoft OLE DB Driver 19 for SQL Server
Description: Incorrect syntax near the keyword 'CONVERT'
SQL State: 42000
Native Error: 156 [Database Vendor Code: 156]
Cause
- The Microsoft OLE DB Driver for SQL Server 19 is not supported, and a default SQL for the date value is sent to the MS SQL Server Stored Procedure with Date Paramete, like:
StoredProcedureName(CONVERT(DATETIME, '2023-01-02 12:34:00', 120))
When MS SQL Server is expecting the following when passing a date value to the Stored Procedure:
StoredProcedureName({ts '2023-01-02 12:34:00'})
Resolution
- The successfully refresh reports based on MS SQL Server Stored Procedure with Date Time Parameter, connect to MS SQL Server 2017, 2019 using the supported driver for the connection type:
- For OLEDB Connection, use the Microsoft OLEDB Driver 18 for SQL Server
- For ODBC Connection, use the Microsoft ODBC Driver 17 for SQL Server
- For reference, and more details, see the Crystal Reports Supported platforms documents:
2859510 - Supported Platforms documents for Crystal Reports
- To download the supported Microsoft SQL Server drivers, go on the Microsoft website, and search for the specific OLEDB or ODBC driver to download.
Keywords
CR, SQL Server, OLE DB , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
Product
SAP Crystal Reports 2016 ; SAP Crystal Reports 2020