
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
- MS-Query
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:
- Customer management. Use SQL Server to store information about your customers
and then use the SQL Server Web Assistant to generate Web pages pertaining to your
customers. Summary data, customer trends, credit data, and other types of customer
information are just a few examples of the types of information that can be published
on the Internet/Intranet.
- Proposal tracking. Use SQL Server to store hot links to proposals and
related documents and then use the SQL Server Web Assistant to generate Web pages
that contain the links to your information. This type of application is great for
a distributed sales force that has to gather information for past proposals.
- Inventory management. Use SQL Server to track and manage your inventory
and then use the Web Assistant to publish your inventory. Customers, suppliers, managers,
and salespeople can all benefit from easy access to inventory information.
USING THE SQL SERVER WEB ASSISTANT
Follow these steps to use the SQL Server Web Assistant:
- 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.
- 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.
- 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:
- Build a query from a database hierarchy: This option allows you to display tables
and columns in HTML format. With this option, you can work with multiple tables or
views. This option also provides a text box for WHERE, ORDER BY, and GROUP BY clauses.
- Enter a query as free-form text: This option allows you to manually enter SQL
syntax as the data source for your Web page. Any valid SELECT statement can be used
with the following clauses: FROM, WHERE, GROUP BY, HAVING, and ORDER BY.
- Use a query in a stored procedure: This option allows you to publish data based
on the information returned from a stored procedure.
- 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).
- 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.
- 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.
- 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.
- 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:
- sp_makewebtask, sp_dropwebtask, and sp_runwebtask system procedures
- HTML file
- Triggers
- Stored procedures
- SQL Server's Task Scheduler
- THE sp_makewebtask, sp_dropwebtask, AND sp_runwebtask
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:
- 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.
- 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.
- 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.
- 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.