Time Phase Material Requirements

Epicor ERP 10
, , ,
1

I have a modified TimePhaseMaterialRequirement SSRS report in /CustomReports/ that works great in my test environment, but when I save it to my production environment, it errors out. I verified that the report does not have the TableGuid parameter set, and the DataSource has been changed to point to the Production database, rather than the testing environment.

The exception information is here:

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: System.Web.Services.Protocols.SoapException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> 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
at Microsoft.ReportingServices.Library.ReportExecution2005Impl.InternalRender(String Format, String DeviceInfo, PageCountMode pageCountMode, Stream& Result, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds)
at Microsoft.ReportingServices.Library.ReportExecution2005Impl.Render(String Format, String DeviceInfo, PageCountMode pageCountMode, Byte[]& Result, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds)
at Microsoft.ReportingServices.WebServer.ReportExecutionService.Render(String Format, String DeviceInfo, Byte[]& Result, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds)


Stack Trace:
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\3.1.600.15\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 633
at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_Releases\ICE\3.1.600.15\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\3.1.600.15\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 87
at Epicor.Hosting.Trace.TraceHandle.TimeAction(Action actionToTime) in C:\_Releases\ICE\3.1.600.15\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\3.1.600.15\Source\Framework\Epicor.System\Hosting\Trace\TraceHandle.cs:line 39
at Ice.Core.SsrsReporting.SsrsRendererBase.TraceReportRendered(String printProgram, String ssrsRenderFormat, Func`1 timedAction) in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 95
at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 256
at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 150
at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_Releases\ICE\3.1.600.15\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) in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 50
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 301
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 226
at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.ProcessReportWithDataInPlace(Func`2 executeCommand, Func`2 executeReader, SqlObjectsCreated sqlObjectsCreated) in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 109
at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 76
at Ice.Core.RptTaskBase`1.XMLClose() in C:\_Releases\ICE\3.1.600.15\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 158
at Erp.Internal.IM.IMR60.RunProcess(Int64 instanceTaskNum, String outputFileName) in c:\_Releases\ERP\RL10.1.600.0\Source\Server\Internal\IM\IMR60\IMR60.cs:line 480
at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_Releases\ICE\3.1.600.15\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 93
at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_Releases\ICE\3.1.600.15\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 54
at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in c:\_Releases\ICE\3.1.600.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in c:\_Releases\ICE\3.1.600.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 535
at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in c:\_Releases\ICE\3.1.600.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 535
at Ice.Services.Lib.RunTaskSvcFacade.RunTask(Int64 ipTaskNum) in c:\_Releases\ICE\3.1.600.0\Source\Server\Services\Lib\RunTask\RunTaskSvcFacade.cs:line 87
at SyncInvokeRunTask(Object , Object[] , Object[] )
at System.ServiceModel.Dispatcher.SyncMethodInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs)
at Epicor.Hosting.OperationBoundInvoker.InnerInvoke(Object instance, Func`2 func) in C:\_Releases\ICE\3.1.600.15\Source\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 59
at Epicor.Hosting.OperationBoundInvoker.Invoke(Object instance, Func`2 func) in C:\_Releases\ICE\3.1.600.15\Source\Framework\Epicor.System\Hosting\OperationBoundInvoker.cs:line 28
at Epicor.Hosting.Wcf.EpiOperationInvoker.Invoke(Object instance, Object[] inputs, Object[]& outputs) in C:\_Releases\ICE\3.1.600.15\Source\Framework\Epicor.System\Hosting\Wcf\EpiOperationInvoker.cs:line 23
at System.ServiceModel.Dispatcher.DispatchOperationRuntime.InvokeBegin(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage5(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)
at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)
at System.ServiceModel.Dispatcher.ChannelHandler.DispatchAndReleasePump(RequestContext request, Boolean cleanThread, OperationContext currentOperationContext)
at System.ServiceModel.Dispatcher.ChannelHandler.HandleRequest(RequestContext request, OperationContext currentOperationContext)
at System.ServiceModel.Dispatcher.ChannelHandler.AsyncMessagePump(IAsyncResult result)
at System.ServiceModel.Dispatcher.ChannelHandler.OnAsyncReceiveComplete(IAsyncResult result)
at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
at System.ServiceModel.Channels.SecurityChannelListener`1.ReceiveItemAndVerifySecurityAsyncResult`2.InnerTryReceiveCompletedCallback(IAsyncResult result)
at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
at System.ServiceModel.Channels.TransportDuplexSessionChannel.TryReceiveAsyncResult.OnReceive(IAsyncResult result)
at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
at System.ServiceModel.Channels.SynchronizedMessageSource.ReceiveAsyncResult.OnReceiveComplete(Object state)
at System.ServiceModel.Channels.SessionConnectionReader.OnAsyncReadComplete(Object state)
at System.Runtime.Fx.AsyncThunk.UnhandledExceptionFrame(IAsyncResult result)
at System.Net.LazyAsyncResult.Complete(IntPtr userToken)
at System.Net.LazyAsyncResult.ProtectedInvokeCallback(Object result, IntPtr userToken)
at System.Net.Security.NegotiateStream.ProcessFrameBody(Int32 readBytes, Byte[] buffer, Int32 offset, Int32 count, AsyncProtocolRequest asyncRequest)
at System.Net.Security.NegotiateStream.ReadCallback(AsyncProtocolRequest asyncRequest)
at System.Net.AsyncProtocolRequest.CompleteRequest(Int32 result)
at System.Net.FixedSizeReader.CheckCompletionBeforeNextRead(Int32 bytes)
at System.Net.FixedSizeReader.ReadCallback(IAsyncResult transportResult)
at System.Runtime.AsyncResult.Complete(Boolean completedSynchronously)
at System.ServiceModel.Channels.ConnectionStream.IOAsyncResult.OnAsyncIOComplete(Object state)
at System.Net.Sockets.SocketAsyncEventArgs.OnCompleted(SocketAsyncEventArgs e)
at System.Net.Sockets.SocketAsyncEventArgs.FinishOperationSuccess(SocketError socketError, Int32 bytesTransferred, SocketFlags flags)
at System.Net.Sockets.SocketAsyncEventArgs.CompletionPortCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* nativeOverlapped)
at System.Threading._IOCompletionCallback.PerformIOCompletionCallback(UInt32 errorCode, UInt32 numBytes, NativeOverlapped* pOVERLAP)

