on 2024 Nov 05 4:59 PM
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.
Request clarification before answering.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
User | Count |
---|---|
80 | |
30 | |
10 | |
8 | |
8 | |
7 | |
6 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.