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]

Hi Patrick,

What framework and Platform is your AppPool running in for your app?

What version of VS are you using, note there is a 32 bit installer for VS 2010->2019 and a 64 bit installer for VS 2022, you can use the wrong one, it's to integrate into VS properly.

SP 34 requires 4.7 or 4.8 framework, which one are you you using?

Is IIS running in the same platform, x86 or x64?

See my Blog for upgrading, it's does make a difference:

https://blogs.sap.com/2020/10/30/upgrading-a-visual-studio-2008-2019-.net-project-with-the-latest-cr...

Must set the Copy Local to False for all CR runtime.

Quick test, open one report and select Database, Set Location and use a 64 bit DSN, or 32 bit DSN if IIS is 32 bit.

You may need to create the DSN's.

Sometimes there are issues with ODBC or OLE DB, so a quick test to see if one or the other works. I don't recall if there were any known issues with SQL Native 11 Client...

The next thing to test is to turn on SQL Profiler and see if Azure is blocking access, could be a permission issue or CR can't find the Client.

CRLogger is CR's database logging dll, it may catch something if it's database related.

Search for CRLogger using Google and you'll find a link to it to turn on logging.

Another quick test is to comment out the logon routine and preview one of the reports and let it prompt for log on info, or you may simply be able to hit the Refresh button in the Viewer to prompt.

It should prompt to connect to the DB, sometimes the User Name or Password is wrong, this verifies if your app does have permissions to connect. CR does respect all DB Permissions so possibly the User account you are using doesn't have rights to access the Database.

Since you are using the same Client all you should have to do is use the Azure Server Instance in the Connection property, CR for VS does like the SQL Native 11 client.

CR does simply pass the DB error to you that comes from the Client, so if it fails to connect that is the first to look into.

If you can run in debug mode and single step into each line and mouse over the connection info properties and verify the values are correct.

If your app doesn't use a viewer then write a simple 3 liner test app, Load the report, set the Viewer to the report and preview.

It should prompt for log on info to verify it does work.

There is one potential issue with the Client though, according to CR's Platform Azure is only supported using this client: Microsoft ODBC Driver 17 for SQL Server

I've never tried it so not sure if it works with MS SQL Native 17.

Here's the Platform link, use the one for CR 2020, none of the other links work:

https://wiki.scn.sap.com/wiki/display/BOBJ/SAP+Crystal+Reports+-+Supported+Platforms

Look for the Database info and click on it, it downloads an Excel file.

TO sum up:

Look on the MS SQL Server and see what log on info is being passed from the app.

Enable CRLogger to check to see what CR is sending.

Verify using a third party tool you can connect using the same log on credentials and IIS/Your App is using

Simplify the test as much as possible.

Don

[pgoruick]

Thanx, Don. To be clear, our app works as expected when the tenant's DB is on our SQL Server on a VM and generally works fine once re-pointed to an Azure SQL DB, but intermittently runs into this exception - often the same report will work on retry.

Already looked at SQL Profiler - nothing apparent there as queries just keep running (sometimes for minutes, depending on the report) until it just stops, though, AFAIK, there's no way to tell if the queries all finished successfully and there's something 'downstream' interrupting the report process.

Will definitely pursue the CRLogger idea - could provide some valuable info. Thanx.