image
image.png1232×992 373 KB

I am at a loss as to why it would be failing in the Production environment, but works great in Testing. Thoughts?

2

Can you run the report in report builder?

3

The only way it could be ran in Report Builder is if it finished successfully in Epicor, so I could obtain the GUID from the System Monitor, right? Or is that not true in this case?

4

Depend on which report data definition you are using. If it is the same as another style you can run the style that is working. Then once the tables are created grab that GUID.

Were there any changes to the report definition in test?

5

No changes at all. And some further testing here leads me to more information…even the base SSRS report is failing when I run it in Production. It runs fine in Testing.

I also am noticing that it seems to error out when the report has a lot of data in it. When I run it in Testing, it returns 28,800 some odd rows. The Production environment should be approximately the same amount. I don’t see why it would fail in 1 and not the other, as they are both using the same Report Server instance. (IE: the timeouts in the reportserver.config files is a non-issue).

Now that I am aware that the stock report is also failing, it makes me wonder if there isn’t an issue with the data itself…(something in Production being ‘bad data’). It has been a while since I have copied Production to Testing, so if there is any ‘bad data’, it may be newer than the last DB Copy.

6

The exception you posted says a possibility of a low disk condition. Any chance the disks on your prod server are filling up?

7

Not unless 10-20 gigs free on each drive is considered low on capacity.

8

So I copied the Production DB to a new E10 Instance, deployed a new app server and configured it for SSRS as well. Same results.
When I try to run the report with the 3 required part classes (FG, COMP, RAW) it fails. When I try it with just FG and RAW it works. Running it with COMP on it’s own causes it to fail. So, either something is wrong with one of the parts with the class of COMP, or for some reason SSRS really doesn’t like it when there are that many rows (18,000 +/-) to work with.

9

if two groups work on one doesn’t it wreaks of a data problem
i remember in older versions when a bad char in a description would cause a report error.

i would try to modify report to limit data to certain subgroups, if possible.

10

@amaragni thought about that as well, so I made another copy of the database, and changed all 28,558 part descriptions to read “TEST”. I re-ran the reports and as expected the first 2 finished fine. Those were ran for parts with ClassID of RAW and FG with 826 parts and 9,417 parts respectively. However, when I ran it for ClassID of COMP, it once again failed.

