Query Design Tools

  • Article

When creating Microsoft Systems Management Server (SMS) 2003 reports, queries can be created while in the Report SQL Statement dialog box in the SMS Administrator's console. The SMS views and associated columns are both listed, but you still need to know how to write query statements. For some administrators, it is much easier to use one of the SQL Query design tools, which are available with Microsoft SQL Server, to create the SMS report query. These tools have a variety of features that help in designing and testing queries.

SQL Server 2000 Query Analyzer

A SQL Server 2000 tool that is helpful when creating or modifying queries for SMS reports is the SQL Query Analyzer. This tool allows the user to enter a SQL statement in the full-text window, execute the statement, and view the results in the results window. Follow the procedure to start the SQL Query Analyzer:

To start SQL Query Analyzer

  1. In Microsoft Windows Server 2003, click Start, All Programs (or Programs in classic view), point to the Microsoft SQL Server program group, and click Query Analyzer.

  2. In the Connect to SQL Server dialog box, select the SQL Server name by using the SQL Server drop-down box or clicking Browse. If SQL Server is local, you will most likely keep the defaults and click OK.

The object browser in the left pane is a new feature in SQL 2000 that allows you to drill down to the SMS database and see all of the SMS SQL views and each associated column. You can drag a column to the full-text window and add it to the SQL statement. Another nice feature of the SQL 2000 Query Analyzer is that it color codes the SQL statement to improve the readability of more complex statements.

SQL Server 2000 Query Designer

The Query Designer tool in SQL Server 2000 is helpful when creating queries for SMS reports. You can copy the query from the SMS report into the DTS Query Designer and easily add views, create joins, select columns to display, add criteria, sort data, and so on. Follow the procedure to start the SQL DTS Query Designer.

To start DTS Query Designer

  1. Start SQL Server 2000 Enterprise Manager.

  2. Navigate to and click the Views node under the SMS 2003 database to list the SMS views in the display pane:

    Console Root

    + Microsoft SQL Servers

      + SQL Server Group…(local)(Windows NT)

        + Databases

          + SMS_<sitecode>

            + Views

  3. Right-click the view of your choice, point to Open View, and click Query.

The Query Designer opens with four panes: the diagram, grid, SQL, and results panes. You can start working on your query based on the view that opens, start over by removing the query statement and entering a new one in the SQL pane, or even copy the query from an existing SMS 2003 report directly into the SQL pane and modify the query to display the desired data.

SQL Server 2005 Management Studio

Writing queries in the SQL Server 2005 Management Studio is much like the DTS Query Designer in SQL Server 2000. You can copy the query from the SMS report into and easily add views, create joins, select columns to display, add criteria, sort data, and so on. Follow the procedure to create or modify a query in SQL Server Management Studio.

To create or modify queries in SQL Server Management Studio

  1. Start SQL Server Management Studio.

  2. Navigate to and click the Views node under the SMS 2003 database to list the SMS views in the display pane:

    Console Root

    + Databases

      + SMS_<sitecode>

        + Views

  3. Right-click the view of your choice and click Open View. The results pane displays, returning all rows in the selected view.

  4. Right click in the results pane, point to Pane, and click Diagram. The diagram pane displays above the results pane.

  5. Repeat the previous step to display the Criteria and SQL panes.

Just as in the Query Designer, you can now start working on your query based on the view that opens, start over by removing the query statement and entering a new one in the SQL pane, or copy the query from an existing SMS 2003 report directly into the SQL pane and modify the query to display the desired data.

SQL Design Tool Considerations

When using one of the SQL query design tools, you should be aware of the following points so that your queries and reports work as expected.

Report Prompt Query Variables

Many built-in SMS reports have report prompts. These report prompts require the user to enter a value for a specified view column, the value is stored in a variable, and the variable is then used to filter the query result set. These variables will not work in the DTS Query Designer or SQL Query Analyzer, so you must change the variable to a static value or the query will fail. The following example shows a query from an SMS report that contains a variable representing a specific collection ID and how this variable is modified so that the query design tool can be used.

Query from an SMS 2003 report:

SELECT Name
FROM v_FullCollectionMembership
WHERE CollectionID = @collid

Change the variable to the desired static value:

SELECT Name
FROM v_FullCollectionMembership
WHERE CollectionID = ‘SMS00001’

After the query has been modified in one of the query design tools and is ready to be used in an SMS report, the query can be copied into the Report SQL Statement dialog box for the report and modified so that the original report prompt variable is used replacing the static value entered above.

If you change the column order by modifying the query in a predefined report, and the report has a link to another report that requires a column number, the link can pass data from the wrong column to the target report. To prevent this, verify that the correct column numbers are specified in the link.