Incident reports failing with - Query execution failed for dataset 'RelatedCIs'.

  • Article

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'.
(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.