I ran into a funny problem the other day when working with the SCSM Incident reports.
The reports all ran fine, but when we wanted to drill furter down we got the following error:
Query execution failed for dataset 'RelatedCIs'. (rsErrorExecutingCommand)
I tried to run the report directly on the SSRS server and got little more information:
- An error has occurred during report processing. (rsProcessingAborted)
Query execution failed for dataset 'RelatedCIs'.
Could not find stored procedure
So the problem is that it can't find some stored procedures and when looking in the DataMart DB under stored procedures, there was in fact missing 4 compared to working environment:
I'm not sure what creates these stored procedures and where in the installation it has failed. So instead I logged on to a working enviroment and exported all the dbo.ServiceManager_Report_Common_SP_* and imported those into to faulty DataMart DB.
HOW TO: In SQL Server Management Studio, go to Object Explorer and navigate to the database. Right-click the database and select Tasks->Generate Scripts. A wizard will open. Follow the steps and you will be able to select all or individual database objects, including stored procedures. At the end of the wizard choose to script a text file. Load the text file to the SQL instance with the missing stored procedures and execute the queries there.
After the import of the 4 stored procedures, all the reports worked as expected.