My experience with Microsoft technology

My experience with Microsoft technology

Deploy stored procedures together with your report

When you create your own report the report authoring guide advices you to use a stored procedure rather than a normal SQL query for performances reasons. The guide states how to do that in raw XML but not from the gui. Because i needed some addtional steps I decided to write this post.

I assume you created your report and tested it with a SQL query. I will describe the process form here on.

We have two things to do:

  1. Change the report to use the stored procuder
  2. Deploy the stored procedure with a data warehouse script

The only thing which needs to be changed in your report is the method of your data set:

1 storedproc


Change it from Text to Stored Procedure. And enter the name of the stored procedure. Save your work and copy the report definition to your management pack.

In the authoring console go to the dependencies tab of your report an create a new data warehouse script.

2 newscript

The data warehouse script is used in three occasions: when the management pack gets installed, when it gets uninstalled and when it is upgraded. Therefore you need to fill in three scripts:

3 install

Notice that i grant the control permission to the database roles OpsMgrReader and OpsMgrWriter. This is not mentioned in the guide but for me it didn’t work until i granted those permissions.

4 uninstall

Nothing special here

5 upgrade

This is a screenshot from version 6.1.722.1.0 of the authoring console which is coming with the authoring resource kit availabe here . If you use the older version never select any of the bullets otherwise the console will show you an error message.

Save your management pack and import it to your management group. Watch for data warehouse related errors in the RMS eventlog.

Regards Alex


Written by alexanderschmitt

3. March 2010 at 20:50

Posted in Operations Manager

%d bloggers like this: