Chapter 28
New SQL Server Utilities



by Orryn Sledge

SQL Server 6.5 includes two new graphical utilities that help simplify and automate SQL Server tasks:

SQL SERVER WEB ASSISTANT

The SQL Server Web Assistant automates the generation of HyperText Markup Language ( HTML) Web pages. You can use this wizard to publish data that resides in SQL Server on the Internet or Intranet. As you can imagine, this link between SQL Server and the Internet/Intranet creates several new and exciting possibilities for the distribution of information. Following is a list of some of the uses for distributing SQL Server information in the HTML format:

USING THE SQL SERVER WEB ASSISTANT

Follow these steps to use the SQL Server Web Assistant:

  1. Double-click the SQL Server Web Assistant icon in the Microsoft SQL Server 6.5 (Common) program group. The SQL Server Web Assistant Login dialog box appears (see Figure 28.1).

    Figure 28.1.
    The SQL Server Web Assistant Login dialog box.

  2. From the SQL Server Web Assistant Login dialog box, enter the server name, login ID, and password. If you are using integrated security, select the Use Windows NT Security option (or you can select a Password option). You do not have to provide a login ID or password if you use integrated security. Click the Next button. The SQL Server Web Assistant Query dialog box appears (see Figure 28.2).

    Figure 28.2.
    The SQL Server Web Assistant Query dialog box.

  3. From the SQL Server Web Assistant Query dialog box, select your data source. This example uses a free-form text query as the data source (refer to Figure 28.2). The following list explains the data source options:

  4. Click the Next button. The SQL Server Web Assistant Scheduling dialog box appears (see Figure 28.3).

    Figure 28.3.
    The SQL Server Web Assistant Scheduling dialog box.


    TIP: Use MS-Query to automate the generation of the SQL syntax for a Web page. Build your query in MS-Query. Copy the SQL statement to the Clipboard and then paste it into the SQL Server Web Assistant. You can use stored procedures to generate Web pages when you need maximum flexibility and management of published data. Stored procedures provide flexibility through the use of temporary tables, multiple queries, cursors, and other types of Transact SQL commands that can gather and present your data (see Chapter 24, "Using Stored Procedures and Cursors," for more information on stored procedures).

  5. From the SQL Server Web Assistant Scheduling dialog box, select the scheduling options. The example in Figure 28.3 regenerates the Web page whenever the data in the titles table has changed. When you select the option to regenerate the Web page (when data changes, the following three types of triggers are generated for the titles table: INSERT, UPDATE, and DELETE). When the data is modified, the trigger executes a system procedure that regenerates the Web page.

  6. Click the Next button. The SQL Server Web Assistant File Options dialog box appears (see Figure 28.4).

    Figure 28.4.
    The SQL Server Web Assistant File Options dialog box.

  7. In the SQL Server Web Assistant File Options dialog box, enter a filename, display information, and URL link information. Click the Next button. The SQL Server Web Assistant Formatting dialog box appears (see Figure 28.5).


    TIP: To display multiple URLs in your Web page, create a table in SQL Server that contains the Internet address and then use the Yes, Add a List of ... Text from a Table option in the SQL Server Web Assistant File Options dialog box. These steps allow you to maintain a list of Internet links in a SQL Server table and then use a query to build a Web page that displays the links.


    Figure 28.5.
    The SQL Server Web Assistant Formatting dialog box.
  8. From the SQL Server Web Assistant Formatting dialog box, select a formatting style and other Web page options. Click the Finish button to generate the HTML file and any scheduling or trigger options.

Congratulations! You have just designed a Web page that will be generated automatically the next time your data changes. Use a Web browser such as Microsoft's Internet Explorer (shown in Figure 28.6) or the Netscape browser to view the contents of the Web page.

Figure 28.6.
Browsing a Web page created by the SQL Server Web Assistant.

A Behind-The-Scenes Look At The SQL Server Web Assistant

The SQL Server Web Assistant provides a tremendous amount of automation through a simple-to-use graphical interface. However, to fully implement a Web-based solution, you must understand how the wizard performs Web page automation. Understanding the wizard's functionality is useful when you need to modify an existing Web page or correct an error in a Web page. Here is a list of the components used by the wizard; the following sections detail these components:

System Procedures The following system procedures are used by the SQL Server Web Assistant to generate Web pages: sp_makewebtask, sp_dropwebtask, and sp_runwebtask (refer to Appendix C for more information on these system stored procedures). You can use these system procedures to further extend the functionality of the SQL Server Web Assistant.

HTML File The output from the SQL Server Web Assistant is an HTML Web page file. This file is a static file that does not contain any live links to SQL Server data. Therefore, if you have to modify an existing HTML file, you can regenerate the entire file or manually modify the file. Manually modifying a file is an easy way to make a quick fix to an existing Web page. Because an HTML file is an ASCII text file, you can use any ASCII text editor (Microsoft Word, Write, Notepad, and so on) to modify an existing HTML file (see Figure 28.7).

Triggers If you want your Web page to be a current reflection of your data, you will have to regenerate the Web page whenever your data changes. (Remember that the data in an HTML file is static; to reflect updates, the file must be regenerated.) To automate the generation of the HTML file, the SQL Server Web Assistant can create a set of INSERT, UPDATE, and DELETE triggers (see Figure 28.8) that schedule program calls to regenerate the Web page.

Figure 28.7.
Modifying an HTML file with an ASCII editor.

Figure 28.8.
A trigger used to regenerate a Web page.


CAUTION: BCP bypasses a table's triggers. If you are using triggers to regenerate a Web page, you must manually regenerate the Web page to reflect the data changes made with BCP.

SQL Server's Task Scheduler SQL Server's Task Scheduler is another component of the SQL Server Web Assistant. For example, if you want the wizard to regenerate the Web page on an hourly basis, the wizard will schedule a task that, in turn, calls sp_runwebtask (see Figure 28.9). The Web page is rebuilt when this system procedure is executed (see Figure 28.10).

Figure 28.9.
Task Scheduler and Web page integration.


Figure 28.10.
The call to sp_runwebtask from the Task Scheduler.

Stored Procedures When the sp_runwebtask system procedure is executed from the Task Scheduler, it calls a stored procedure generated by the SQL Server Web Assistant. This stored procedure contains the information input by the user when the original Web page was created. To determine the name of the stored procedure being called, look at EXEC sp_runwebtask @procname = syntax (refer to Figure 28.10).

MS-Query

MS-Query is a graphical query tool that can be used to browse and modify data. Other features of MS-Query include the capability to execute stored procedures, the capability to edit and create table definitions, and extensive sort and filter capabilities.

Follow these steps to use MS-Query:

  1. Double-click the MS-Query icon in the Microsoft SQL Server 6.5 (Common) program group. The Microsoft Query window appears (see Figure 28.11).

    Figure 28.11.
    The Microsoft Query window.

  2. To establish a connection to SQL Server from the Microsoft Query window, click the New Query toolbar button. The Select Data Source dialog box appears (see Figure 28.12). From this dialog box, select the appropriate data source and click the Use button. You are prompted for a login and password. Enter this information and click the OK button to continue. The Add Tables dialog box appears (see Figure 28.13).

    Figure 28.12.
    The Select Data Source dialog box.

    Figure 28.13.
    The Add Tables dialog box.

  3. From the Add Tables dialog box, select the tables you want to work with. To add a table, select the table name and click the Add button. If you select multiple tables, MS-Query automatically tries to create a WHERE clause to join the tables. After adding the appropriate tables, click the Close button. The Query1 dialog box appears (see Figure 28.14).

    Figure 28.14.
    The Query1 dialog box.

  4. From the Query1 dialog box, you can select the data you want to display by dragging and dropping the information from the top half of the window to the bottom half of the window.


NOTE: If you directly modify data in MS-Query, the data in SQL Server will be updated automatically for you. To enable editing, select the Allow Editing option from the Records menu.

Summary

The SQL Server Web Assistant and MS-Query are two graphical tools that reduce the need to manually generate Transact SQL statements. These two powerful tools often meet the needs of an administrator or developer. When a task exceeds the capabilities of these products, you can still use these tools to generate scripts that can be manually modified.


DISCLAIMER


To order books from QUE, call us at 800-716-0044 or 317-361-5400.

For comments or technical support for our books and software, select
Talk to Us.

© 1997, QUE Corporation, an imprint of
Macmillan Publishing USA, a Simon and Schuster Company.