I started the report on 6/6/2019 at 5:58AM. Thirty seven minutes later, at 6:35AM, it failed.

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: System.Web.Services.Protocols.SoapException: An error occurred within the report server database.  This may be due to a connection failure, timeout or low disk condition within the database. ---> 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
       at Microsoft.ReportingServices.Library.ReportExecution2005Impl.InternalRender(String Format, String DeviceInfo, PageCountMode pageCountMode, Stream& Result, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds)
       at Microsoft.ReportingServices.Library.ReportExecution2005Impl.Render(String Format, String DeviceInfo, PageCountMode pageCountMode, Byte[]& Result, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds)
       at Microsoft.ReportingServices.WebServer.ReportExecutionService.Render(String Format, String DeviceInfo, Byte[]& Result, String& Extension, String& MimeType, String& Encoding, Warning[]& Warnings, String[]& StreamIds)

The error once again states very vague information. “This may be due to a connection failure, time or low disk condition within the database”. I do not think it is a ‘low disk’ issue, judging from the screenshot below.

image
image900×431 44.6 KB

I wouldn’t think it is a connection error, since other reports run just fine.

And as far as the timeout option goes, well I have already looked into that about a month and a half ago. Epicor has some instructions (KB0047758) for adjusting the timeouts on the app server and Report Server. I followed those instructions, and the problem still persists.

I have had a case open with Epicor for a while now, and I they have not responded to any of my messages regarding the case since May 24th. If there are any Epicor staff members reading this, my Case Number is CS0001501905.

I am running out of ideas quick. I am going to write a quick script to check all fields in the Parts table for unknown Unicode characters to see if there are any in other fields, but if that comes back negative…well, lets just say that I hope it doesn’t, because I have no other ideas.

11

crazy
maybe not a description - maybe it is timeout?

how about going the other way
create a NEW class - and move ALL the parts OUT of COMP except just one or two
then run the COMP group … that will help confirm
(1) the class COMP doesn’t have a problem
(2) maybe it is a timeout (in which case you can build up the number of parts in COMP until it breaks)

just a thought … you have the ability to do so with the DB copy

PS on the COMP do you have BOTH PO and MFG PART/STOCK types…? if so - maybe choose only one
and/or CUT the planning horizon down (so if its’ 6 monhts - make it 1 month)

12

I am running the report using the options shown below. I don’t use the report myself, but the person who does rely on this report provided me with the configuration that he uses.

image
image745×1081 129 KB
image
image745×1081 52.9 KB

I am going to look into doing some manipulation on the DB and moving some parts around to see what (if anything) changes.

13

I didn’t even have a chance to do anything yet and something happened: At 7:10AM I re-ran the report with all of the same options. The only thing I changed was I chose the output format to be PDF rather than CSV. And 25 minutes later at 7:35, Adobe pops open with my report…

Now this REALLY makes me think that it is an issue wit the data being bad. I think that when running it to CSV that a piece of bad data is not converting to UTF-8 properly or something.

14

Well, I finally got the report to run. It turns out that it was taking so long to run that it was timing out. But, it wasn’t timing out on the SQL side, but the ReportServer side. Even though I set the service to run indefinitely, it was still timing out.

image
image.png687×559 13.8 KB

So, I started to look at the report a little more, as well looking at SQL Profiler while running the report, and I noticed that when the report was running, it would call the TPhse table first, and loop through that. Each time it would look at a row in TPhse, it would run 5 other queries (Company, RptLabels, etc). Those tables are not needed in the TimePhase report, at least for our company.

As this report is only ever ran as CSV, I decided to make a new Report Style, and edit the rdl for TimePhase and LotDim and removed the datasets that were not needed. I also tweaked the report to take away all of the fields that start with lbl, as those are not needed in a CSV either.

image
image.png1233×189 55.9 KB

The report is much more compact now, and because it does not have to grab all of the RptLabels, the CallClientContextData, CallContextBpmData, etc, it runs in a matter of minutes. It spits out a CSV that is formatted exactly how the finance and MRP departments requested. This report can even run against all Part ClassID’s and with the “Project Balances Through” field being left open too. Takes all of 12 minutes to run this now, compared to the 40+ minutes for it to fail previously.

Thanks to everyone here for pointing me in different directions trying to nail down the cause of this issue. It was a headache for sure, but I learned quite a bit in the process, which I will be using in the near future on other fairly large reports that we utilize that do not need all of those extra things in it.

15

Hi Jason,

After you updated your Report Server settings (prior to the SSRS report re-write), did you have to Recycle the Application Pool or Stop/Start the App Pool or Regen to get the settings to work?

Thanks,
Karen