Thomas Ellermanns view on System Center

This blog will discuss features, functions and views on Operations Manager and Service Manager

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

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

  • Comments 2
  • Likes

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.

Comments
  • 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.

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment