Producing HTML from sp_makewebtask

for RuBoard

Up to this point we've been exploring emitting HTML the old-fashioned waythrough Transact-SQL and OSQL. SQL Server includes a much better way of translating data from a database into HTML: sp_makewebtask. Sp_makewebtask exposes a full-blown HTML production engine that you can use to create tasks that produce Web pages from SQL Server data.

One of the challenges of writing a SQL Server book is in trying to avoid replicating what's already covered in the Books Online while remaining thorough enough to be comprehensive and to reassure those who've purchased the book that their money was well spent. SQL Server's online documentation has long been one of its strong points. It's far more extensive , cohesive, and generally more useful than the online documentation of the other DBMS products I regularly work with. That said, its thoroughness makes writing about topics it covers in depth challenging for those who don't want to simply rephrase what it says quite well already. Rather than rehash the info that comes in the box with the product, I'd rather spend the limited number of pages in this book teaching you things that don't.

So, rather than list the syntax and parameter list for sp_makewebtask, I'll let you research that in the Books Online. I'll explain in general terms how sp_makewebtask works, then provide some examples that should get you started using it yourself.

Sp_makewebtask is a stored procedure that creates a SQL Server job that produces HTML from data stored in a SQL Server database. It can schedule this job, run it immediately, or both. For scheduled jobs, sp_runwebtask can be used to invoke them manually.

There are nearly three dozen parameters that you can pass to sp_makewebtask. The two most important ones are @query and @outputfile. The @query parameter specifies a query to produce the data you want to display, and @outputfile specifies the name of the file in which to save the newly generated HTML. Here's a call to sp_makewebtask that produces the same table we built ourselves using plain Transact-SQL (Listing 11-4):

Listing 11-4 sp_makewebtask produces our table with one line of code.

 EXEC sp_makewebtask @outputfile = 'C:\temp\cust_table.HTML', @query='SELECT CompanyName, CustomerID FROM Northwind..Customers ORDER BY CompanyName' , @lastupdated=0,@resultstitle='  ' 

(Results abridged)

In addition to the @query and @outputfile parameters, we also specify the @lastupdated and @resultstitle parameters. The @lastupdated parameter takes a 1 or 0 indicating whether you want a "Last Updated" line included on the Web page. Here we disable it by specifying 0. The @resultstitle parameter specifies a page heading string. We pass two spaces to it in order to disable the display of a results title. The default is "Query Results."

One parameter that we didn't specify is the @whentype parameter. It takes an integer specifying when to create the Web page. The page can be created immediately (the default), when requested , when scheduled, or some combination of the three. By not specifying the parameter, we leave it at its default (1) which simply creates the page immediately.

Hyperlinks

Sp_makewebtask can also include hyperlinks on the pages it creates. You can specify a single URL directly or supply a query that provides a list of them. Here's an example that adds a single URL to the bottom of a generated page (Listing 11-5):

Listing 11-5 sp_makewebtask can optionally include hyperlinks on the pages it creates.

 EXEC sp_makewebtask @outputfile = 'C:\temp\cust_table.HTML', @query='SELECT CompanyName, CustomerID FROM Northwind..Customers ORDER BY CompanyName', @lastupdated=0, @resultstitle='  ', @URL='http://www.khen.com',@reftext='Ken Henderson''s Home Page' 

(Results abridged)

  Ken Henderson's Home Page  

Note the hyperlink at the bottom of the page. Sp_makewebtask can also retrieve URLs from a query that you supply. This query needs to supply both the URL and the display text for each hyperlink you want to include. An example is presented in Listing 11-6:

Listing 11-6 You can supply a table of hyperlinks for sp_makewebtask to use.

 CREATE TABLE WebSites(URL varchar(100), URL_text varchar(100) NULL) GO INSERT WebSites VALUES ('http://www.awl.com', 'Addison-Wesley') INSERT WebSites VALUES ('http://www.khen.com','Ken Henderson''s Home Page') GO EXEC sp_makewebtask @outputfile = 'C:\temp\cust_table.HTML', @query='SELECT CompanyName, CustomerID FROM Northwind..Customers ORDER BY CompanyName', @lastupdated=0, @resultstitle='  ', @table_urls = 1, @url_query= 'SELECT URL, URL_text FROM WebSites' 

