Crystal Reports Visual Studio runtime exceptions on SQL Azure

[DellSC]

For 20+ years, we have been successfully running Crystal Reports via the runtime in our ASP.NET web app on SQL Server (of varying versions) in hypervisor and VM infrastructure.

We are now attempting to migrate to Azure SQL Database Platform As A Service.

We can run the exact same tenant and application successfully connected to our ‘old’ SQL Server but once we point said app to a SQL Azure DB we start getting these INTERMITTENT exceptions that we’ve never seen before:

Example 1:

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> CrystalDecisions.CrystalReports.Engine.InternalException: Error in File ZZZ116156_48048_{36851D49-1F02-4F14-9F02-D7FAE9837054}.rpt:<br>Failed to retrieve data from the database. ---> System.Runtime.InteropServices.COMException: Error in File ZZZ116156_48048_{36851D49-1F02-4F14-9F02-D7FAE9837054}.rpt:<br>Failed to retrieve data from the database.<br> at CrystalDecisions.ReportAppServer.Controllers.ReportSourceClass.Export(ExportOptions pExportOptions, RequestContext pRequestContext)<br> at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)<br> --- End of inner exception stack trace ---<br> at CrystalDecisions.ReportAppServer.ConvertDotNetToErom.ThrowDotNetException(Exception e)<br> at CrystalDecisions.ReportSource.EromReportSourceBase.ExportToStream(ExportRequestContext reqContext)<br> at CrystalDecisions.CrystalReports.Engine.FormatEngine.ExportToStream(ExportRequestContext reqContext)<br> at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToStream(ExportOptions options)<br> at CrystalDecisions.CrystalReports.Engine.ReportDocument.ExportToStream(ExportFormatType formatType)

Example 2:

System.Web.HttpUnhandledException (0x80004005): Exception of type 'System.Web.HttpUnhandledException' was thrown. ---> System.Runtime.InteropServices.COMException (0x800002F4): Failed to open the connection.<br>ZZZ14064_133364_{0B6CB231-743A-4151-82A9-B9C98B9FFF6A}.rpt<br> at CrystalDecisions.ReportAppServer.Controllers.DatabaseControllerClass.SetTableLocation(ISCRTable CurTable, ISCRTable NewTable)<br> at CrystalDecisions.CrystalReports.Engine.Table.set_Location(String value)

We've update the runtime on our web servers and libraries in our code to the latest version (CRRuntime_32bit_13_0_34.msi) to no effect.

Any help or information you can provide would be greatly appreciated.

Thanx…!

[DellSC]

What type of connection are you using to connect to your database? When you upgraded your application, did you upgrade your reports to use the latest version of the SQL Server client that will provide the connection to SQL Server on Azure?

-Dell

[pgoruick]

Database Type: OLE DB (ADO)
Provider: SQLNCLI11

That's certainly a good point and warrants some looking into. We'd just assumed the same driver/provider would work in SQL Azure, so we may have to change to something better-supported for that service.

Unfortunately, we have hundreds of reports...I hope this can be modified via code!

[DonWilliams]

Just thought of something else, CR caches the connection info while it's waiting for data or if the page is idle etc.

Possibly the session is timing out thus CR is losing the connection.

You may need to add Sessions and PostBack code and ping the session every 5 minutes or so to keep it alive:

See this KBA on how to:

https://userapps.support.sap.com/sap/support/knowledge/en/1985571

Doesn't explain why it works with MS SQL Server but it could also be Client issues.

So you may have to use the SQL Client specified in the Platforms file.

According to it we only support Microsoft ODBC Driver 17 for SQL Server for Azure.

You may have found why Native 11 isn't listed...

I haven't tried it but converting Reports from old MS Clients to SQL Native 11 works at runtime but it won't save the updated Client through code, so you may have to hire a student to manually update each report and save it.

You could save a Flag in the Reports Summary Info to say it's been converted while that process is being done, then in code check the flag if it hasn't then use RAS and ReplaceCOnnection() to update the report.

Search for it or see my Parameter test app on the WIKI page on how to.

Don