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

User · Accepted Answer

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.

User · Answer

This problem was caused by an orphaned SQL Login  I ran my favorite sp fixusers script and the error was resolved   The suggestion above to look at the logs was a good one   and it led me to my answer

User · Answer

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

User · Answer

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

User · Answer

I also had a very similar issue with a very similar error message   My issue was that the database could not be connected to   In our case  we have mirrored databases and the connection string did not specify the Failover Partner   So when the database couldn t connect  it never went to the mirror and was throwing this error   Once I specified the Failover Partner in the connection string for my datasource  it resolved the issue

User · Answer

I had the similar issue showing the error      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  PrintInvoice     Solution  1  The error may be with the dataset in some cases  you can always check if the dataset is populating the exact data you are expecting by going to the dataset properties and choosing  Query Designer  and try  Run   If you can successfully able to pull the fields you are expecting  then you can be sure that there isn t any problem with the dataset  which takes us to next solution   2  Even though the error message says  Query Failed Execution for the dataset   another probable chances are with the datasource connection  make sure you have connected to the correct datasource that has the tables you need and you have permissions to access that datasource

User · Answer

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

User · Answer

Very grateful I found this great post  As for my case  the user executing the stored procedure did not have EXECUTE permissions  The solution was to grant EXECUTE permissions for the user within the stored procedure by adding below code to the end of the stored procedure   GRANT EXECUTE ON dbo StoredProcNameHere TO UsernameRunningreports GO

User · Answer

The solution for me came from GShenanigan      You ll need to check out your log files on the SSRS server for more detail  They ll be somewhere like   C  Program Files  x86  Microsoft SQL Server MSRS10 50 DEV Reporting Services LogFiles     I was able to find a permissions problem on a database table referenced by the view that was not the same one as the where the view was   I had been focused on permissions on the view s database so this helped pinpoint where the error was

User · Answer

I experienced the same issue  it was related to security not being granted to part of the tables  review your user has access to the databases  tables views functions etc used by the report

User · Answer

In addition to the above answers  it could be due to a missing SQL stored-procedure or SQL function   For example  this could be due to the function not migrating from a non-prod region to the production  prod  region

User · Answer

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

User · Answer

I was also facing the same issue - I checked below things to fix this issue    If you have recently changed pointing database-name in data-source then first check that all the store procedures for that report exist  on changed database  If there are multiple sub reports on main report then make sure each  report individually running perfectly  Also check security panel - user must have access to the databases  tables views functions for that report    Sometimes  we also need to check dataset1 - store procedure  As if you are trying to show the report with user1 and if this user doesn t have the access rights  of provided  dataset1 database  database then it will throw the same error as above so must check the user have access of dbreader in SQL Server   Also  if that store procedure contains some other database  Database2  like   Select   from XYZ inner join Database2  Table1 on     where      Then user must have the access of this database too    Note  you can check log files on this path for more details   C  Program Files Microsoft SQL Server MSRS11 SQLEXPRESS Reporting Services

User · Answer

This might be the permission issue for your view or store procedure

User · Answer

In my situation  I created a new SSRS report and new stored procedure for the dataset  I forgot to add the stored procedure to the database role that had permission to execute it  Once I added the permissions to SQL database role with EXECUTE  all was fine   The error message encountered by the user was  An error occurred during client rendering  An error has occurred during report processing  rsProcessingAborted   Query execution failed for dataset  DataSet1    rsErrorExecutingCommand  For more information

User · Answer

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

User · Answer

I got same error but this worked and solved my problem  If report is connected to Analysis server then give required permission to the user  who is accessing reporting server to view the the reports  in your model of analysis server  To do this add user in roles of model or cube  and deploy the model to your analysis server

[sql-server] SSRS Query execution failed for dataset

Examples related to sql-server

Examples related to sql-server-2008

Examples related to reporting-services

Examples related to ssrs-2008