cancel
Showing results for 
Search instead for 
Did you mean: 

DateTime Parameter issue with the MS SQL stored Procedure when generating Report

0 Kudos

Hi Team,

I am using the CR Runtime Engine (CR Runtime 13.0.23.2819) using the SQL Database (2014) and executing a SPROC to run reports.

The .NET windows Application was working fine with TLS1.0 enabled, but when we disable TSL 1.0 and Enable TLS 1.2, the CR did not work with the SQLOLEDB Driver, so we upgraded the CR Runtime to (CR Runtime 13.0.31.4010) and Tried with the new driver installed MSOLEDBSQL19 Driver, it supported the TLS1.2, but some of the reports that has DateTime as one of the Parameter, it is not working and throwing a syntax error at the Convert function. Below is what we get when we trace the SQL request in the profiler. Browsed through lot of forums, but seems this latest driver is not supported by CR yet. Is that still holds true or is there any fix for the below ?

exec "ARxDb"."dbo"."SP_Name";1 CONVERT(DATETIME, '2022-03-24 13:56:03', 120), CONVERT(DATETIME, '2022-03-24 13:56:03', 120), '03770' (which is not a valid statement for executing the sproc)

Need to find the correct driver that supports both TLS 1.2 and also that does not throw the DateTime convert error.

Thanks in advance.

Sathish

DellSC
Active Contributor
0 Kudos

I changed the primary tag on your question to "SAP Crystal Reports, version for Visual Studio" as this is the tag for questions about using the .NET SDK. The "SAP Crystal Reports" tag is generally for questions about report design and the Crystal Reports desktop software.

-Dell

Accepted Solutions (1)

Accepted Solutions (1)

DellSC
Active Contributor
0 Kudos

The problem may be that the version of the SDK that you're using doesn't support MSOLEDBSQL19. You're using SP 23 of the SDK, which was released in May of 2019, and version 19 of MSOLEDBSQL was released in Feb 2022.

So, you have two options:

1. Upgrade your application so that it uses the latest version (SP 31) of the Crystal .NET SDK, then install the new runtime and deploy the upgraded application. This might require upgrading the version of .NET you're using.

2. Downgrade the version of the MSOLEDBSQL driver that you're using to 18.1 or 18.2.1 from here: https://docs.microsoft.com/en-us/sql/connect/oledb/release-notes-for-oledb-driver-for-sql-server?vie....

Option 2 might be the easier fix for now. However, SAP only supports the current version of the SDK, although it is still possible to download older versions back to SP23.

-Dell

0 Kudos

Hi Dell, Thanks for your reply.

I have already installed the Crystal Report for Visual Studio from older version SP23 to the latest which is SP31, updated the SAP Runtime as well (SP31) and upgraded all my reports to that version and also upgraded the driver to MSOLEDBSQL 17/18/19 (tried all versions) since we need the support for TLS 1.2 and not for TLS 1.0.

The result is, MSOLEDBSQL supports the TLS 1.2, but we are facing issue with the reports that has DateTime DataType as one of the Parameter in the SQL Stored Procedure, when the SQL call is triggered, it is forming the syntax in a wrong way as mentioned in my question and as below.

exec "ARxDb"."dbo"."SP_Name";1 CONVERT(DATETIME, '2022-03-24 13:56:03', 120), CONVERT(DATETIME, '2022-03-24 13:56:03', 120), '03770' (which is not a valid statement for executing the sproc)

Passing expression to the exec sp_name will not work in SQL. Not sure what is forming this wrong syntax while calling the stored procedure with the values. CR or SQL Driver ?

Note: The reports that do not have datetime datatype as one of the parameters, it works fine.

Thanks

Sathish

DellSC
Active Contributor
0 Kudos

don.williams, do you have any thoughts on this? I'm not sure where to go from here.

-Dell

jjamcgu
Discoverer
0 Kudos

I am experiencing the same issue with CR 13 SP33 and MSOLEDBSQL19. See my question https://answers.sap.com/questions/13831169/ for more details.

fabian-eisenbart61
Discoverer
0 Kudos

Still the same issue with SP34, added a comment to your question 13831169 as well.

DellSC
Active Contributor
0 Kudos

Please don't add a comment to an (over a year) old question that is already marked as "Answered". It's against the rules of engagement. Please post a new question with the details of your problem.

Thanks!

-Dell

Answers (1)

Answers (1)

0 Kudos

Yes you do need to upgrade to SP 32, releasing tomorrow:

Follow this WIKI:

https://wiki.scn.sap.com/wiki/display/BOBJ/Crystal+Reports%2C+Developer+for+Visual+Studio+Downloads

The other issue Microsoft updated their client dll name, for CR to be able to use the recent Clients you need to manually upgrade each report.

Open CR Designer, open your report, Click on Menu Database, Set Datasource Location.

In the new list of connection select OLE DB or ODBC and select the latest OLE DB Client from the list. If using ODBC then your DSN needs to be updated to also use the latest Client.

Unfortunately there is no mass update API that can update the report when saved. You can update the report to use the new connection info but it doesn't get saved, only works in the current session which is why you need to update the reports manually.

Use Google and search for this KBA - 2269180 - Disabling TLS 1.0 and 1.1 causes Crystal Reports and .NET application to fail to connect to MS SQL Server

It's older but should answer a few questions.... Using a MS Client that supports TLS 1.2 is Client side configuration only, other then updating the reports to recognise and use the updated client.

Don

0 Kudos

Sure, Thanks Don.

Will wait for tomorrow and hope the SP 32 will support the MSSQLOLEDB Driver for both the TLS 1.2 and the DateTime Format issue.

Thanks

Sathish

0 Kudos

Hi Don,

I'm trying to make sense of the Wiki but it's practically unreadable. Can you post a direct link to the SP32 downloads here, please as I'm going around in circles and I'm going blind with the different fonts and colours.