SSRS CustomReports

[aarong (Aaron Gulley)]

Hi All,

Can anyone explain this one to me?

I am getting this on CustomReports only with Base Reports working fine. I copied these from live server to a development server and got this error.

Program Ice.Services.Lib.RunTask raised an unexpected exception with the following message: RunTask: System.Web.Services.Protocols.SoapException: One or more data source credentials required to run the report have not been specified. Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: One or more data source credentials required to run the report have not been specified.

Some of the errors are this…

Program Ice.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: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_GetNonStock", or the name is ambiguous.```

It's confusing the hell out of me, I have redoployed SSRS Reports and everything.

Kind regards,
Aaron.

[jkane (John Kane)]

Are those from the SSRS Report Server log or a different log?

[aarong (Aaron Gulley)]

SSRS Server Log

[jkane (John Kane)]

Did you set the Data Source when you copied over from the other environment?

[aarong (Aaron Gulley)]

ERP10: An error occurred trying to run task ID 1292657 for agent SystemTaskAgent on the application server (User: EDR, Task Description: Sales Order Acknowledgment).
Error Detail:
Program Ice.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: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.udf_GetNonStock", or the name is ambiguous.
   at Microsoft.ReportingServices.Library.ReportExecution2005Impl.SetExecutionParameters(ParameterValue[] Parameters, String ParameterLanguage, ExecutionInfo2& executionInfo)
   at Microsoft.ReportingServices.WebServer.ReportExecutionService.SetExecutionParameters(ParameterValue[] Parameters, String ParameterLanguage, ExecutionInfo& executionInfo)
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.SetExecutionParameters(ParameterValue[] Parameters, String ParameterLanguage) in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\Web References\SsrsReportService\Reference.cs:line 497
   at Ice.Core.SsrsReporting.SsrsRendererBase.ConfigureReportServerForReport(String reportPath, ParameterValue[] parameters) in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 163
   at Ice.Core.SsrsReporting.SsrsSingleDocumentRenderer.<>c__DisplayClass1_0.<RenderDocument>b__0() in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsSingleDocumentRenderer.cs:line 49
   at Ice.Core.SsrsReporting.SsrsRendererBase.<>c__DisplayClass12_0.<TraceReportRendered>b__0() in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 87
   at Epicor.Hosting.Trace.TraceHandle.TimeAction(Action actionToTime) in C:\_Releases\ICE\ICE3.2.400.20\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.400.20\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.400.20\Source\Server\Internal\Lib\TaskLib\SsrsReporting\SsrsRendererBase.cs:line 95
   at Ice.Core.SsrsReporting.ReportProcessorBase.RenderReportForPrintOrEmailReport(RenderedSsrsReport renderedReport, Boolean serverSidePrint, Boolean ignorePageSettings) in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 303
   at Ice.Core.SsrsReporting.ReportProcessorBase.ProcessReportPart(String reportLocation) in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\SsrsReporting\ReportProcessorBase.cs:line 158
   at Ice.Core.SsrsReporting.StandardReportProcessor.ProcessReportParts() in C:\_Releases\ICE\ICE3.2.400.20\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.400.20\Source\Server\Internal\Lib\TaskLib\RoutingAndBreaking\ReportPersister.cs:line 57
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RenderUnroutedSsrsReport() in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 303
   at Ice.Core.RptBase.ReportSsrsDatabaseBuilder.RunSsrsReportIfEnabled() in C:\_Releases\ICE\ICE3.2.400.20\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.400.20\Source\Server\Internal\Lib\TaskLib\RptBase\ReportSsrsDatabaseBuilder.cs:line 111
   at Ice.Core.RptBase.ReportDatabaseBuilder.XMLClose() in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\RptBase\ReportDatabaseBuilder.cs:line 93
   at Ice.Core.RptTaskBase`1.XMLClose() in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\RptBase\RptTaskBase.cs:line 229
   at Erp.Internal.OM.SalesOrderAck.RunProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ERP\UD10.2.400.20\Source\Server\Internal\OM\SalesOrderAck\SalesOrderAck.cs:line 691
   at Ice.Core.TaskBase`1.StartProcess(Int64 instanceTaskNum, String outputFileName) in C:\_Releases\ICE\ICE3.2.400.20\Source\Server\Internal\Lib\TaskLib\TaskBase\TaskBase.cs:line 47
   at Ice.Hosting.TaskCaller.InnerExecuteTask(IceDataContext newContext) in C:\_Releases\ICE\ICE3.2.400.20\Source\Framework\Epicor.Ice\Hosting\TaskCaller\TaskCaller.cs:line 98
   at Ice.Hosting.TaskCaller.ExecuteTask() in C:\_Releases\ICE\ICE3.2.400.20\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.400.0\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.400.0\Source\Server\Services\Lib\RunTask\RunTask.cs:line 592

``

[ckrusen (Calvin Krusen)]

Does running the report with the output destination of “Generate Only” succeed?

Do the RDL’s datasets in Dev and Prod match? If someone pressed the Sync button, the RDL may have changed in a way that no longer works with the RDD.

FWIW - I’ve never seen a reference like “dbo.udf_...” in a report dataset or datasource.

[aarong (Aaron Gulley)]

I’ve never seen this before…

I’m currently copying the ReportServer database and catalog… The RDL files aren’t stored in the place I thought. I can’t seem to find the RDL files…

Looked in

• SQL Program Files
• inetpub/ERP10/Server/Reports/CustomReports - Blank just BAQReport.

Decided to copy whole report database over, see if that fixes it

[Mark_Wonsil]

@aarong

The RDL files aren’t stored in the place I thought. I can’t seem to find the RDL files…

Looked in

• SQL Program Files
• inetpub/ERP10/Server/Reports/CustomReports - Blank just BAQReport.

RDL files are stored in blobs in the SSRS database and not as files anywhere.

[ckrusen (Calvin Krusen)]

@aarong

Decided to copy whole report database over

The RDL’s aren’t stored as files. They are stored in the DB in a table.

[aarong (Aaron Gulley)]

Thought as much…

Hopefully this will do the trick.

[ckrusen (Calvin Krusen)]

From what I recall… there are three DB’s related to reporting in Epicor

• RPT_PRD_102300 (the actual name comes from the value in the EAC setting) - This holds the info generated by the reports RDD (or BAQ). Table names like dbo.Company_<GUID> are in here.
  
  (Report Settings tab in EAC)

• ReportServer - holds the RDL’s definitions (via multiple tables). Querying the Catalog table lists the RDLs

• ReportServerTempDB - Not exactly sure. Looks a lot like the ReportServer DB. Probably holds temporary info while RDLs are being updated and whatnot.

[Adam (Adam Szymanski)]

Long shot… but did you ever get this fixed? I’m facing the same issue suddenly.

[aarong (Aaron Gulley)]

Yes, it was the SharedDataSource connection string was deleted when transferring the database from live to dev.

You might want to look there first, I will post my string tomorrow for you.

[Adam (Adam Szymanski)]

That was it!

Custom SSRS Invalid Object Name

Typically this means that either: the OrderHed{GUID} table isn’t in the SSRS reporting database for whatever reason (there are a number of reasons for this) or -the datasource for this report isn’t pointing to the same SSRS reporting database as the Epicor base reports. Regarding the first one. Do you see a table in the SSRS reporting database with that name? You can determine the name of the SSRS reporting database from the Application Server Configuration for the appserver in question w…