I have created some custom BAQs to feed production BAQ reports that I run for many filtered customers. These smaller reports all take a while to run, but they eventually spit out a report after half an hour or so.
The largest of these reports comprises over a thousand parts. In Pilot, I can run the report and after 30-45 minutes I will get a result. I can run the report in live, if I use a top 10% or top 50% filter in the BAQ. In live the 50% report runs in about 15 minutes. But if I try to run the full report, I don’t get any result in half an hour as I would expect. After I go home and come in the next day, I see that the report has failed sometime in the evening after 8-9 hours of running.
I can run the BAQ by itself and get a result (300,000+ rows) in about 2-3 minutes.
What can I do to get around this issue and get my full report in Live?
Thanks for your time!
Nate
Error in system monitor? Any useful clues in the details?
Wonder if it is a problem in SSRS?
Maybe check into logging… if you haven’t already?
Also, since you mentioned it works in one environment and not the other, potentially you have some data in your production environment that may be causing some unexpected results? Possible clues may lie in your table joins etc as to where failures could occur once the report hits that data?
I believe I do not have access to the server error log, as I am a cloud user. Here is the system monitor error:
Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:
System.Net.WebException: The operation has timed out
at System.Web.Services.Protocols.WebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.HttpWebClientProtocol.GetWebResponse(WebRequest request)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Ice.Core.SsrsReportService.ReportExecutionService.Render(String Format, String DeviceInfo, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 633
at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 142
at Ice.Core.SsrsReporting.SsrsRendererBase.<>c__DisplayClass12_0.<TraceReportRendered>b__0() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 87
at Epicor.Hosting.Trace.TraceHandle.TimeAction(Action actionToTime) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 92
at Epicor.Hosting.Trace.TraceHandle.AddTimedIfEnabled(String traceFlag, Action actionToTime, Func`2 messageBuilder) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 43
at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func`1 timedAction) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 95
at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 264
at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 158
at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\StandardReportProcessor.cs:line 31
at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func`2 reportsRenderer, Action`1 fillSysRptLstRow, Action`2 processReport, Func`3 filterTableAttachmentsFunc) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 59
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 303
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 228
at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
at Ice.Core.RptTaskBase`1.XMLClose() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 229
at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.RunReportWithErrorHandling(ReportDatabaseConnectionHelper reportDatabaseConnectionHelper) in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 70
at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.WriteData() in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 44
at Ice.Core.TaskBase`1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 83
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 117
at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 59
at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Services\Lib\RunTask\RunTask.cs:line 452
That’s too bad, not much useful in the error, you might end up needing to pursue access to logs.
But for now, I have one other thought ( in addition to that of rbucek )
Suspect it might be something in SSRS itself and not the BAQ.
Can you “Generate Only” and then does the report show as complete in your system monitor?
If so, you could then try opening your report in Report Builder, and try to preview it, using the FileName from the system monitor. e.g. REPORT DATABASE: e3080ae353f04413877df404aba8ab96
If if bombs out, you still might see a different/better error in Report Builder.
- and do you have any calculated fields in your report? i.e. possible issues due to unexpected data types?
All excellent ideas. I will get to trying them out. I believe that our Pilot DB is an accurate enough example of our Live DB, that any data issues should also be present in Pilot. Having said that It is entirely possible that a new part in Live could have some strange setting that my report doesn’t account for. I will try out the generate only option. I haven’t tried that before.
Thanks!
Nate
So far the report has been running for over an hour. I’ll let it go overnight and see if I get a result by morning.
I am finally checking up on this again. When I checked the system monitor logs this morning, I found that something went wrong. I stopped working before noon on 4/10, but my server log shows over a hundred instances of the report from 4/10 to 4/12. Some of them show a status of complete, and some show a status of Cancelled, and some show the error status. I am certain that no one else was running this report, it must have been something I did. I have included a snapshot of the log if you can make any sense of it. The last actions I took were to start the report using the generate only button. I may have started that process twice. Has anyone ever seen anything like this before? Could something weird have happened with my remote connection that constantly kept running the report?
log.xlsx (17.9 KB)
This is an example of the details for the cancelled and error lines:
Cancelled: Task deleted due to restart of TaskAgent.
Error:
Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask:
System.Web.Services.Protocols.SoapException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Diagnostics.Utilities.ReportServerDatabaseLogonFailedException: The report server cannot open a connection to the report server database. The log on failed. ---> System.Exception: For more information about this error navigate to the report server on the local server machine, or enable remote errors
at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)
at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)
at Ice.Core.SsrsReportService.ReportExecutionService.Render(String Format, String DeviceInfo, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 633
at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 142
at Ice.Core.SsrsReporting.SsrsRendererBase.<>c__DisplayClass12_0.<TraceReportRendered>b__0() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 87
at Epicor.Hosting.Trace.TraceHandle.TimeAction(Action actionToTime) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 92
at Epicor.Hosting.Trace.TraceHandle.AddTimedIfEnabled(String traceFlag, Action actionToTime, Func`2 messageBuilder) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 43
at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func`1 timedAction) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 95
at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 264
at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 158
at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\SsrsReporting\StandardReportProcessor.cs:line 31
at Ice.Core.RoutingAndBreaking.ReportPersister.Persist(ReportInstanceInformation reportInstance, Func`2 reportsRenderer, Action`1 fillSysRptLstRow, Action`2 processReport, Func`3 filterTableAttachmentsFunc) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 59
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 303
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 228
at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
at Ice.Core.RptTaskBase`1.XMLClose() in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 229
at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.RunReportWithErrorHandling(ReportDatabaseConnectionHelper reportDatabaseConnectionHelper) in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 70
at Ice.Internal.XA.DataBuilders.DatabaseReportDataBuilder.WriteData() in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Internal\XA\XABaqRpt\DataBuilders\DatabaseReportDataBuilder.cs:line 44
at Ice.Core.TaskBase`1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 83
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 117
at Ice.Hosting.TaskCaller.ExecuteTask(IceDataContext dataContext, Boolean suppressTransaction) in C:\_Releases\ICE\UD10.2.500.10FW\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 45
at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:\_Releases\ICE\RL10.2.500.0FW\Source\Server\Services\Lib\RunTask\RunTask.cs:line 452
I do see some complete tasks, but I don’t see any kind of filename in the system monitor.
Nate,
2-3 minutes of run time on a BAQ is a long time. If you haven’t already done so, I’d suggest looking at the query for possible optimizations.
What I see a lot are joins in the top query where the subsidiary tables generate a lot of overhead. Strategic table criteria and subqueries can make a lot of difference.
I just did one today, where the query was ShipDtl left joined to InvcDtl inner joined to InvcHead. So the InvcDtl to InvcHead part was running over and over (100k+ times). I moved the InvcDtl to InvcHead part into an inner subquery where that part runs only once, and then connected ShipDtl to the subquery. It went from timing out to running under two seconds.
If you get the query running faster your other issues might go away.
Your mileage may vary. Good luck!
Joe
This is the advice that actually worked. I knew that my BAQ shouldn’t be returning so many rows, but it took forever to figure out how to limit things properly. I have no idea what caused that crazy loop before, but now my BAQ only returns under 100k records, and works in just a few seconds. The SSRS also now runs in only a few minutes.
I am no expert when it comes to epicor, or BAQ optimization, so I had to brute force it. I looked at all the tables in my subqueries, then added essentially all the fields in from the linked tables. This let me see the one field that was making tons of otherwise identical records unique. I believe I had to remove the line and release fields for one of my job tables. Once I did that, everything seemed to work like a charm. I went back and cleaned up the fields to only show exactly what I need. So far so good!
Thanks everyone for the ideas and suggestions. Stay safe out there!
Nate
Great! Glad you got it running.
Joe