SSRS Stock Status Report

knash · July 12, 2019, 1:18pm

We are getting the following error when running the Stock Status Report. The report has no customizations. I did open a ticket with Epicor, but checking here as well. The report was working up until Monday of this week. It was just brought to my attention that it wasn’t working. Seems odd that all of a sudden that it would stop working.

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Stack Trace:
   at System.Data.SqlClient.TdsParser.TdsExecuteRPC(SqlCommand cmd, _SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc, Boolean sync, TaskCompletionSource`1 completion, Int32 startRpc, Int32 startParam)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Ice.Core.RptBase.ReportRowDataWriter.WriteRow(IRow row) in C:\_Releases\ICE\ICE3.2.200.10\Source\Server\Internal\Lib\TaskLib\RptBase\ReportRowDataWriter.cs:line 73
   at Erp.Internal.IM.IMR40.PrintReport() in C:\_Releases\ERP\UD10.2.200.10\Source\Server\Internal\IM\IMR40\IMR40.cs:line 2602
   at Erp.Internal.IM.IMR40.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ERP\UD10.2.200.10\Source\Server\Internal\IM\IMR40\IMR40.cs:line 1097
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_Releases\ICE\RL3.2.200.0\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 98
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_Releases\ICE\RL3.2.200.0\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 57
   at Ice.Lib.RunTask.BpmFriendlyTaskLauncher.Run(String sessionIdPrefix, IceContext db, Action taskRunner) in C:\_Releases\ICE\ICE3.2.200.10\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
   at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in C:\_Releases\ICE\ICE3.2.200.10\Source\Server\Services\Lib\RunTask\RunTask.cs:line 549

John_Mitchell · July 12, 2019, 1:51pm

My guess is a bad PartTran date. You could try:

SELECT MAX(TranDate), MIN(TranDate), MAX(SysDate), MIN(SysDate) FROM ERP.PartTran

knash · July 12, 2019, 1:53pm

Thanks. Looks like the date are ok.

ckrusen · July 12, 2019, 2:00pm

My guess would be a bad TranDate in your DB (or a bad date entered as the SSR date).

Try using the Filter sheet running it for just one Part (that you know you have).

If that works, then start adding parts (or classes) until you find the Part(s) that are giving you problems

EDIT:

Another option would be to make a BAQ of the PartTran table and sort by TranDate and see if any records have a wonky Tran Date. Repeat for SysDate.

tsmith · July 12, 2019, 4:53pm

Along with @ckrusen’s suggestion to check SysDate, also check to make sure there aren’t any NULL TranDates or SysDates. Looks like the database table allows nulls, but I can’t think of any reasons there should be a valid null value in these columns.

knash · July 12, 2019, 6:56pm

thanks for the ideas.

checked for nulls as well.

all dates look “normal”

ckrusen · July 12, 2019, 8:20pm

Did you see if the error still happens for selected parts?

knash · July 12, 2019, 8:27pm

I can get the report to work for some parts!!!

Narrowed it down to a single part class not working, now how do I find the bad part transaction/data.

ckrusen · July 13, 2019, 2:03am

How many parts are in that class? Possible to select subsets of them to find the errant partum?

Another thing to check is if the WIP Recon report runs okay. I only suggest that as you can pre-filter the data by starting and ending dates, as well as choosing either TranDate or SysDate.

One last thing back on the SSR report… does it still fail if you choose today’s date, or does it require going back to a certain date.

tpynepeak · August 4, 2022, 4:12pm

I have the same issue.

When running a query to show the Min and maximum of the system and trandate they look normal.

Puzzled. Going to open a ticket but this is a mess.

If I run the report for a specific class it blows up. Query PartTran for that class everything looks fine. I do not see any malformed or null dates.

gpayne · August 4, 2022, 4:36pm

A BAQ will make nulls to space so you won’t see it like you would in SSMS. You would have to make a calculated field that you suspect is the culprit with something like IsNull(field,‘Null Value’) to find them.

tpynepeak · August 4, 2022, 5:11pm

I was suspicious when I did the Min Max on the dates but did not see Null or Spaces - everything was a date

The ISNULL code compiled but blew up when executing.

I tied something similar with a case statement

case
when PartTran.TranDate IS NULL then ‘NULL’

else 'OK DATE'

end

They were all good. I did the same for SysDate - same result. Very puzzled. It could be a case of the error not meaning what we think it means. It could be another table. It could be an epicor calculation (The RDD references virtual tables).

I’ve seen this topic a few times on various forums but not if or how people were able to fix the issue.

tpynepeak · August 4, 2022, 5:44pm

One more thing - the System Monitor has a “Activity” column in the task detail tab. It will tell you what part was being processed when the report fails. However this did not help so far. I queried part tran for those records and the dates looked normal. In fact most had the Sys date and Tran date being the same

tpynepeak · August 4, 2022, 8:07pm

It looks like having a bad date in Part Lot on the Mfg Date or Cure Date will cause the Stock Status to have this error. This can happen by DMT - it will allow bad dates.

Luckily enough it is easy enough to fix manually or with a DMT.