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
412

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

What version of MS SQL Server are you using?

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

 

2019ODBC BI 4.3 SP01+Microsoft ODBC Driver 17 for SQL Server
OLEDB BI 4.3 SP01+Microsoft OLEDB Driver 18 for SQL Server
JDBCBI 4.3 SP01+Microsoft JDBC Driver 8.2 for SQL Server
ODBCBI 4.3 SP01+DataDirect ODBC (10)
2022ODBC BI 4.3 SP04+Microsoft ODBC Driver 18 for SQL Server
OLEDB BI 4.3 SP04+Microsoft OLEDB Driver 19 for SQL Server
JDBCBI 4.3 SP04+Microsoft JDBC Driver 12.2 for SQL Server
ODBCBI 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

0 Kudos
This time I tested the issue with SQL Server 2022 and MS OLE DB Driver 19 for SQL Server (latest version 19.3.5).
0 Kudos
Last time it was most likey with SQL Server 2019. Same outcome.
DonWilliams
Active Contributor
0 Kudos
Did you use the Database menu and set location using the new Client? Did ODBC work?
0 Kudos
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'
0 Kudos
But is there any way to make MSOLEDBSQL19 work correctly?
DonWilliams
Active Contributor
0 Kudos

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.