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

[fabian-eisenbart61]

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.

[DonWilliams]

Please use the original post to update the request...

[fabian-eisenbart61]

Do I understand correctly that you are proposing to use the ODBC driver "Microsoft ODBC Driver 18 for SQL Server" instead of OLE DB "MS OLE DB Driver 19 for SQL Server" from now on? During the current tests, it was not necessary to update any report. The connection properties are set by code at runtime. This also worked for ODBC now. There would probably be too many reports to update them all.

[DonWilliams]

Test with ODBC 19, we know 18 works, to see if it generates the correct SQL. CR dynamically generates the Syntax based on the Client DLL used, which is why you can see differences between OLE DB and ODBC

[fabian-eisenbart61]

The first tests with the latest ODBC driver “”Microsoft ODBC Driver 18 for SQL Server” (only OLEDB already has version 19) have worked. We will therefore carry out further tests with it before possibly changing everything over. Many thanks for pointing out this possibility.

[DonWilliams]

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.

[fabian-eisenbart61]

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'

[fabian-eisenbart61]

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.