Error running Report in SQL Server Reporting Services (SQL Server 2005)

ssrs

I get an error message " An error has occurred during report processing. Query execution failed for dataset [sprocs name]" when running SSRS report.

The stored procedures for this report can be executed without error in the SQL server. It has a CTE query that uses a linked server to retrieve data from a different database server.

There is no error in the windows log that points to this issue.

My guess is that it has to do with the linked server dropping connection but I can't seem to prove that is the case because I can't find any error that points to it and also sprocs rans fine. And also opening the report frequently with the error above from SSRS, to check if it is working, brought down the report server.

Can anyone help troubleshoot this issue?

Best Answer

The SQL Profiler didn't help to troubleshoot the issue. I was able to test and run the report in our Staging SSRS server without error. I was able to run the report with the same data source credential from production,an authentication issue is unlikely.

This is the error found in the ReportServer log

ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set '[sprocname]'., ; Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Query execution failed for data set '[sprocname]'. ---> System.Data.SqlClient.SqlException: Named Pipes Provider: No process is on the other end of the pipe.

However, our System Admin had compared SQL Server Configuration and found that the client protocols are ordered differently in Staging and Production. We changed the order in Production to match with Staging to the following order Shared Memory, TCP/IP and Named Pipes, instead of Named Pipes preceded the TCP/IP. This resolved the issue and so far the report runs without any error.

Related Topic