SSRS Large Report Timeouts (Sales Order Acknowledgements)

jason.uphoff · January 17, 2023, 7:36pm

Hello everyone,

First time poster - just entering the Epicor world, and I am a data guy. My experience is with databases, primarily MS SQL Server, before starting my new position.

One of my first tasks has been to help troubleshoot an issue with the Sales Order Acknowledgement form. There is a custom report style created prior to my arrival, and this report style has no sub-reports like the system report does. We have a few particularly large orders (150+ lines) that are causing a timeout when the report is run. Unfortunately, the cause of the timeout isn’t readily apparent, even in the stack trace provided by SSRS.

I was able to find a workaround, which I will get to, but I think the context of what I tried before I got there is important.

First, I updated the SSRS timeout values in both the web an app config files. (90000 for Web config Timeout, 1200 for the App Config Timeout.)
Next, I worked with my Data Analyst to remove any fields from the data sets that weren’t used in the report.
Per this article (SSRS RDD Report Timeout), we removed the Comments fields from the report.
Reviewed the Stack Trace:

An example of the stack trace, which is repeated 3 times (Default SSRS Timeout Retry Settings). I’ve truncated a lot of the duplicate lines to keep the post succinct:

processing!ReportServer_0-1!29f4!01/16/2023-11:29:16:: w WARN: Data source 'dsSOForm': Report processing has been aborted.
processing!ReportServer_0-1!29f4!01/16/2023-11:29:16:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: [UserCanceled:ReportProcessing], Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: Report processing has been canceled by the user. ;
processing!ReportServer_0-1!29f4!01/16/2023-11:29:16:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: [UserCanceled:ReportProcessing], Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: Report processing has been canceled by the user. ;
library!ReportServer_0-1!29f4!01/16/2023-11:29:36:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: , An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database.;
library!ReportServer_0-1!29f4!01/16/2023-11:29:36:: e ERROR: Transaction rollback failed. Exception thrown: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
   at System.Data.SqlClient.TdsParserStateObject.ReadSniSyncOverAsync()
   at System.Data.SqlClient.TdsParserStateObject.TryReadNetworkPacket()
   at System.Data.SqlClient.TdsParserStateObject.TryPrepareBuffer()
   at System.Data.SqlClient.TdsParserStateObject.TryReadByte(Byte& value)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction, Boolean isDelegateControlRequest)
   at System.Data.SqlClient.SqlInternalTransaction.Rollback()
   at System.Data.SqlClient.SqlTransaction.Rollback()
   at Microsoft.ReportingServices.Library.ConnectionManager.AbortTransaction()
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.Library.Storage.WrapAndThrowKnownExceptionTypes(Exception e)
   at Microsoft.ReportingServices.Library.ConnectionManager.AbortTransaction()
library!ReportServer_0-1!29f4!01/16/2023-11:29:36:: w WARN: Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerStorageException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---

Based on the Stack Trace, I confirmed that the Report Server database (and tempDB) had plenty of space, weren’t limited in their growth, growth amounts were set appropriately, and the drives they lived on were healthy.
I watched the process in SQL Profiler, which helped me to understand the process. I saw the data pulled in less than a second, the GUID tables were created and data inserted in less than a second, and then SSRS sat on the primary query until it timed out.

I stumbled upon the workaround by accident - in an effort to identify the failure point, I kicked off the report, confirmed the GUID tables were created, and then attempted to preview the report in Report Builder directly. Lo and behold, in less than 3 seconds I had the completed report ready.

Based on this, it is clear that SSRS is the issue, not Epicor - I am likely going to create a ticket with Microsoft to investigate, but I wanted to drop a post here to see if there is something I’m not considering, or if anyone has solved this issue in the past.

Thanks for any advice you have, and look forward to collaborating with you.

Jason Uphoff

utaylor · January 17, 2023, 7:59pm

What version are you on and did you perform an update to your ERP or something? What spurred this change, was it working yesterday and now it’s fine? Any other info?

jason.uphoff · January 17, 2023, 8:06pm

Good questions -

No changes made to the version of Epicor or SQL/SSRS (That my team is aware of). They tell me they’ve had this issue for over a year and haven’t been able to make headway on it. We are on Epicor 10.2.700.32.

utaylor · January 17, 2023, 8:09pm

So jason, there is a log for the SSRS service.

Have you looked in there yet for anything?

utaylor · January 17, 2023, 8:09pm

You on prem?

Also, welcome to the forum.

jason.uphoff · January 17, 2023, 8:10pm

Thanks for the welcome! The stack trace is from the SSRS Logs - unfortunately that’s the most useful chunk I found in about 10 runs across all of our different changes. We are on prem!

utaylor · January 17, 2023, 8:14pm

you got that from this location of the server you run your SSRS from?

Program Files\Microsoft SQL Server Reporting Services\SSRS

jason.uphoff · January 17, 2023, 8:17pm

Yes sir!

utaylor · January 17, 2023, 8:19pm

Is this the base acknowledgement form or a custom one?

You using the base RDD for orderack or you using a custom RDD?

jason.uphoff · January 17, 2023, 8:26pm

Base RDD, custom report style

utaylor · January 17, 2023, 8:27pm

Does the base report style also time out?

jason.uphoff · January 17, 2023, 8:29pm

It doesn’t - my next steps are to completely rebuild the report from the base report and check it whenever I make a change to identify the issue… but I thought I’d check here first before I did that / submit a MS ticket.

utaylor · January 17, 2023, 8:30pm

See Jason I have had this exact same issue at one point in my career and I am struggling to remember what it was due to, but I think it may have been custom code or a field expression. I am struggling to recall.

The fact that base runs fine obviously points you in the right direction.

Do you know how to get to the custom code section of the report?

utaylor · January 17, 2023, 8:32pm

There was also this KB that I found KB0029568, but given you don’t have this exact same issue that the KB was written to address, I don’t know how applicable it is.

jason.uphoff · January 17, 2023, 8:39pm

There are a TON of custom expressions in this report, and I also suspect that as a prime candidate - especially if there is circular logic. Unfortunately the report is massive and I haven’t had a chance to completely rip it apart yet, which is why i’ll likely rebuild it from the bottom up if we don’t identify an issue here.

Thanks for the KB reference, i’ll be checking that out, too!

ckrusen · January 18, 2023, 6:30pm

I recall a few SO’s that were really long and it was a subreport (one that didn’t even get used in our order) that was the culprit. Think I posted about it here. If I find it I’ll add to this topic

edit

I think this was it

jason.uphoff · January 18, 2023, 6:49pm

We are at an off site meeting today, but will try that tomorrow and check back in. Thank you for the lead!