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:
Query execution failed for dataset 'RelatedCIs'.(rsErrorExecutingCommand)
Could not find stored procedure'ServiceManager_Report_Common_SP_GetCIsRelatedToWI'.
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.
Thanks, This fix worked for us, however for reasons I don't understand the reportuser wasn't granted access to the stored procedures after I copied them. I had to grant them execute access to each of the stored procedures. Once I did this then the reports worked
Hi John, I'm glad you ended up with a working environment.