4,707 questions
This is a Crystal Reports issue.
Please see:
https://answers.sap.com/questions/8779045/server-native-client-100-incorrect-syntex-datetime.html
MSOLEDBSQL (v18) driver and stored procedure calls with DateTime parameter in crystal report.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(12.8, 53%, 11%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EGJ%3C/text%3E%3C/svg%3E)
Gond, Janmejay
1
Reputation point
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.
Developer technologies | Transact-SQL
SQL Server | Other
14,501 questions
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(236.8, 76%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EEM%3C/text%3E%3C/svg%3E)
EchoLiu-MSFT 14,621 Reputation points2021-01-05T09:02:34.83+00:00 Hi @Gond, Janmejay ，
Your question involves sql-server-general, I have added the sql-server-general tag for you, and the engineers there will provide more professional advice.
Echo
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(96, 26%, 19%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EYK%3C/text%3E%3C/svg%3E)
Yitzhak Khabinsky 26,586 Reputation points2021-01-05T14:35:13.617+00:00 Please share your stored procedure source code.
Sign in to comment
3 answers
Sort by: Most helpful
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
Tom Phillips 17,771 Reputation points2021-01-05T20:16:23.207+00:00 -
Gond, Janmejay 1 Reputation point
2021-01-06T04:30:55.027+00:00 Hi,
Agree with you, the reported issue is same as mine but Its a old issue something related to CR2008 but I'm already using latest version of crystal report.
-
Tom Phillips 17,771 Reputation points
2021-01-06T16:45:31.847+00:00 The fix is the same regardless of CR version.
Sign in to comment -
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator2021-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.
-
Gond, Janmejay 1 Reputation point
2021-01-06T04:16:35.583+00:00 Hi,
Please see the following profiler trace captured for both derivers-
With MSOLEDBSQL Driver: Failing
exec "Procedure Name" 0, CONVERT(DATETIME, '2021-01-04 03:31:48', 120), CONVERT(DATETIME, '2021-01-04 03:31:48', 120),NULL, NULL, NULL, NULL, NULL, NULL, NULLWith SQLNCLI11 Driver: Working
exec "Procedure Name" 0, {ts, '2021-01-04 03:31:48'}, {ts, '2021-01-04 03:31:48'},NULL, NULL, NULL, NULL, NULL, NULL, NULL -
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
2021-01-06T10:56:27.937+00:00 I assume these events are SQL:BatchStarting and not RPC:Starting?
What have you really said in Crystal?
What is happening here is that Crystal is calling the stored procedure by submitting an EXEC statement. That is not really the right way to do it. A procedure should be called from the client through RPC, Remote Procedure Call. With RPC the above cannot happen, because the client passes the procedure name and the parameters in binary format without syntax.
What I don't know is how Crystal got this idea in the first place. Was it given some piece of SQL syntax by you (in which case I don't want to accuse Crystal from not understanding it), or did you tell it that you really have a stored procedure?
-
Gond, Janmejay 1 Reputation point
2021-01-06T11:27:09.937+00:00 Thanks for your reply and yes, you are right these calls are SQL:BatchStarting. One thing I want to understand that why it is working with another SQL Driver (SQLNCLI11) with same flow.
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
2021-01-06T11:53:09.523+00:00 The EXEC Statement does not accept expressions for its parameters. The {ts] things counts as literals, which is why they work.
I'm not so much interested it was working with one provider and not with the other. I'm more interested in why they are not RPC calls, as in my opinion that is the root of the problem.
Sign in to comment -
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(108.80000000000001, 46%, 20%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAM%3C/text%3E%3C/svg%3E)
AmeliaGu-MSFT 14,006 Reputation points Microsoft External Staff2021-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.