SAP Knowledge Base Article - Public

3335149 - Error: "Incorrect syntax near the keyword 'CONVERT'..." when refreshing reports based on a MS SQL Server Stored Procedure with Date Time Parameter in Crystal Reports

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

  1. In Crystal Reports, create a report based on a Microsoft OLE DB Driver for SQL Server 19
  2. Select a MS SQL Server Stored Procedure that have at least one Date Time Parameter.
  3. 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