SSRS Export to Excel results in (500) Server Error

Question

I have an SSRS report (SSRS 2012) that when I try to export to Excel, always crashes after a minute or so with a 500 server error.

The report itself runs successfully. And I can successfully export the result to CSV, which actually only takes a few seconds to do after the report itself has rendered.

I am experiencing this on result sets greater than around 20000 records; my current example has 36000 records - it crashes the Excel export but the CSV export is successful.

I've tried checking the SSRS logs (C:\Program Files\Microsoft SQL Server\MSRS11.SQL2012\Reporting Services\LogFiles) no errors are shown in there, just a couple entries for RenderForNewSession and RenderFromSession pointing to my report.

The Event Viewer Application log shows a Warning entry for ASP.NET 2.0:

Exception information: Exception type: COMException Exception message: This network connection does not exist. (Exception from HRESULT: 0x800708CA)

Is there any place else I might find useful debugging information? I'd really rather not have to direct the client to "not export large reports to Excel, use CSV instead" if I don't have to. Or are there any known issues or quirks when exporting to Excel from the SSRS web report viewer?

EDIT:

After some further digging, I've found some more info but have yet to find an actual solution. I did find where I could turn up the debugging level in the logs - in the ReportingSErvicesService.exe.config file there are two settings, DefaultTraceSwitch and Components underneath an RSTrace tag. They have default values of 3 but bumping each to 4 provides what appears to be a verbose logging output.

That was interesting, but didn't really point me to anything new other than confirming that it looks like the client execution is timing out.

Next I've come across a TechNet article that at least seems to describe the various places I might be able to set an SSRS timeout. From what I can tell it seems like the Excel export renderer is hitting an execution timeout somewhere, but all of the SSRS settings appear to cover plenty of headroom for running anything.

Should the web.config httpRuntime executionTimeout setting override the machine.config settings? The TechNet article implies that the machine.config settings seems to line up with the timeout I'm seeing when exporting, but the ReportServer web.config has an executionTimeout setting much larger than the ASP.NET default setting.

Answer 1

One thought for you is the output of the data has something Excel cannot handle but the csv can. So line breaks in the data or something along those lines.

What happens if you limit your results to just 1 row returned if possible? No rows returned? Does it then output the Excel?

Also, I know from SSRS you can output data onto new tabs, are you using that functionality and perhaps running out of tabs?

Reporting Services export to Excel with Multiple Worksheets

Answer 2

Not sure if this is the same problem, but I've had trouble with exports to Excel that have too many characters in a cell. The entire export crashes if it encounters just 1 cell that has more than 32745 characters.

I've had to start adding truncate code to my text boxes to truncate the cell text upon export to Excel, and that fixed the issue for me -- that's not always a viable option though, so just a suggestion.

IIf(Globals!RenderFormat.Name= "EXCELOPENXML" OR Globals!RenderFormat.Name = "EXCEL",
          Left(Join(Fields!FieldA.Value, chr(10)),32745) + "...Truncated for Excel",
Join(Fields!FieldA.Value, chr(10) ))

Otherwise, here's a link that shows some of the limitations of exports to Excel from Reporting Services: https://docs.microsoft.com/en-us/sql/reporting-services/report-builder/exporting-to-microsoft-excel-report-builder-and-ssrs

Answer 3

a conflation of ideas, but this is often a problem in SSRS 2008r2

As for SSRS2012 - there are two excel export formats

Version of SSRS      Render format name to use
2008 R2              EXCEL
2012                 EXCELOPENXML

Is it possible that you're opening the 2008r2 version? (which has a 65K row limit). if so, you can turn off this option in the ssrs config on the server (but will turn it off for all reports)

With 36K rows you shouldn't have this issue, even in EXCEL (2008r2) export, but is there any possibility you're outputting 2 rows for each result?