Microsoft SQL Server 7.0 System Administration Training Kit
You can generate Hypertext Markup Language (HTML) Web pages from SQL Server table data with the SQL Server Web Assistant Wizard. The wizard provides a user interface to the sp_makewebtask system stored procedure, which creates Web Assistant jobs. This lesson provides an overview of Web publishing and describes how to use Web Assistant to create and update Web pages.
After this lesson, you will be able to
- Create a Web page with SQL Server Web Assistant Wizard
- Use Web publishing system stored procedures
- Schedule a Web Assistant job to be executed at regular intervals
- Have a Web Assistant job update a Web page whenever data changes
Estimated lesson time: 90 minutes
How Web Publishing Works
You can generate HTML Web pages from SQL Server using the SQL Server Web Assistant. Using the Web Assistant, you can create Web Assistant jobs that publish and format information from a database. These jobs are executed on demand or automatically (see Figure 7.1).
When a Web Assistant job is created with the Web Assistant Wizard or the sp_makewebtask system stored procedure, a number of objects may be created. For all Web Assistant jobs,
- A row is added to a system table in the msdb system database. This entry records the name of the Web Assistant job and the location of the HTML file that will be generated by the job.
- A stored procedure with the same name as the job is created in the database that is specified when the job is created. This stored procedure contains the Transact-SQL query for the job. When the job is executed, the stored procedure is executed and an HTML Web page containing the query results is generated.
If a Web Assistant job is created for one-time execution, the entry in msdb and the stored procedure are deleted immediately after the job has been executed.
A Web Assistant job can be executed manually at any time using SQL Server Enterprise Manager or the sp_runwebtask system stored procedure. For Web Assistant jobs that are to execute automatically, one of the following occurs when the job is created:
- For scheduled Web Assistant jobs, a SQL Server Agent job with the same name as the Web Assistant job is created.
- For each Web Assistant job that is to execute when data changes, three triggers are created. These are named after the Web Assistant job, with a number appended to each to make the names unique.
Figure 7.1 Creating a Web page with SQL Server Web publishing
Specifying Web Page Characteristics
Web Assistant jobs produce HTML documents that contain the result set(s) of a query. When you use Web Assistant Wizard to define a Web Assistant job and the Web page that it will generate, you must supply query, update timing, file location, and formatting information.
Specifying the Query
Web Assistant Wizard allows you to select the data that is displayed in the Web page by
- Specifying a single table and selecting columns from that table
- Entering a query directly as text
- Specifying an existing stored procedure that returns one or more result sets
Automating Web Page Updates
The following table describes the scheduling options that you can use when you create a Web Assistant job.
| Scheduling option | Description |
|---|---|
| Only one time when I complete this wizard | Immediate one-time execution. After execution, the Web Assistant job is deleted. |
| On demand | Optionally runs immediately, and the Web Assistant job is saved so that it can be run later. |
| Only one time at | The job runs once at a specific date and time. |
| At regularly scheduled intervals | The job runs at regular intervals that you specify. |
| When the SQL Server data changes | The job runs when underlying SQL Server data changes. |
Scheduled Web Page Updates
When you schedule a Web Assistant job, a SQL Server Agent job is created to execute it, as shown in Figure 7.2.
Figure 7.2 Scheduling Web page updates
Do not confuse these two kinds of jobs; you will learn more about creating SQL Server Agent jobs in Chapter 13. SQL Server Agent must be running for scheduled jobs to run.
It is the SQL Server Agent job that is scheduled, not the Web Assistant job. When the SQL Server Agent job runs on schedule, it in turn executes the Web Assistant job, using the sp_runwebtask system stored procedure. The SQL Server Agent job is created by the sp_makewebtask system stored procedure.
To change the schedule for a scheduled Web Assistant job, you need to edit the schedule of the SQL Server Agent job; you cannot edit a Web Assistant job. If you delete the Web Assistant job with SQL Server Enterprise Manager or the sp_dropwebtask system stored procedure, the SQL Server Agent job is automatically deleted as well.
Triggered Web Page Updates
When you create a Web Assistant job that updates the Web page whenever data changes, three triggers are added to the table that contains the target data (see Figure 7.3). These triggers fire when data is inserted into, updated in, or deleted from the table. The Web Assistant job can query a different table than the one in which the triggers are defined.
Figure 7.3 Triggered Web page updates
The triggers execute the Web Assistant job by using the sp_runwebtask system stored procedure. If you delete the Web Assistant job with SQL Server Enterprise Manager or the sp_dropwebtask system stored procedure, the triggers are automatically removed from the table.
Setting the Output File Location
Web Assistant Wizard allows you to specify the output filename and path for a Web page. This can be on the local computer or a remote one. The folder you specify must already exist.
CAUTION
The default is the C:\Mssql7\Html folder. It is recommended that you use another folder, as the default folder stores files that are used by SQL Server Enterprise Manager. If you store your files in this folder and then later delete some of the SQL Server Enterprise Manager files by mistake, SQL Server Enterprise Manager will not work correctly.
Specifying Format Options
If you do not use an HTML template file (described in the section "Using an HTML Template File" later in this chapter), Web Assistant Wizard allows you to specify the following options for the HTML file that is generated:
- Text for the title of the Web page
- Text and point size of the title of the table that contains the query results
- Whether or not to include a time and date stamp
- Whether or not to include column headers and borders for the table that contains the query results
- Font characteristics for the table that contains the query results
- Whether one or more hyperlinks should be included in the page
- A limit for the total number of rows that a query returns and for the number of rows displayed on each page
Web Assistant Wizard provides defaults for options that you do not specify.
After a Web page is generated, you can edit it manually and add other HTML tags to enhance the formatting and presentation of the data. Do not do this for pages that are regenerated when data is updated or according to a schedule, as your changes will be overwritten the first time the page is regenerated. If you wish to be able to customize the look of pages, it is recommended that you use a template file.
Exercise: Creating a Static Web Page
In this exercise, you will start the SQL Server Web Assistant Wizard from within SQL Server Enterprise Manager to create a static Web page.
- To create a Web page using the Web Assistant Wizard
- Open SQL Server Enterprise Manager and click your server.
- On the Tools menu, click Wizards.
- Expand Management, and then double-click Web Assistant Wizard.
- Create an HTML page with the information in the following table. Accept the defaults for any options that are not listed.
| Option | Value |
|---|---|
| Database | StudyNwind |
| What do you want to name this Web Assistant job? | Current_stock |
| What data do you want to publish to the table on the Web page? | Data from the Transact-SQL statement I specify |
| Transact-SQL Query | SELECT ProductName, UnitsInStock FROM Products ORDER BY ProductName |
| When should the Web Assistant update the Web page? | Only one time when I complete this wizard |
| Where do you want to publish the Web page? | C:\Sqladmin\Exercise\Ch07\Instock.htm |
| Do you want Web Assistant to help you format the Web page | Yes, help me format the layout of the Web page? |
| What do you want to title the Web page? | Northwind Traders Current Stock |
| What do you want to title the HTML table that contains the data? | Inventory of Items |
| Write Transact-SQL to File | C:\Temp\Instock.sql |
Exercise: Viewing the Web Page
In this exercise, you will view the Web page and the source HTML created by the Web Assistant job.
- To view the created Web page
- Open Internet Explorer.
- Open the C:\Sqladmin\Exercise\Ch07\Instock.htm file and view the Web page.
- On the View menu, click Source, and then review the HTML.
Exercise: Viewing the Transact-SQL Script
In this exercise, you will view the Transact-SQL script that the Web Assistant Wizard generated.
- To view the generated Transact-SQL script
- Open SQL Server Query Analyzer and log on to the (local) server with Microsoft Windows NT authentication.
- Open the C:\Temp\Instock.sql file and review the contents.
Note that the Web Assistant Wizard generates a single call to the sp_makewebtask system stored procedure, supplying parameter values according to your choices on the wizard screens.
What is the meaning of the @whentype = 1 parameter?
Using an HTML Template File
You can use an HTML template file to format a Web page. A template provides the layout for a Web page that you create. With a template, you can specify precisely how to display database data. When the Web page is generated, the results from the query are merged with the HTML template file.
Specifying an HTML Template File
Create the template file as you would any standard HTML file. HTML template files usually have a .TPL extension.
Formatting an HTML Template File
In order to specify where the query results are to be displayed, the file must contain one of the following:
- A single <%insert_data_here%> tag at the place where you want SQL Server to merge the result of the query into the document. The result of the query is inserted as a single HTML table.
- A block starting with the <%begindetail%> tag and ending with the <%enddetail%> tag. Between these tags, specify a complete row layout, with one <%insert_data_here%> tag for each column in the query. Typically these tags would be placed within <TR></TR> and <TD></TD> tags to format the results in an HTML table.
When the Web Assistant job is executed, the results of the query will be inserted into the positions indicated by the special tags; the rest of the HTML template file will be left as is. Therefore, you have complete control over the output from the Web Assistant.
The following example shows the contents of a simple template file that can be used with a Web Assistant job to create a Web page that lists the products sold by Northwind Traders and the prices for each product. The following table explains the meaning of the HTML tags in the template. Everything in the template is standard HTML except for the <%begindetail%>, <%enddetail%>, and <%insert_data_here%> tags.
| <HTML> <HEAD> <TITLE>Northwind Traders Price List</TITLE> </HEAD> <BODY> <H1>Items For Sale</H1> <HR> <P> <TABLE BORDER> <TR><TH><I>Product Name</I></TH><TH>Price</TH></TR> <%begindetail%> <TR> <TD><I><%insert_data_here%></I></TD> <TD ALIGN=RIGHT><B>$<%insert_data_here%></B></TD> </TR> <%enddetail%> </TABLE> <P> <A HREF = "http://www.microsoft.com">Microsoft</A> <P> </BODY> </HTML> |
| HTML tags | Contents contained within tags translate to |
|---|---|
| <HTML> </HTML> | The entire HTML document |
| <HEAD> </HEAD> | Header of the document |
| <TITLE> </TITLE> | Title (usually displayed in the title bar of the browser) |
| <BODY> </BODY> | Body of the document |
| <H1> </H1> | Header—first level |
| <HR> | Horizontal rule |
| <P> | Paragraph marker |
| <TABLE BORDER> </TABLE> | Table structure with borders |
| <TR> </TR> | Table row |
| <TH> </TH> | Table column heading |
| <TD> </TD> | Table data |
| <TD ALIGN=RIGHT> </TD> | Table data, right aligned |
| <I> </I> | Italic text |
| <B> </B> | Bold text |
| <%begindetail%> <%enddetail%> | The result set format for an entire row |
| <%insert_data_here%> | Data from a single column that is returned from a Transact-SQL query |
| <A HREF> </A> | URL hyperlink |
Exercise: Examining a Template File
In this exercise, you will examine a template file that you will later use to create a Web page.
- To review a template file
- Open Notepad.
- Open C:\Sqladmin\Exercise\Ch07\Pricelst.tpl and review the contents.
Note the location of the <%insert_data_here%> tags.
Exercise: Creating and Executing a Web Assistant Job
In this exercise, you will execute a Transact-SQL script that generates a Web page based on an HTML template.
- To create and execute a Web Assistant job with the sp_makewebtask system stored procedure
- Switch to SQL Server Query Analyzer, open C:\Sqladmin\Exercise\Ch07 \Pricelst.sql, and review its contents.
- Execute the script.
Notice the value of each parameter, the name of the output file, and the reference to the C:\Sqladmin\Exercise\Ch07\Pricelst.tpl template file.
Exercise: Viewing the Web Page
In this exercise, you will view the Web page and the source HTML.
- To view the generated Web page
- Switch to Internet Explorer.
- Open the C:\Sqladmin\Exercise\Ch07\Pricelst.htm file and view the Web page.
- On the View menu, click Source, and then review the HTML.
Managing Web Assistant Jobs
You can use SQL Server Enterprise Manager or system stored procedures to manage Web Assistant jobs.
Viewing Web Assistant Jobs
To view Web Assistant jobs in SQL Server Enterprise Manager, expand Management in the console tree and then click Web Publishing. You cannot list all Web Assistant jobs using Transact-SQL.
Executing a Web Assistant Job
To execute a Web Assistant job in SQL Server Enterprise Manager, in the console tree expand Management, click Web Publishing, right-click the job in the details pane, and click Start Web Assistant Job.
You can also execute a Web Assistant job with the sp_runwebtask system stored procedure, as follows:
| sp_runwebtask [[@procname =] 'procname '][,[@outputfile = ] 'outputfile'] |
Replace procname with the name of the Web Assistant job to run and outputfile with the name of the HTML file to create. The procname parameter is so called because the stored procedure in the database is executed when the job runs. You can specify either parameter or both parameters. The parameter(s) you specify must exactly match the parameters specified when the Web Assistant job was created.
The example given here runs a Web Assistant job by using an @outputfile parameter of 'C:\Web\Myfile.html ' and a @procname parameter of 'My Web Assistant Job'.
| sp_runwebtask @procname = 'My Web Assistant Job', @outputfile = 'C:\Web\Myfile.' |
Deleting a Web Assistant Job
To delete a Web Assistant job in SQL Server Enterprise Manager, in the console tree expand Management, click Web Publishing, right-click the job in the details pane, and click Delete.
You can also delete a Web Assistant job with the sp_dropwebtask system stored procedure, as follows:
| sp_dropwebtask [[@procname =] 'procname '] [,[@outputfile = ] 'outputfile'] |
The procname parameter is so called because the stored procedure in the database is deleted when the job is deleted.
This example deletes a Web Assistant job named MYHTML that has an output file of C:\Web\Myfile.html.
| sp_dropwebtask 'MYHTML', 'c:\Web\Myfile.html' |
NOTE
When you execute sp_runwebtask or sp_dropwebtask, you must be using same database that was specified in the SQL Server Web Assistant Wizard or with the dbname parameter of the sp_makewebtask system stored procedure when the Web Assistant job was created.
Exercise: Creating a Web Assistant Job That Updates a Web Page When Data Changes
In this exercise, you will use the SQL Server Web Assistant Wizard to create a Web Assistant job that is triggered to update a Web page whenever data changes in the database.
- To create a Web Assistant job that will update a Web page whenever specified data changes
Use the SQL Server Web Assistant Wizard to create an HTML page based on the options in the following table. Accept the defaults for any options that are not listed.
| Option | Value |
|---|---|
| Database | StudyNwind |
| What do you want to name this Web Assistant job? | Web_trigger |
| What data do you want to publish to the table on the Web page? | Data from tables and columns that I select |
| What table and columns do you want to publish to the Web page? | Products table; ProductName, UnitsInStock, UnitPrice columns |
| Which rows from the table do you want to publish to the Web page? | Only those rows that meet the following criteria |
| Column | [Products].UnitsInStock |
| Operator | < |
| Value | 3 |
| When should the Web Assistant update the Web page? | When the SQL Server data changes |
| Generate a Web page when the wizard is completed. | Checked |
| What table and columns should Web Assistant monitor? | Products table; UnitsInStock column |
| Where do you want to publish the Web page? | C:\Sqladmin\Exercise\Ch07\Lowstock.htm |
| Do you want Web Assistant to help you format the layout of the Web page? | Yes, help me format the Web page |
| What do you want to title the Web page? | Northwind Traders Low Stock |
| What do you want to title the HTML table that contains the data? | Low Stock List |
| Write Transact-SQL to File | C:\Temp\Invtrig.sql |
Exercise: Viewing the Generated Script
In this exercise, you will view the Transact-SQL script that the SQL Server Web Assistant Wizard generated. This script executes sp_makewebtask with parameters that cause it to create both the Web Assistant job and the triggers that execute the job to update the page when data in the UnitsInStock column changes.
- To view the generated Transact-SQL script
- Switch to SQL Server Query Analyzer.
- Open the C:\Temp\Invtrig.sql file and review its contents.
What is the meaning of the parameters @whentype = 10 and @datachg = N'TABLE = Products COLUMN = UnitsInStock'?
Answer
Exercise: Viewing the Job
In this exercise, you will use SQL Server Enterprise Manager to view the Web Assistant job created previously.
- To view the Web_Trigger job
- In the console tree, expand Management, and then click Web Publishing.
- In the details pane, right-click the Web_Trigger job, and then click Properties.
- In the Web_Trigger Properties dialog box, review the properties of the job.
- Click Cancel to close the dialog box.
Exercise: Viewing the Trigger
In this exercise, you will use SQL Server Enterprise Manager to examine the trigger that the SQL Server Web Assistant Wizard created.
- To view the trigger
- In the console tree, expand Databases, expand the StudyNwind database, and then click Tables.
- In the details pane, right-click the Products table, point to All Tasks, and then click Manage Triggers.
- Examine all triggers on the Products table by selecting the various triggers in the Name drop-down list.
What triggers exist?
Answer
Exercise: Viewing the Web Page
In this exercise, you will view the Web page and the source HTML.
- To view the generated Web page
- Switch to Internet Explorer.
- Open the C:\Sqladmin\Exercise\Ch07\Lowstock.htm file and view the Web page.
- On the View menu, click Source, and then review the HTML.
Exercise: Testing the Trigger
In this exercise, you will insert new information into the Products table, which will cause the INSERT trigger to fire. This will execute the Web Assistant job, which will update the Low Stock Web page.
- To test the trigger
- Open SQL Server Query Analyzer, and log on to the (local) server with Microsoft Windows NT authentication.
- Type the following statements:
- Switch to Internet Explorer, and then refresh and view the C:\Sqladmin \Exercise\Ch07\Lowstock.htm file. The product Chai will have been added to the table on the Web page.
| USE StudyNwind UPDATE Products SET UnitsInStock = (UnitsInStock - 38) WHERE ProductName = 'Chai' |
Changing the value of the UnitsInStock column initiates the trigger that creates a new version of the Web page.
Lesson Summary
The SQL Server Web Assistant allows you to generate Hypertext Markup Language (HTML) Web pages from SQL Server table data. The wizard provides a user interface to the sp_makewebtask system stored procedure, which creates Web Assistant jobs. SQL Server uses triggers to automatically update the Web pages when changes have been made to the data.