MSOLEDBSQL (v18) driver and stored procedure calls with DateTime parameter in crystal report.

Gond, Janmejay 1 Reputation point
2021-01-05T04:39:36.427+00:00

Hello,

Having problem with MSOLEDBSQL driver. I am using Crystal reports to call SQL Server 2016/19 stored procedures.

When a stored procedure with Datetime parameter is called, it is putting the parameter as CONVERT(DATETIME, '2018-06-01 00:00:00', 120) and fails with Database vendor Error 156, Incorrect syntax near the keyword 'CONVERT'.

With the older SQLNCLI11 driver, the same parameter was being called as {ts '2018-06-01 00:00:00'} and worked perfectly fine.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,962 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,652 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Tom Phillips 17,736 Reputation points
    2021-01-05T20:16:23.207+00:00
    1 person found this answer helpful.

  2. Erland Sommarskog 112.7K Reputation points MVP
    2021-01-05T22:55:27.063+00:00

    I would use Profiler to see what is actually being sent to SQL Server. You will need to include the events SP:StmtStarting.

    I would guess that Crystal is doing something behind the scenes.


  3. AmeliaGu-MSFT 13,981 Reputation points Microsoft Vendor
    2021-01-06T03:01:18.497+00:00

    Hi @Gond, Janmejay ,

    It seems it is a known issue in Crystal Report. Please refer to using MSOLEDBSQL for Crystal Reports and 'Incorrect syntax near keyword CONVERT' for more details.
    And according to the above similar issue, it is recommended to use SQL Native client 11 in report.
    Best Regards,
    Amelia


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.