(Results abridged)

  Addison-Wesley   Ken Henderson's Home Page  

Note the hyperlinks at the bottom of the page. These are added to the page through two key parameters: @table_urls and @url_query. @table_urls tells sp_makewebtask to generate a list of URLs using the query specified in @url_query. Note that @URL and @reftext are mutually exclusive of @table_urls and @urll_query. You can specify one pair or the other, but not both.

Templates

The ability to configure most of what sp_makewebtask does through parameters is nice, but an even more powerful and more flexible approach is to use HTML templates. Sp_makewebtask supports the use of HTML templates with placeholders indicating where to insert data. This gives you complete control over formatting and placement of data, and allows you to create sophisticated "data-driven" Web pages. Here's an example of an HTML template (Listing 11-7):

Listing 11-7 A basic HTML template.

 <HTML> <HEAD> <TITLE>An HTML template that displays Customer data</TITLE> <BODY> <H1>Customers</H1> <P> <%insert_data_here%> <P> <A HREF = "http://www.awl.com">Addison-Wesley</A><P> <A HREF = "http://www.khen.com">Ken Henderson's Home Page</A><P> </BODY> </HTML> 

The key here is the <%insert_data_here%> placeholder. It specifies where sp_makewebtask inserts the data returned by the query supplied in @query. Here's a query (Listing 11-8) that makes use of the template:

Listing 11-8 The Customers table listed using a template.

 EXEC sp_makewebtask @outputfile = 'C:\temp\cust_table.HTML', @query='SELECT CompanyName, CustomerID FROM Northwind..Customers ORDER BY CompanyName', @templatefile='c:/temp/cust_table.htp', @lastupdated=0, @resultstitle='  ' 

(Results abridged)

  Addison-Wesley   Ken Henderson's Home Page  

Although the ability to control the HTML completely that precedes and follows the query result set certainly affords a great deal of flexibility, for sophisticated, professional-looking Web pages you need the ability to control the formatting of the result set itself. You can use templates to do this as well. Rather than simply inserting the result set as one contiguous monolithic piece, you can specify formatting for each column in the result set. Listing 11-9 presents a template that does this:

Listing 11-9 A more sophisticated template that allows row and column formatting.

 <HTML> <HEAD> <TITLE>An HTML template that displays Customer data</TITLE> <BODY> <H1>Customers</H1> <P> <TABLE BORDER> <TR> <TH><B>Company Name</B></TH><TH><B>Customer ID</B></TH></TR> <%begindetail%> <TR><TD><%insert_data_here%></TD><TD><I><%insert_data_here%></I></TD></TR> <%enddetail%> </TABLE> <P> <A HREF = "http://www.awl.com">Addison-Wesley</A><P> <A HREF = "http://www.khen.com">Ken Henderson's Home Page</A><P> </BODY> </HTML> 

Notice the <%begindetail%> and <%enddetail%> placeholders. They envelop the body of the table that sp_makewebtask generates. Specifically, they allow you to control the formatting of the rows in the table. Notice that we've used HTML <I> tags to italicize the second column. Using standard HTML tags, you can bold columns, underline them, change the display font or color , and so forth. The <%insert_date_here%> placeholder we used earlier indicates where to insert each column ( columns are inserted in the order they're returned by the query). Listing 11-10 is the query that uses this template and the resulting Web page:

Listing 11-10 The table produced by the custom template.

 EXEC sp_makewebtask @outputfile = 'C:\temp\cust_table.HTML', @query='SELECT CompanyName, CustomerID FROM Northwind..Customers ORDER BY CompanyName', @templatefile='c:\ temp\ cust_table2.htp', @lastupdated=0, @resultstitle='  ' 

(Results abridged)

  Addison-Wesley   Ken Henderson's Home Page  

Using templates and HTML's ability to control text layout and formatting, you can create sophisticated pages with little or no coding. Moreover, by standardizing your Web page production using templates, you give your Web site a uniform look and feel, and can take advantage of more advanced features such as cascading style sheets, DHTML, and embedded controls.

for RuBoard