Error After Adding in Tables to Report Data

Epicor ERP 10
1

I am wanting to adjust a few things in Sales Order Pick so it works better for us. I added in two new tables and everything so far seemed to be going well, until this.

.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: System.Web.Services.Protocols.SoapException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for dataset 'OrderHed'. ---> System.Data.SqlClient.SqlException: Incorrect syntax near 'T1'.
   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\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 633
   at Ice.Core.SsrsReporting.SsrsRendererBase.Render(SsrsRenderInformationBase renderInformation) in C:\_Releases\ICE\ICE3.2.300.30\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\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 87
   at Epicor.Hosting.Trace.TraceHandle.TimeAction(Action actionToTime) in C:\_Releases\ICE\ICE3.2.300.30\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\ICE3.2.300.30\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\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 95
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPreviewOrGenerate(RenderedSsrsReport renderedReport) in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 264
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 158
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_Releases\ICE\ICE3.2.300.30\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\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 57
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 303
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 228
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.ProcessReportWithDataInPlace(Func`2 executeCommand, Func`2 executeReader, SqlObjectsCreated sqlObjectsCreated) in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 111
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 216
   at Ice.Core.TaskBase`1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ICE\ICE3.2.300.30\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 47
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_Releases\ICE\ICE3.2.300.30\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 98
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_Releases\ICE\ICE3.2.300.30\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\RL3.2.300.0\Source\Server\Services\Lib\RunTask\BpmFriendlyTaskLauncher.cs:line 63
   at Ice.Services.Lib.RunTaskSvc.InnerRunTask(Int64 ipTaskNum, Boolean suppressTransaction) in c:\_Releases\ICE\RL3.2.300.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 577
2

The chagnes I made are this:

="SELECT 
T1.OrderNum,T1.Calc_BCCustNum,T1.Calc_BCOrderNum,T1.Calc_CustID,T1.Calc_CustName,T1.Calc_HeadComment,T2.DisplaySeq,T2.KitFlag,T2.KitShipComplete,T2.LineDesc,CAST( T2.OrderLine as nvarchar ) as OrderLine,CAST( T2.OrderNum as nvarchar ) as OrderDtl_OrderNum,T2.PartNum,T2.PickListComment,T2.RevisionNum,T2.Calc_BCLineNum,T2.Calc_BCPart,T2.Calc_BCRev,T2.Calc_UOM, T2.Calc_KitPartNum,T3.OrderRelNum,CAST( T3.ReqDate as nvarchar ) as ReqDate,T3.ShipToNum,T3.ShipViaCode,T3.Calc_BCRelNum,T3.Calc_RelQty,T3.Calc_Source,T4.JobNum,T5.PartNum,T5.WarehouseCode,T6.PartNum,T6.BinNum,T7.RptLiteralsLOf,T7.RptLiteralsLBin as BinNum,T7.Calc_LotNum,T7.RptLiteralsLAllocQty,T7.RptLiteralsLComponents,T7.RptLiteralsLDate,T7.RptLiteralsLDimCode,T7.RptLiteralsLJob,T7.RptLiteralsLKit,T7.RptLiteralsLKitSeq,T7.RptLiteralsLLine,T7.RptLiteralsLOnHand,T7.RptLiteralsLPage,T7.RptLiteralsLPickFrm,T7.RptLiteralsLQtyShip,T7.RptLiteralsLRel,T7.RptLiteralsLRelQty,T7.RptLiteralsLShipTo,T7.RptLiteralsLShipVia,T7.RptLiteralsLUOM,T7.RptLiteralsLWhse,T7.RptLiteralsRptTitle,T7.OrderNum as RptLabelsOrderNum, T7.CustNum, T7.PartNum as RptLabelsPartNum, T7.RevisionNum as RptLabelsRevisionNum, T7.RptLiteralsTime
FROM OrderHed_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2
  ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum
 LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3
  ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine
 LEFT OUTER JOIN JobProd_" + Parameters!TableGuid.Value + " T4
  ON T3.Company = T4.Company AND T3.OrderNum = T4.OrderNum AND T3.OrderLine = T4.OrderLine AND T3.OrderRelNum = T4.OrderRelNum
 LEFT OUTER JOIN PartWhse_" + Parameters!TableGuid.Value + " T5  
  ON T3.PartNum = T5.PartNum AND T3.WarehouseCode = T5.WarehouseCode
 LEFT OUTER JOIN PartBin_" + Parameters!TableGuid.Value + " T6 
  ON T5.Company = T6.Company AND T5.PartNum = T6.PartNum AND T5.WarehouseCode = T6.WarehouseCode
 LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T7
  ON T1.RptLanguageID = T7.RptLanguageID"
3

Try making the FROM ... one single line.

Or place the line breaks outside of the string constants.

From

".... FROM OrderHed_" + Parameters!TableGuid.Value + " T1
 LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2 ...."

to

".... FROM OrderHed_" + Parameters!TableGuid.Value 
+ " T1 LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value 
+ " T2 ...."

Even the syntax highlighting on this site doesn’t like those line breaks in the strings. :wink:

edit

Or if you really want it to be readable, break the T1 LEFT OUTER ... into
T1 " + " LEFT OUTER ... and put the line breaks just after the +. Make sure that you have spaces (like between T1 and LEFT

"... FROM OrderHed_" + Parameters!TableGuid.Value + " T1 " + 
" LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T2 " + 
"  ON T1.Company = T2.Company AND T1.OrderNum = T2.OrderNum " +
" LEFT OUTER JOIN OrderRel_" + Parameters!TableGuid.Value + " T3 " + 
"  ON T2.Company = T3.Company AND T2.OrderNum = T3.OrderNum AND T2.OrderLine = T3.OrderLine " +
"..."

edit #2

You might the same problem (line break in a string), between SELECT and T1.OrderNum. And anywhere else a string spans two line.

1 Like