You are currently offline, waiting for your internet to reconnect

How to extract an SQL statement from a Microsoft Operations Manager 2005 report

This article has been archived. It is offered "as is" and will no longer be updated.
When you try to troubleshoot Microsoft Operations Manager (MOM) Reporting Services issues, you may want to extract the SQL statement that is being used to pull the data. You may want to extract this statement to query the SystemCenterReporting database directly. This article describes how to extract an SQL statement from a MOM 2005 report.
To create editing reports for MOM 2005 Reporting Services, the computer must meet the following requirements:
  • The computer must have Microsoft Visual Studio .NET 2003 installed.
  • The computer must have SQL Server Reporting client components installed.
  • The computer must have a connection to the System Center data warehouse and to the SQL Reporting Services servers that are used for report development.
To extract the SQL statement from a MOM 2005 report, follow these steps:
  1. Select the report that you want to use, and then click the Properties tab.
  2. Click Edit, and then select a location in which to save the .rdl file.
  3. Start Visual Studio .NET 2003, point to New, and then click Project on the File menu.
  4. Under Business Intelligence Products, click Report Project, and then click OK.
  5. To create a connection to the database, right-click Shared Data Sources, and then click Add New Data Source.
  6. In the Data Link Properties dialog box, type the name of the instance in which the SystemCenterReporting database resides, click the Windows NT integrated security option, and then select the SystemCenterReporting database.
  7. Click Test Connection to validate your connection to the database, and then click OK.
  8. Right-click the new connection, and then rename it as SCDW.rds.
  9. Right-click Reports, point to Add, and then click Add Existing Item.
  10. Click the .rdl file that you saved in step 2.
  11. Double-click the report to open it in the Visual Studio .NET 2003 Design Environment.
  12. Click Preview, type the appropriate criteria, and then verify that the report returns the data that you expect.
  13. Click the Data tab, and then select the appropriate dataset from the Dataset list. This step displays the transact-SQL for the selected dataset of the report.

    Note After you extract the SQL statement from a MOM 2005 report, you will probably also have to edit the statement accordingly. For example, you may also have to supply literal values for all declared parameters.

Article ID: 947678 - Last Review: 01/15/2015 17:39:17 - Revision: 1.1

Microsoft Operations Manager (MOM) 2005

  • kbnosurvey kbarchive kbexpertiseinter kbhowto kbinfo KB947678