2

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?

3
  • How long does the query typically take to run outside of SQL Server Reporting Services (SSRS)? I wonder if you could be running into a command query timeout baked into the SSRS config. You might not have the same command timeout when you are running the query in SQL Server Management Studio. Apr 24, 2012 at 21:31
  • It only takes 2sec for the query to run outside of SQL Server Reporting Services (SSRS). Apr 24, 2012 at 22:11
  • Ok, then command query timeout is unlikely. Apr 25, 2012 at 14:07

3 Answers 3

1
+50

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.

0

Use SQL profiler to see what is actually beeing sent and returned from the SQL server. Using the trace log you should be able to determine the cause.

Is the reporting service running on the same server you tested the stored procedures? Do you run the report directly from SSRS interface or is it embedded within another application?

Could be authentication issue, firewall issue, double hop kerberos issue. My guess is you only reach one of the servers OR you reach both but login fails to the linked server.

1
  • I run the report directly from SSRS interface. I was able to run this from our Staging SSRS server without any error. May 2, 2012 at 22:15
0

As of my knowledge this is Authentication issue check your reporting server credentials and linked server credentials and permissions

1
  • I was able to run the report in our Staging SSRS without any error using the credential from production. May 2, 2012 at 22:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .