Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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. –  Larry Silverman Apr 24 '12 at 21:31
    
It only takes 2sec for the query to run outside of SQL Server Reporting Services (SSRS). –  iamceeceecarter Apr 24 '12 at 22:11
    
Ok, then command query timeout is unlikely. –  Larry Silverman Apr 25 '12 at 14:07

3 Answers 3

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.

share|improve this answer

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.

share|improve this answer
    
I run the report directly from SSRS interface. I was able to run this from our Staging SSRS server without any error. –  iamceeceecarter May 2 '12 at 22:15

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

share|improve this answer
    
I was able to run the report in our Staging SSRS without any error using the credential from production. –  iamceeceecarter May 2 '12 at 22:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.