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 · Accepted solution]

What version of MS SQL Server are you using?

According to the CR Platforms info ODBC 19 is only supported in MS SQL 2022:

2019	ODBC	BI 4.3 SP01+	Microsoft ODBC Driver 17 for SQL Server
OLEDB	BI 4.3 SP01+	Microsoft OLEDB Driver 18 for SQL Server
JDBC	BI 4.3 SP01+	Microsoft JDBC Driver 8.2 for SQL Server
ODBC	BI 4.3 SP01+	DataDirect ODBC (10)
2022	ODBC	BI 4.3 SP04+	Microsoft ODBC Driver 18 for SQL Server
OLEDB	BI 4.3 SP04+	Microsoft OLEDB Driver 19 for SQL Server
JDBC	BI 4.3 SP04+	Microsoft JDBC Driver 12.2 for SQL Server
ODBC	BI 4.3 SP04+	DataDirect ODBC (10)

So what CR does is when the DB Client dll cannot be found what it does is default to a generic SQL Syntax, likely cause why the Syntax for the Parameter is different.

The other option is test using an ODBC connection, just as a test, to see if ODBC generates the correct syntax.

Note the patch level is the same for CR 2020 Designer as noted for BI 4.3

[fabian-eisenbart61 · Accepted solution]

This time I tested the issue with SQL Server 2022 and MS OLE DB Driver 19 for SQL Server (latest version 19.3.5).

[fabian-eisenbart61 · Accepted solution]

Last time it was most likey with SQL Server 2019. Same outcome.

[DonWilliams · Accepted solution]

Did you use the Database menu and set location using the new Client? Did ODBC work?

[fabian-eisenbart61 · Accepted solution]

Tests with Microsoft ODBC Driver 18 for SQL Server were sucessful. This creates acommand like this: "DBNAME"."dbo"."STORED_PROCEDURE_NAME";1 '02-14-2023'

[fabian-eisenbart61 · Accepted solution]

But is there any way to make MSOLEDBSQL19 work correctly?

[DonWilliams · Accepted solution]

PLEASE test with ODBC, it's not the first time MS OLE DB client has had problems and works differently than ODBC, it may be something Microsoft needs to fix.

There are CR ODBC registry keys one can create to change the Syntax generated by CR, but nothing for OLE DB

Upgrade to CR 2020 according to the Platforms info to be supported for ODBC 19.

And when updating the Report you MUST Open the Report, Set Location, select the new OLE DB Client and save the report. The SDK will NOT update the Client info saved in the RPT file, only way is to manually update each report in CR Designer.

[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.