Using Stored Procedures in a Custom Report
Updated: June 1, 2011
Applies To: Operations Manager 2007 R2
You can save a query in a Transact-SQL stored procedure to create a report. Stored procedures provide more efficiency and security over embedded SQL queries, but they must be created by a script. For more information about stored procedures, see Stored Procedure How-To Topics (http://go.microsoft.com/fwlink/?LinkId=220266).
To create a stored procedure
Open Microsoft SQL Server Management Studio and connect to the database server.
In Object Explorer, connect to an instance of the database engine, and then expand that instance.
Expand Databases, expand the OperationsManagerDW database, and then expand Programmability.
Right-click Stored Procedures, and select New Stored Procedure. A query editing pane opens.
This pane shows a script that creates the stored procedure in the database for later use. A skeleton of the script is provided for you; placeholder items that you must replace are indicated with brackets (<>).
Replace the placeholder text following
CREATE PROCEDUREwith the name that you want to give the procedure. Make a note of this name; you must have it when you design the report.
If you are using a specific user to execute the report, for example, the
readonlyuser recommended in Setting up the Environment, that user must have permission to use the stored procedure. Locate the following lines in the script:
USE [OperationsManagerDW] GO
After those lines, add this code:
GRANT EXECUTE TO readonly GO
This provides the
readonlyuser who has permission to run the script.
If you are not using parameters in this query, you can comment out (preface with
--) the two lines that begin
<@Param. If you are using parameters, provide the name of the parameter, the data type, and the default value, if any. Note the names of the parameters for later use in designing your report. For more about how to use parameters with your query, see Custom Report Parameters.
SELECTstatement with the query that you have created to use in your report.
On the toolbar, click Parse to test the syntax of your query.
After the syntax is correct, click Execute to create the procedure in the database.
Close SQL Server Management Studio.
After you have created the stored procedure, it is available to use instead of the query in any report you create.
To use a stored procedure in your report
Create a new report or open an existing report. For more information about creating a report, see Using a Report Builder. If you are creating a new report, use the wizard until you reach the point of creating the query. If you are using an existing report, right-click the dataset for this report, and then select Query. The Query Designer pane opens.
In the Query Designer, click Edit As Text. The Query Designer switches to text editing mode.
Click the Command type selection box, and then select Stored Procedure. In the text box, type the name of the stored procedure that you created earlier. Click ! to test the stored procedure.
Click OK to close the Query Designer.
The report now returns data as if you had used the Transact-SQL query directly.
The procedures discussed so far work with stored procedures that are created on the local report server. If you are using a stored procedure to be distributed with a management pack, you must add a script to the management pack to manage that stored procedure. The script requires three separate sections, for installing, uninstalling, and upgrading the stored procedure. The Authoring console has tools to assist with this.
|Perform this procedure on existing reports only. If you create the scripts in the Authoring console at the same time as you create the report definition itself, you cannot save the scripts. Create the report definition, apply it to the management pack, save the management pack, and then create the scripts as described here.|
To add a stored procedure to a management pack
Open the management pack containing the report in the Authoring console. Click the Reporting tab.
Right-click the report in which you are using the stored procedure, and select Properties. The Properties page for that report opens.
Click the Dependencies tab. In the Data Warehouse Scripts section of this tab, click Create. The Choose a unique identifier dialog box opens. Enter an identifier that you can use to locate the script later. Click OK. The Properties page for this script opens.
On the General tab, in the Name section, enter a user-readable name that can be used to identify the script. Optionally, in the Description section, enter a description of what the script is used for.
The Properties page has a tab for each script that you must provide. Click the Install tab. In this box, enter the Transact-SQL code required to install your stored procedure. This code begins with the following lines:
IF NOT EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'stored_procedure_name') BEGIN EXECUTE ('CREATE PROCEDURE dbo.[stored_procedure_name] AS RETURN 1') END GO
stored_procedure_namewith the name that you have selected for your stored procedure. After the line that shows
GO, enter the Transact-SQL code for your stored procedure. If your stored procedure is already written and saved to a file, click Load content from file, navigate to the location of the file, and click OK. If you prefer to enter the code in an external editor, click Edit in external editor, and the editor of your choice opens.
Click the Uninstall tab. In this box, enter the Transact-SQL code required to install your stored procedure. This code is usually much simpler than the installation code, as it only requires a
DROP PROCEDUREstatement. That code should look like this:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'stored_procedure_name') BEGIN DROP PROCEDURE dbo.[stored_procedure_name] END GO
stored_procedure_namewith the name that you have selected for your stored procedure. If your stored procedure is already written and saved to a file, click Load content from file, navigate to the location of the file, and click OK. If you prefer to enter the code in an external editor, click Edit in external editor, and the editor of your choice opens.
Click the Upgrade tab. In this box, enter the Transact-SQL code required to upgrade your stored procedure. This code is almost the same as the code for the installation procedure. However, the
IF NOT EXISTSclause is not required, because the upgrade will not run if the procedure does not already exist. Instead, begin with this code, which should already be present in your stored procedure:
ALTER PROCEDURE [dbo].[stored_procedure_name]
stored_procedure_namewith the name that you have selected for your stored procedure. Then add the code that defines the stored procedure. Ensure that the check box Automatically upgrade previous versions of the script is selected. If your stored procedure is already written and saved to a file, click Load content from file, navigate to the location of the file, and then click OK. If you prefer to enter the code in an external editor, click Edit in external editor, and the editor of your choice opens.
Click OK to close the script properties. The script now appears in the Data Warehouse Scripts section of the report properties. Click OK to close this Properties page.
Save the management pack, but do not deploy it yet.
You must perform an additional step that the Authoring console does not do for you. You must edit the XML directly. Open the .xml file for the management pack in the XML editor of your choice. The scripts you just created are located in the DataWarehouseScripts section of the management pack. If you examine this section of the management pack, you will see separate sections with the tags
<Upgrade>. Immediately following the Install tag is an opening
<CDATA>tag, which encloses the content of the script. The Upgrade section also has a
<CDATA>tag. The Uninstall section does not have a
<CDATA>tag; you must add it manually. Add the opening and closing
<CDATA>tags so that the Uninstall section now looks like this:
<Uninstall><![CDATA[IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'stored_procedure_name') BEGIN DROP PROCEDURE dbo.[stored_procedure_name] END GO]]></Uninstall>
Save the file, and then close the XML editor. Deploy the management pack. When the management pack is imported, the script installs the stored procedure on the local report server.