After adding a T-SQL query-based dataset to a report and deploying it, the report stopped working because of the following error.
An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'NewDataset'.
(rsErrorExecutingCommand) For more information about this error
navigate to the report server on the local server machine, or enable
remote errors
Datasource : Cube + SQL DB
Dataset: Cube + SQL DB
Table on the report: Most columns filled from Cube DB. Added one more column connected T-SQL DB.
Before adding T-SQL dataset, report was showing perfectly on the site. The SSRS previews report perfectly.
The report uses a shared dataset that connects to a SQL shared datasource.
Best Answer
Often, dataset queries will fail when deployed due to security problems. Ensure that the account that the SSRS reports run under have the appropriate security for all of the db objects used in the dataset query.
Find the data source in the Report Manager. Click the down arrow located on the right side of the data source and choose "Manage". In the properties, you can determine what account is used to connect to SQL.
On the SQL database, ensure that the account used for the data source has the security privileges required to access the database objects used in the query (maybe the tables referenced in the query, or the stored procedure, depending on what the source of your dataset is).
Try connecting to SSMS with the same account that the data source is using, and run the same query. The errors that you get during this process will help identify where security privileges are needed.