cancel
Showing results for 
Search instead for 
Did you mean: 

Still unable to pass DateTime parameters with "MS OLE DB Driver 19 for SQL Server" (MSOLEDBSQL19)

0 Kudos
400

This got already asked once, but unfortunately it didn't receive a proper solution yet. I just tried it again with the latest SP36 and the latest version MSOLEDBSQL19. The problem still exists!

If my store procedure has a Date or Datetime Parameter, the report fails with a Syntax error when using "MS OLE DB Driver 19 for SQL Server" (MSOLEDBSQL19).

With "MS OLE DB Driver for SQL Server" (MSOLEDBSQL) the generated query is:

"DBNAME"."dbo"."STORED_PROCEDURE_NAME";1 {d '2023-02-14'}, {d '2023-02-14'}

with "MS OLE DB Driver 19 for SQL Server" (MSOLEDBSQL19) the generated query is:

"DBNAME"."dbo"."STORED_PROCEDURE_NAME";1 CONVERT(DATETIME, '02-14-2023', 110), CONVERT(DATETIME, '02-14-2023', 110)

The second query contains a syntax error. Does anyone know how to get it to format the query like the first way while using the newer driver?

And before anyone suggests it, staying on the older drivers is not an acceptable solution. We have rolled back to "MS OLE DB Driver for SQL Server" (MSOLEDBSQL) as a temporary work around while we look for the permanent fix for all our existing reports. Sooner or later we have to go to MSOLEDBSQL19, since only there you get the latest security features like TDS 8.0. Therefore you currently can't force "Strict Encryption" in SQL Server.

View Entire Topic
DonWilliams
Active Contributor
0 Kudos

I SAID TEST IT USING ODBC 19 to SEE if it works.

If it works ask Microsoft to fix their OLE DB 19 client to generate the same SQL as ODBC 18.

In CR 9 and since R&D stopped building in "hacks" to "fix" third party Client issues, if there is a problem with MS's OLE DB 19 client MS has to fix it.

And Yes if you test updating one report and redirecting CR to use the new ODBC 19 Client it's the ONLY way to fix all of your reports.

0 Kudos
As I already said: Tests with Microsoft ODBC Driver 18 for SQL Server were successful (and that report did not need any modification). It creates a command like this: "DBNAME"."dbo"."STORED_PROCEDURE_NAME";1 '02-14-2023'
0 Kudos
However, as an outsider, I don't understand why Microsoft and not SAP CR should fix anything. According to SQL Profiler, the same metadata queries are first executed by CR runtime with both OLE DB drivers (i.e. version 18 and 19), with the same results. But CR runtime then generates an invalid SQL statement to query the data only with version 19. Does CR runtime or the OLE DB driver generate this invalid query? I would guess the CR runtime. As you said, the CR runtime generates the generic SQL syntax, which is not valid for SQL Server.