SSRS Query execution failed for dataset

Question

Have just deployed my Project on to my reporting Server.

I have multiple datasets which are referencing views which exist on the db on that server.

When I try to go into any report part I am getting this message:

An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'dataset1'. (rsErrorExecutingCommand)
For more information about this error navigate to the report server on the local server machine, or enable remote errors 

Can anyone help?

Answer 1

I encountered a similar error message. I was able to fix it without enabling remote errors.

In Report Builder 3.0, when I used the Run button to run the report, an error alert appeared, saying

An error has occurred during report processing. (rsProcessingAborted)
[OK] [Details...]

Pressing the details button gave me a text box where I saw this text:

For more information about this error navigate to the report server
on the local server machine, or enable remote errors
----------------------------
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)

I was confused and frustrated, because my report did not have a dataset named 'DataSet1'. I even opened the .rdl file in a text editor to be sure. After a while, I noticed that there was more text in the text box below what I could read. The full error message was:

For more information about this error navigate to the report server
on the local server machine, or enable remote errors
----------------------------
Query execution failed for dataset 'DataSet1'. (rsErrorExecutingCommand)

----------------------------
The execution failed for the shared data set 'CustomerDetailsDataSet'.  
(rsDataSetExecutionError)
----------------------------
An error has occurred during report processing. (rsProcessingAborted)

I did have a shared dataset named 'CustomerDetailsDataSet'. I opened the query (which was a full SQL query entered in text mode) in SQL Server Management Studio, and ran it there. I got error messages which clearly pointed to a certain table, where a column I had been using had been renamed and changed.

From that point, it was straightforward to modify my query so that it worked with the new column, then paste that modification into the shared dataset 'CustomerDetailsDataSet', and then nudge the report in Report Builder to recognise the change to the shared dataset.

After this fix, my reports no longer triggered this error.

Answer 2

I just dealt with this same issue. Make sure your query lists the full source name, using no shortcuts. Visual Studio can recognize the shortcuts, but your reporting services application may not be able to recognize which tables your data should be coming from. Hope that helps.

Answer 3

I enabled remote errors to pinpoint the problem.

I identified that a column in a particular dataset (one of my views) was throwing an error.

So using a tool "SQL Delta", I compared the development version of the database with the live version on the reporting server. I noticed that one of the views had an extra column on the development server, that was not on the live version of the db.

SQL Delta generated the script I needed to run to update the view on my live db.

I ran this script, re-ran the report, everything worked.

Answer 4

BIGHAP: A SIMPLE WORK AROUND FOR THIS ISSUE.

I ran into the same problem when working with SharePoint lists as the DataSource, and read the blogs above which were very helpful. I had made changes in both the DataSource and Data object names and query fields in Visual Studio and the query worked in visual Studio. I was able to deploy the report to SharePoint but when I tried to open it I received the same error.

I guessed that the issue was that I needed to redeploy both the DataSource and the DataSet to SharePoint so that that changes in the rendering tools were all synced.

I redeployed the DataSource, DataSet and the Report to sharePoint and it worked. As one of the blogs stated, although visual studio allowed the changes I made in the dataset and datasource, if you have not set visual studio to automatically redeploy datasource and dataset when you deploy the report(which can be dangerous, because this can affect other reports which share these objects) this error can occur.

So, of course the fix is that in this case you have to redeploy datasource, dataset and Report to resolve the issue.

Answer 5

Like many others here, I had the same error. In my case it was because the execute permission was denied on a stored procedure it used. It was resolved when the user associated with the data source was given that permission.

Answer 6

Using SSRS, Report Builder 3.0, MSSQL 2008 and query to an Oracle 11G database, I found that the oracle stored procedure ran well, produced consistent results with no errors. When I tried bringing the data into SSRS, I got the error as listed in OP's query. I found that the data loaded and displayed only if I removed the parameters (not a good idea). On Further examination, I found that under dataset properties>parameters I had set the start date to parameterName P_Start and parameter Value to @P_Start.

Adding the Parameter value as [@P_Start] cleared the problem, and the data loads well, with parameters in place.