Producing Results in Webpages (HTML) Using SQL 7
Have you ever wanted to make available to your customers (who may not be SQL Server savvy) a way to view SQL Server database information without having to install Access or an aspect of SQL Server tools like Enterprise Manager or Query Analyzer on their local system? Microsoft has provided the solution.
SQL Server V7 (as well as V6.5) includes a stored procedure (sp_makewebtask) that provides the capability for the results of any type of query it runs to be ported out to an HTML page (viewable using any common internet browser such as Internet Explorer or Netscape).
This article will discuss how to set up and make use of this feature. The steps involved include creating the SQL query, HTML template and SQL job. The article will also present a sample of a Web page generated via this procedure.
To start: what information do you want?
The very first thing to determine is what kind of information you want from your SQL server or its databases. Try to be as specific as possible. Also, be sure you've written the query in advance: you'll need to input it into the SQL script you've set up to run on your SQL server. The following is an example of a generic connection query that returns data back on the name of the server, current date and time and, finally, the number of connections to each existing database on that server:
SELECT "SERVER" = @@SERVERNAME GO
SELECT "TIME:" = GETDATE() GO
SELECT "DATABASE" = CONVERT(VARCHAR(25),DB_NAME(dbid)),
COUNT(*) from sysprocesses GROUP BY CONVERT(VARCHAR
(25),DB_NAME(dbid))
At this point, you should determine the needs of your customer before you decide what type of query to run. The goal of this task is to produce an HTML Web page that will allow your customer to view the results of your query via an ordinary Internet browser. Once scheduled as a job on the server, this query can run as often as needed, thus presenting the illusion of a real-time reporting page.
Once you determine which SQL query to run, you will need to create an HTML template and store it in a private directory on the local share of the SQL server. As the name implies, the template will serve as the frame for producing your Web page. Microsoft gives plenty of examples on how to do this in SQL Books Online, but actually, it is rather simple to setup. You will need to make use of some simple HTML scripting, and if you are a bit rusty on this, I would recommend purchasing an HTML book. The template can be composed any number of ways using a wide variety of colors and other fancy add-ons. The example I have included is extremely basic, but serves its purpose well. The template will need to be saved on the local share with an *.tpl extension.
<HTML>
<HEAD>
<TITLE>SQL Server Connection Count Report Web Template</TITLE>
</HEAD>
<body bgcolor="000000" text="FFFF00">
<BODY>
<H1>SQL Server Connection Count Report</H1>
<H4>Company Name</H4>
<H5>[Updated every 5 minutes]</H5>
<HR>
<P>
<TR> <TH ALIGN=CENTER>SERVER:</TH> </TR>
<%begindetail%>
<H2><%insert_data_here%></H2>
<%enddetail%>
<TR> <TH ALIGN=CENTER><I>TIME:</I></TH> </TR>
<%begindetail%>
<TH ALIGN=LEFT><I><%insert_data_here%></I></TH>
<%enddetail%>
<TABLE BORDER>
<TR> <TH ALIGN=CENTER>DATABASE</TH>
<TH ALIGN=LEFT><I># of Connections<I></TH> </TR>
<%begindetail%>
<TR> <TD> <%insert_data_here%> </TD>
<TD ALIGN=LEFT><I><%insert_data_here%></I></TD> </TR>
<%enddetail%>
</TABLE>
<%begindetail%>
<TH ALIGN=RIGHT> <H3><%insert_data_here%></H3></TH>
<%enddetail%>
<P>
<HR>
</BODY>
</HTML>
*The words in bold indicate variables that easily can be changed to suit appropriate requirements
This code produces a simple SQL Server Connections page that affords end users the ability to read query data without having anything special on their system.
The resulting Web page reflects a server called Steelwood with the generic company name underneath. I input a statement about how often the page is updated, which you can change by scheduling the job to run in Enterprise Manager. Notice the important data is enclosed in a quick and easy table for users to view. If you prefer a format other than yellow on a black background, the following is a chart of HTML 4 named colors that should work with all common internet browsers (but test them to be sure).
| Color name | Hex Pair Value | Process Color |
| Black | #000000 | K = (Black) |
| Navy | #000080 | |
| Blue | #0000FF | B = (Blue) |
| Green | #008000 | |
| Teal | #008080 | |
| Lime | #00FF00 | G = (Green) |
| Aqua | #00FFFF | |
| Maroon | #800000 | C = (Cyan) |
| Purple | #800080 | |
| Olive | #808000 | |
| Gray | #808080 | |
| Silver | #C0C0C0 | |
| Red | #FF0000 | |
| Fuchsia | #FF00FF | M = (Magenta) |
| Yellow | #FFFF00 | Y = (Yellow) |
| White | #FFFFFF |
* Table taken from Practical HTML 4 by Lee Anne Phillips, QUE Publications 1998.
Finally, to make this work, you must schedule the job to run in SQL Server Enterprise Manager. The script for this contains several items such as the SQL query that you want to run as well as the location of the template for producing the HTML page. The following is defined syntax for this located in Microsoft's Books Online:
sp_makewebtask [@outputfile =] 'outputfile', [@query =] 'query'
[, [@fixedfont =] fixedfont]
[, [@bold =] bold]
[, [@italic =] italic]
[, [@colheaders =] colheaders]
[, [@lastupdated =] lastupdated]
[, [@HTMLHeader =] HTMLHeader]
[, [@username =] username]
[, [@dbname =] dbname]
[, [@templatefile =] 'templatefile']
[, [@Web pagetitle =] 'Web pagetitle']
[, [@resultstitle =] 'resultstitle']
[
[, [@URL =] 'URL', [@reftext =] 'reftext']
| [, [@table_urls =] table_urls, [@url_query =] 'url_query']
]
[, [@whentype =] whentype]
[, [@targetdate =] targetdate]
[, [@targettime =] targettime]
[, [@dayflags =] dayflags]
[, [@numunits =] numunits]
[, [@unittype =] unittype]
[, [@procname =] procname ]
[, [@maketask =] maketask]
[, [@rowcnt =] rowcnt]
[, [@tabborder =] tabborder]
[, [@singlerow =] singlerow]
[, [@blobfmt =] blobfmt]
[, [@nrowsperpage =] n]
[, [@datachg =] table_column_list]
[, [@charset =] characterset]
[, [@codepage =] codepage]
Here is my sample script:
-- Variables that can be modified: @outputfile -- @query -- @templatefile -- @dbname -- @rowcnt -- @whentype -- Note: Template file needs to be present for sp_makewebtask to produce -- an HTML output file. -- If the query(s) is/are altered, you must alter the template to accommodate -- the data. -- -- USE master GO EXECUTE sp_makewebtask @outputfile = 'location for your final Web page', @query = 'SELECT "SERVER:" = @@SERVERNAME SELECT "TIME:" = GETDATE() SELECT "DATABASE" = CONVERT(VARCHAR(25),DB_NAME(dbid)), COUNT(*) from sysprocesses GROUP BY CONVERT(VARCHAR(25),DB_NAME (dbid))', @templatefile = 'location of your HTML template', @dbname = 'master', @rowcnt= 8, @whentype = 9 GO
When creating the job in Enterprise Manager, be alert for the following:
-
Note that in the "@query" section, the included queries are only separated by a space.
-
The highlighted red sections must be defined correctly (location of the Web page that users will view - which can be located somewhere else on the network and the location of the HTML template on the local share).
-
[@rowcnt =] rowcnt specifies the maximum number of rows to display in the generated HTML document. rowcnt is int, with a default of 0, which specifies that all rows satisfying the given query be displayed in the HTML document.
-
Concerning [@whentype =], Microsoft specifies the following in SQL books online:
[@whentype =] whentype
This specifies when to run the task that creates the HTML document. whentype is tinyint, and can have these values.
| Value | Description |
| 1. (default) | Create page now. The Web job is created, executed immediately and deleted immediately after execution. |
| 2. | Create page later. The stored procedure for creating the HTML document is created immediately, but execution of the Web job is deferred until the date and time specified by targetdate and targettime (optional). If targettime is not specified, the Web job is executed at 12:00 A.M. targetdate is required when whentype is 2. This Web job is deleted automatically after the targeted date and time have passed. |
| 3. | Create page every n day(s) of the week. The HTML document is created on day(s) specified in dayflags and at the time specified by targettime (optional), beginning with the date in targetdate. If targettime is omitted, the default is 12:00 A.M. targetdate is required when whentype is 3. The day(s) of the week are specified in dayflags, and more than one day of the week can be specified. Web jobs created with whentype is 3 are not deleted automatically and continue to run on the specified day(s) of the week until the user deletes them with sp_dropwebtask. |
| 4. | Create page every n minutes, hours, days, or weeks. The HTML document is created every n time period beginning with the date and time specified in targetdate and targettime. If targettime is not specified, the Web job is executed at 12:00 A.M. targetdate is required in this case. The job runs automatically every n minutes, hours, days, or weeks as specified by numunits and unittype, and continues to run until the user deletes them with sp_dropwebtask. |
| 5. | Create page upon request. The procedure is created without automatic scheduling. The user creates a HTML document by running sp_runwebtask and deletes it only with sp_dropwebtask. |
| 6. | Create page now and later. The HTML document is created immediately and re-created, as when whentype is 2. |
| 7. | Create page now and every n day(s) of the week. The HTML document is created immediately and re-created, as when whentype is 3, except targetdate is not required. |
| 8. | Create page now and periodically thereafter. The HTML document is created immediately and re-created, as when whentype is 4, except targetdate is not required. |
| 9. | Create page now and upon request. The HTML document is created immediately and re-created, as when whentype is 5. The task must be deleted manually. |
| 10. | Create page now and when data changes. Creates the page immediately and later whenever the data in the table changes. datachg is required with this value. |
-
Finally, the SQL Server Agent must be running when a job is scheduled to run periodically; otherwise, the HTML page is not generated.
Sounds easy now? Actually, it is and since "sp_makewebtask" has a large number of variables, functionality is not in question. With this in mind, let's review the major steps involved:
-
Determine the information desired
-
Create the SQL query that extracts the correct data
-
Create an HTML template that will serve as the frame for your query
-
Create and schedule the job in SQL Enterprise Manager
-
Finally, reap the rewards from your grateful customer, who thinks you are a Web page guru (but watch out, this could back fire ;-)
Also, be sure to review Microsoft's SQL Books Online concerning "sp_makewebtask" to discover the many other facets of this store procedure that I couldn't cover in this piece.
---
Eddy Arnold (MCP) has been working exclusively as a Microsoft SQL Server DBA for the last several years. Currently, he is one of the primary IS SQL Server DBAs for BMC Software, Inc.
Contributors : Eddy Arnold
Last modified 2005-08-10 07:22 AM
