sp_makewebtask

sp_makewebtask (version 6.5)

Creates a task that produces an HTML document. This HTML document contains data returned by executed queries.

Syntax

sp_makewebtask {@outputfile = 'outputfile', @query = 'query'}
[, [@fixedfont = fixedfont,] [@bold = bold,] [@italic = italic,]
[@colheaders = colheaders,] [@lastupdated = lastupdated,]
[@HTMLHeader = HTMLHeader,] [@username = username,]
[@dbname = dbname,] [@templatefile = 'templatefile',]
[@webpagetitle = 'webpagetitle',] [@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]]

@outputfile

Specifies the location of the generated HTML file on the computer that is running SQL Server and can be a UNC name if the file is to be created on a remote computer. This parameter is required and must be unique for each task created with sp_makewebtask. The outputfile variable is of varchar datatype and has a maximum of 255 characters.

@query

Specifies the query to be run. This parameter is required. Query results are displayed in the HTML document in a table format when the task is run with sp_runwebtask. Multiple SELECT queries can be specified and will result in multiple tables being displayed in the @outputfile. The @query parameter is of text datatype.

@fixedfont

Specifies that the query results be displayed in a fixed font (1) or a proportional font (0). Fixed font (1) is the default. The fixedfont variable is of tinyint datatype.

@bold

Specifies that the query results be displayed in a bold font (1) or non-bold font (0). Non-bold (0) is the default. The bold variable is of tinyint datatype.

@italic

Specifies that the query results be displayed in an italic font (1) or non-italic font (0). Non-italic (0) is the default. The italic variable is of tinyint datatype.

@colheaders

Specifies that the query results be displayed with column headers (1) or no column headers (0). Column headers (1) is the default. The colheaders variable is of tinyint datatype.

@lastupdated

Specifies whether the generated HTML document displays a Last Updated: timestamp indicating the last updated date and time (1) or no timestamp (0). Timestamp (1) is the default. The timestamp appears one line before the query results in the HTML document. The lastupdated variable is of tinyint datatype.

@HTMLHeader

Specifies the HTML formatting code for displaying the text contained in the @resultstitle variable. The HTMLHeader variable is of tinyint datatype. These are the values.



Value	HTML formatting code
1	H1
2	H2
3	H3
4	H4
5	H5
6	H6

@username

Specifies the username for executing the query. The default is the current user. The username variable is of varchar datatype and has a maximum of 30 characters. The sa or dbo can specify another @username.

@dbname

Specifies the database name to run the query on. If a database name is not given, @dbname defaults to the current database. The dbname variable is of varchar datatype and has a maximum of 30 characters.

@templatefile

Specifies the path of the template file used to generate the HTML document. The template file contains information about formatting characteristics for HTML documents and contains the tag <%insert_data_here%>, which indicates the position to add the query results in an HTML table. There are no spaces between the less than sign (<) and the "%i" of "%insert" and between the "e%" of "here%" and the greater than sign (>). The templatefile variable is of varchar datatype and has a maximum of 255 characters.

There are two ways to specify the location of the results of a query in a template file:

Include the tag <%insert_data_here%>, which indicates the position to add the query results in an HTML table. There are no spaces between the less than sign (<) and the "%i" of "%insert" and between the "e%" of "here%" and the greater than sign (>).
Specify a complete row format between the keywords <%begindetail%> and <%enddetail%> including <TR>, </TR>, <TD>, and </TD> HTML tags. For each column to display in the results set, include the tag <%insert_data_here%> inside the row format keywords. Specifying the complete row format produces a more precise layout of the results set. When used with this complete row format, @colheaders is ignored.

If @templatefile is specified, the following parameters are ignored when the HTML file is generated:

@bold @singlerow

@fixedfont @tabborder

@HTMLHeader @table_urls

@italic @URL

@lastupdated @url_query

@reftext @webpagetitle

@resultstitle

@webpagetitle

Specifies the title of the HTML document. The default is SQL Server Web Assistant. The webpagetitle variable is of varchar datatype and has a maximum of 255 characters.

@resultstitle

Specifies the title displayed above the query results in the HTML document. The default title is Query Results. The resultstitle variable is of varchar datatype and has a maximum of 255 characters.

@URL

Specifies a hyperlink to another HTML document. The hyperlink is placed after the query results and at the end of the HTML document. If this parameter is specified, @reftext must also be specified, and @table_urls and @url_query cannot be specified. The URL variable is of varchar datatype and has a maximum of 255 characters.

Important If @table_urls = 1, then @url_query must be included to specify the query to be executed for retrieving hyperlink information, and @URL and @reftext cannot be specified. Information is specified either in @URL and @reftext, or in @url_query and @table_urls.

@reftext

Specifies the hyperlink that describes to what HTML document the hyperlink should take the user. The hyperlink text describes the destination and the hyperlink address comes from the URL in the @URL parameter. The reftext variable is of varchar datatype and has a maximum of 255 characters.

@table_urls

Specifies whether hyperlinks will be included on the HTML document (1) and whether the hyperlinks will come from a SELECT statement executed on Microsoft SQL Server. A value of 0 (the default) indicates that there is no query that will generate hyperlinks for the HTML. @URL and @reftext may still be specified for a single hyperlink. A value of 1 indicates that a list of hyperlinks will be created by using @url_query. The table_urls variable is of tinyint datatype.

@url_query

Specifies the SELECT statement to create the URL and its hyperlink text. URLs and hyperlink text come from a SQL Server table. With this parameter, it is possible to generate multiple URLs with associated hyperlinks. Use @url_query with @table_urls. The url_query variable is of varchar datatype with a maximum of 255 characters. The url_query variable must return a results set containing two columns: The first column is the address of a hyperlink and the second column describes the hyperlink. The number of hyperlinks inserted into the HTML document equals the number of rows returned by executing @url_query.

@whentype

Specifies when to run the task that creates the HTML document. The whentype variable is of tinyint datatype. These are the possible values.



Value	Description
1	Create page now. The web task will be created, executed immediately, and deleted immediately after execution. This is the default.
2	Create page later. The stored procedure for creating the HTML document will be created immediately, but execution of the web task is deferred until the date and time specified by @targetdate and @targettime (optional). If no @targettime is specified, the web task will be executed at 12:00 a.m. @targetdate is required when @whentype = 2. This web task will be deleted automatically after the targeted date and time has passed.
3	Create page every n day(s) of the week. The HTML document will be created on day(s) specified in @dayflags and at the time specified by @targettime (optional), beginning with the date in @targetdate. If no @targettime value is specified, the default is 12:00 a.m. The @targetdate parameter is required when @whentype = 3. The day(s) of the week are specified in the @dayflags parameter. More than one day of the week can be specified with the @dayflags parameter. Web tasks created with @whentype = 3 will not be deleted automatically and continue to run on the specified day(s) of the week until the user deletes them by using 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 (optional). If no @targettime is specified, the Web task will be executed at 12:00 a.m. The @targetdate parameter is required in this case. The task will run automatically every n minutes, hours, days, or weeks as specified by the @numunits and @unittype parameters. The tasks will run until the user deletes them by using 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 by using sp_dropwebtask.
6	Create page now and later. The HTML document is created immediately and re-created according to @whentype = 2.
7	Create page now and every n day(s) of the week. The HTML document is created immediately and re-created according to @whentype = 3, except no @targetdate is required.
8	Create page now and periodically thereafter. The HTML document is created immediately and re-created according to @whentype = 4, except no @targetdate is required.
9	Create page now and upon request. The HTML document is created immediately and re-created according to @whentype = 5. The task must be deleted manually.

@targetdate

Specifies the date the page should be built. The format is YYYYMMDD. In @whentype > 3 cases where @targetdate is important but not supplied, the current date is used. For @whentype = 2 (later), 3 (dayofweek), 4 (periodic), and 6 (now and later), the @targetdate parameter is required. The targetdate variable is of int datatype.

@targettime

Specifies the time the HTML document should be created. When a starting time is important but not supplied, 12:00 a.m. is the default. The format is HHMMSS. The targettime variable is of int datatype.

@dayflags

Specifies what days to update the HTML document. To specify certain days, add the values together. To specify Monday and Thursday, for example, use @dayflags = 18. The dayflags variable is of tinyint datatype. This parameter is required for @whentype = 3 (dayofweek), @whentype = 7 (now and dayofweek); and in cases in which @whentype = 4 (periodic) and @whentype = 8 (now and periodic) where the @unittype = 3 (weeks). These are the values and their descriptions.



Value	Day of the week
1	Sunday
2	Monday
4	Tuesday
8	Wednesday
16	Thursday
32	Friday
64	Saturday

@numunits

Specify how often to update the HTML document. Values range from 1 to 255. This parameter is used only when @whentype = 4 (periodic) or @whentype = 8 (now and periodically thereafter). For example, if @whentype = 4, @numunits = 6, and @unittype = 1 (hours), the specified HTML document will be updated every six hours. The numunits variable is of tinyint datatype.

@unittype

Specifies how often the HTML document should be updated for @numunits = 4 (periodic) or @whentype = 8 (now and later). Use 1 (hours), 2 (days), 3 (weeks), or 4 (minutes). The unittype variable is of tinyint datatype.

@procname

Specifies the procedure or task name for the HTML document. If no @procname is specified, the procedure name generated by sp_makewebtask is in the form of Web_YYMMDDHHMMSS<spid>. If it is user-specified, the procedure name must meet the conditions for valid procedure names and the procedure name must be unique. The procname variable is of varchar datatype and has a maximum of 28 characters.

@maketask

Specifies whether a task should be created to execute an internal stored procedure that generates an HTML document. The default is to build the task (1). A value of 0 creates the stored procedure. The maketask variable is of tinyint datatype.

@rowcnt

Specifies the maximum number of rows to display in the generated HTML document. The default is to specify that all rows that satisfy the given query will be displayed in the HTML document (0). The rowcnt variable is of int datatype.

@tabborder

Specifies whether a border should be drawn around the results table (1, the default) or no border should be drawn around the results table (0). The taborder variable is of tinyint datatype.

@singlerow

Specifies whether the results are to be displayed as one row per page. If @singlerow is 0 (the default) all results appear on the same page and in the same table. With a value of 1, @singlerow causes a new HTML page to be generated for every qualifying row in the results set. Successive HTML pages are generated with a number appended to the specified output_filename. For example, if web.html is specified as the output filename, by using @singlerow = 1, pages are called web1.html, web2.html, and so on. The singlerow variable is of tinyint datatype.

@blobfmt

Specifies whether all columns of text or image datatypes should be embedded in the same results page (NULL, the default) or whether these fields should be saved in another page and linked to the main HTML document by an URL. To place the text or image data in a separate HTML page, use the following format:

"%n% FILE=output_filename TPLT=template_filename 
URL=url_link_name..."

where

n: Is the column number in the results list corresponding to a text field, and n+1 is the URL hyperlink text to the separate text or image HTML file. If a template_filename is provided, the <%insert_data_here%> marker should be used to indicate the data insertion point.
Output filenames end with a number indicating successive rows similar to @singlerow. The output_filename parameter is required, but the template_filename and url_link_name parameters are optional. The FILE output_filename is the full path to the output_file location. If provided, the url_link_name is the http:// link to the file that is accessible through the World Wide Web. If the url_link_name parameter is not provided, then the full physical filename preceeded with the "file:///" tag will be used as the url_link_name. The same syntax in @blobfmt ("%n% FILE=...") can be repeated for multiple text or image columns.

Note Do not add spaces before or after the equal sign (=) and do not put filenames in quotation marks (').

The URL text is part of the results set and is always the column after the original text or image column. This URL text column will not be displayed as a separate column in the results set. The blobfmt variable is of text or image datatype.

Remarks

For scheduled tasks, all errors are reported in the Windows NT event log on the computer running SQL Server and will have a source of xpsqlweb.

Important The sp_dropwebtask, sp_makewebtask, and sp_runwebtask stored procedures can be run only on Microsoft SQL Server version 6.5 databases. Running these procedures on a database of a previous version will return errors.

The SQL Server Web Assistant provides an interface for using the sp_makewebtask stored procedure. For more information about creating web pages with the Web Assistant, see SQL Server Web Assistant earlier in this document.

Fonts available for HTML documents depend upon the capabilities of your web browser software. Consult your browser software for information about font availability and HTML formatting.

Examples

A. Create Multiple Queries Using a Template File

This example creates an HTML document now and upon request and retrieves five book titles and prices, five author first and last names, five publisher names, and five author first and last names. In this document, placement of data is specified by the <%insert_data_here%> marker.

This is the template file:

<HTML>
  
<HEAD>
  
<TITLE>SQL Server Multiple Queries with Template Web Sample</TITLE>
  
<BODY>
  
<H1>Books For Sale</H1>
<HR>
  
  
<P>
<TABLE BORDER>
<TR> <TH><I>Title</I></TH> <TH><B>Price</B></TH> </TR>
<%begindetail%>
<TR> <TD><I> <%insert_data_here%> </I> </TD>
    <TD ALIGN=RIGHT><B> $<%insert_data_here%></B></TD> </TR>
<%enddetail%>
</TABLE>
<P>
  
<HR>
  
<%insert_data_here%>
  
  
<P>
  
<TABLE BORDER>
<TR> <TH ALIGN=CENTER>ID</TH> <TH ALIGN=LEFT><I>Publiher's Name</I></TH> </TR>
<%begindetail%>
<TR> <TD> <%insert_data_here%> </TD>
    <TD ALIGN=LEFT><I> <%insert_data_here%></I></TD> </TR>
<%enddetail%>
</TABLE>
  
<HR>
  
<%insert_data_here%>
  
  
<P>
  
  
<A HREF = "http://www.w3.org.pub/WWW/">The World Wide Web Consortium</A><P>
<A HREF = "http://www.w3.org/hypertext/WWW/MarkUp/html3/contents.html">HTML 3 specs</A><P>
<A HREF = "http://www.microsoft.com">MICROSOFT</A><P>
  
  
</BODY>
  
</HTML>

This is the query:

USE pubs
go
EXECUTE sp_makewebtask @outputfile = 'C:\WEB\MULTIPLE.HTM',
@query = 'SELECT title, price FROM titles SELECT au_lname, au_fname 
FROM authors SELECT pub_id, pub_name FROM publishers SELECT au_lname, 
au_fname FROM authors', @templatefile = 'C:\WEB\MULTIPLE.TPL',
@dbname = 'PUBS', @rowcnt = 5, @whentype = 9
go

This is the results set:

<HTML>
  
<HEAD>
  
<TITLE>SQL Server Multiple Queries with Template Web Sample</TITLE>
  
<BODY>
  
<H1>Books For Sale</H1>
<HR>
  
  
<P>
<TABLE BORDER>
<TR> <TH><I>Title</I></TH> <TH><B>Price</B></TH> </TR>
  
<TR> <TD><I> The Busy Executive's Database Guide </I> </TD>
    <TD ALIGN=RIGHT><B> $19.99</B></TD> </TR>
  
<TR> <TD><I> Cooking with Computers: Surreptitious Balance Sheets </I> </TD>
    <TD ALIGN=RIGHT><B> $11.95</B></TD> </TR>
  
<TR> <TD><I> You Can Combat Computer Stress! </I> </TD>
    <TD ALIGN=RIGHT><B> $2.99</B></TD> </TR>
  
<TR> <TD><I> Straight Talk About Computers </I> </TD>
    <TD ALIGN=RIGHT><B> $19.99</B></TD> </TR>
  
<TR> <TD><I> Silicon Valley Gastronomic Treats </I> </TD>
    <TD ALIGN=RIGHT><B> $19.99</B></TD> </TR>
  
</TABLE>
<P>
  
<HR>
  
  
<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>au_lname</TH><TH ALIGN=LEFT>au_fname</TH></TR>
<TR><TD NOWRAP><TT>White</TT></TD><TD NOWRAP><TT>Johnson</TT></TD></TR>
<TR><TD NOWRAP><TT>Green</TT></TD><TD NOWRAP><TT>Marjorie</TT></TD></TR>
<TR><TD NOWRAP><TT>Carson</TT></TD><TD NOWRAP><TT>Cheryl</TT></TD></TR>
<TR><TD NOWRAP><TT>O'Leary</TT></TD><TD NOWRAP><TT>Michael</TT></TD></TR>
<TR><TD NOWRAP><TT>Straight</TT></TD><TD NOWRAP><TT>Dean</TT></TD></TR>
</TABLE>
<HR>
  
  
  
<P>
  
<TABLE BORDER>
<TR> <TH ALIGN=CENTER>ID</TH> <TH ALIGN=LEFT><I>Publiher's Name</I></TH> </TR>
  
<TR> <TD> 0736 </TD>
    <TD ALIGN=LEFT><I> New Moon Books</I></TD> </TR>
  
<TR> <TD> 0877 </TD>
    <TD ALIGN=LEFT><I> Binnet & Hardley</I></TD> </TR>
  
<TR> <TD> 1389 </TD>
    <TD ALIGN=LEFT><I> Algodata Infosystems</I></TD> </TR>
  
<TR> <TD> 1622 </TD>
    <TD ALIGN=LEFT><I> Five Lakes Publishing</I></TD> </TR>
  
<TR> <TD> 1756 </TD>
    <TD ALIGN=LEFT><I> Ramona Publishers</I></TD> </TR>
  
</TABLE>
  
<HR>
  
  
<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>au_lname</TH><TH ALIGN=LEFT>au_fname</TH></TR>
<TR><TD NOWRAP><TT>White</TT></TD><TD NOWRAP><TT>Johnson</TT></TD></TR>
<TR><TD NOWRAP><TT>Green</TT></TD><TD NOWRAP><TT>Marjorie</TT></TD></TR>
<TR><TD NOWRAP><TT>Carson</TT></TD><TD NOWRAP><TT>Cheryl</TT></TD></TR>
<TR><TD NOWRAP><TT>O'Leary</TT></TD><TD NOWRAP><TT>Michael</TT></TD></TR>
<TR><TD NOWRAP><TT>Straight</TT></TD><TD NOWRAP><TT>Dean</TT></TD></TR>
</TABLE>
<HR>
  
  
  
<P>
  
  
<A HREF = "http://www.w3.org.pub/WWW/">The World Wide Web Consortium</A><P>
<A HREF = "http://www.w3.org/hypertext/WWW/MarkUp/html3/contents.html">HTML 3 specs</A><P>
<A HREF = "http://www.microsoft.com">MICROSOFT</A><P>
  
  
</BODY>
  
</HTML>

B. Create Hyperlinks

This example creates a two-column table called my_favorite_sites. The first column url_def is the URL to a specific web location, and the second column display_text, is the hyperlink text for the corresponding URL. After creating the table and filling it with values, the HTML document is created.

This is the query:

USE pubs
go
CREATE TABLE my_favorite_web_sites(url_def varchar(255), display_text varchar(255) NULL)
go
INSERT my_favorite_web_sites(url_def, display_text) 
VALUES ('http://www.micsosoft.com', 'Microsoft Home Page')
INSERT my_favorite_web_sites(url_def) VALUES ('http://www.yahoo.com')
INSERT my_favorite_web_sites(url_def, display_text) 
VALUES ('http://www.w3.org.pub/WWW/', 'The World Wide Web Consortium')
INSERT my_favorite_web_sites(url_def, display_text) 
VALUES ('http://www.nasa.gov', 'NASA  Home Page')
go
EXECUTE sp_makewebtask @outputfile = 'C:\WEB\URL.HTM', 
@query='SELECT title, price FROM titles ORDER BY price desc', 
@table_urls = 1, @tabborder = 0, @lastupdated=0, @colheaders = 0, 
@url_query= 'SELECT url_def, display_text FROM 
my_favorite_web_sites', @whentype = 9
go

This is the results set:

<HTML>
  
<HEAD>
  
<TITLE>Microsoft SQL Server Web Assistant</TITLE>
  
<BODY>
  
<H1>Query Results</H1>
<HR>
  
<P>
<P><TABLE BORDER=0>
<TR><TD NOWRAP><TT>But Is It User Friendly?</TT></TD><TD NOWRAP><TT>22.95</TT></TD></TR>
<TR><TD NOWRAP><TT>Computer Phobic AND Non-Phobic Individuals: Behavior Variations</TT></TD><TD NOWRAP><TT>21.59</TT></TD></TR>
<TR><TD NOWRAP><TT>Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean</TT></TD><TD NOWRAP><TT>20.95</TT></TD></TR>
<TR><TD NOWRAP><TT>Secrets of Silicon Valley</TT></TD><TD NOWRAP><TT>20.00</TT></TD></TR>
<TR><TD NOWRAP><TT>The Busy Executive's Database Guide</TT></TD><TD NOWRAP><TT>19.99</TT></TD></TR>
<TR><TD NOWRAP><TT>Straight Talk About Computers</TT></TD><TD NOWRAP><TT>19.99</TT></TD></TR>
<TR><TD NOWRAP><TT>Silicon Valley Gastronomic Treats</TT></TD><TD NOWRAP><TT>19.99</TT></TD></TR>
<TR><TD NOWRAP><TT>Prolonged Data Deprivation: Four Case Studies</TT></TD><TD NOWRAP><TT>19.99</TT></TD></TR>
<TR><TD NOWRAP><TT>Sushi, Anyone?</TT></TD><TD NOWRAP><TT>14.99</TT></TD></TR>
<TR><TD NOWRAP><TT>Cooking with Computers: Surreptitious Balance Sheets</TT></TD><TD NOWRAP><TT>11.95</TT></TD></TR>
<TR><TD NOWRAP><TT>Fifty Years in Buckingham Palace Kitchens</TT></TD><TD NOWRAP><TT>11.95</TT></TD></TR>
<TR><TD NOWRAP><TT>Is Anger the Enemy?</TT></TD><TD NOWRAP><TT>10.95</TT></TD></TR>
<TR><TD NOWRAP><TT>Emotional Security: A New Algorithm</TT></TD><TD NOWRAP><TT>7.99</TT></TD></TR>
<TR><TD NOWRAP><TT>Life Without Fear</TT></TD><TD NOWRAP><TT>7.00</TT></TD></TR>
<TR><TD NOWRAP><TT>You Can Combat Computer Stress!</TT></TD><TD NOWRAP><TT>2.99</TT></TD></TR>
<TR><TD NOWRAP><TT>The Gourmet Microwave</TT></TD><TD NOWRAP><TT>2.99</TT></TD></TR>
<TR><TD NOWRAP><TT>The Psychology of Computer Cooking</TT></TD><TD NOWRAP>n/a</TD></TR>
<TR><TD NOWRAP><TT>Net Etiquette</TT></TD><TD NOWRAP>n/a</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
<A HREF = "http://www.yahoo.com">http://www.yahoo.com</A><P>
<A HREF = "http://www.w3.org.pub.WWW/">The World Wide Web Consortium</A><P>
  
  
</BODY>
  
</HTML>

C. Execute Multiple Queries with Single Row Mode

This example creates four HTML documents from multiple queries and uses the single row mode.

This is the query:

USE pubs
go
EXECUTE sp_makewebtask @outputfile = 'C:\WEB\SROW.HTM', 
@query = 'SELECT title, price FROM titles ORDER BY price desc 
SELECT au_lname, au_fname FROM authors WHERE state = "CA" ', 
@fixedfont = 0, @webpagetitle = 'Single row SQL Web Assistant', 
@resultstitle = 'One row per page results', @singlerow = 1, 
@rowcnt = 4,@URL = "http://www.microsoft.com", 
@reftext = 'Microsoft Home Page'
go

This is the first file of the results set called C:\WEB\SROW1.HTM:

<HTML>
  
<HEAD>
  
<TITLE>Single row SQL Web Assistant</TITLE>
  
<BODY>
  
<H1>One row per page results</H1>
<HR>
  
<PRE>Last updated: Jun 5 1996  3:15PM</PRE>
  
<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>title</TH><TH ALIGN=LEFT>price</TH></TR>
<TR><TD NOWRAP>But Is It User Friendly?</TD><TD NOWRAP>22.95</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
  
  
</BODY>
  
</HTML>

This is the second file of the results set called C:\WEB\SROW2.HTM:

<HTML>
  
<HEAD>
  
<TITLE>Single row SQL Web Assistant</TITLE>
  
<BODY>
  
<H1>One row per page results</H1>
<HR>
  
<PRE>Last updated: Jun 5 1996  3:15PM</PRE>
  
<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>title</TH><TH ALIGN=LEFT>price</TH></TR>
<TR><TD NOWRAP>Computer Phobic AND Non-Phobic Individuals: Behavior Variations</TD><TD NOWRAP>21.59</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
  
  
</BODY>
  
</HTML>

This is the third file of the results set called C:\WEB\SROW3.HTM:

<HTML>
  
<HEAD>
  
<TITLE>Single row SQL Web Assistant</TITLE>
  
<BODY>
  
<H1>One row per page results</H1>
<HR>
  
<PRE>Last updated: Jun 5 1996  3:15PM</PRE>
  
<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>au_lname</TH><TH ALIGN=LEFT>au_fname</TH></TR>
<TR><TD NOWRAP>White</TD><TD NOWRAP>Johnson</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
  
  
</BODY>
  
</HTML>

This is the fourth file of the results set called C:\WEB\SROW4.HTM:

<HTML>
  
<HEAD>
  
<TITLE>Single row SQL Web Assistant</TITLE>
  
<BODY>
  
<H1>One row per page results</H1>
<HR>
  
<PRE>Last updated: Jun 5 1996  3:15PM</PRE>
  
<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>au_lname</TH><TH ALIGN=LEFT>au_fname</TH></TR>
<TR><TD NOWRAP>Green</TD><TD NOWRAP>Marjorie</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
  
  
</BODY>
  
</HTML>

D. Execute Multiple Queries Using Data Insert Markers and a Template

This example creates two HTML documents from multiple queries by using a template that places each book title and price in separate HTML files.

This is the template file:

<HTML>
  
<HEAD>
  
<TITLE>SQL Server Multiple Queries, Data Insert Markers, & Template Web Sample</TITLE>
  
<BODY>
  
<H1>Books For Sale</H1>
<HR>
  
  
<P>
<TABLE BORDER>
<TR> <TH><I>Title</I></TH> <TH><B>Price</B></TH> </TR>
<%begindetail%>
<TR> <TD><I> <%insert_data_here%> </I> </TD>
    <TD ALIGN=RIGHT><B> $<%insert_data_here%></B></TD> </TR>
<%enddetail%>
</TABLE>
<P>
  
<HR>
  
  
<A HREF = "http://www.w3.org.pub/WWW/">The World Wide Web Consortium</A><P>
<A HREF = "http://www.w3.org/hypertext/WWW/MarkUp/html3/contents.html">HTML 3 specs</A><P>
<A HREF = "http://www.microsoft.com">MICROSOFT</A><P>
  
</BODY>
  
</HTML>

This is the query:

USE pubs
go
EXECUTE sp_makewebtask @outputfile = 'C:\WEB\DATAINS.HTM',
@query = 'SELECT title, price FROM titles', 
@templatefile = 'C:\WEB\DATAINS.TPL', @dbname = 'PUBS', 
@rowcnt = 2, @whentype = 9, @singlerow = 1
go

This is the first file of the results set called C:\WEB\DATAINS1.HTM:

<HTML>
  
<HEAD>
  
<TITLE>SQL Server Multiple Queries, Data Insert Markers, & Template Web Sample</TITLE>
  
<BODY>
  
<H1>Books For Sale</H1>
<HR>
  
  
<P>
<TABLE BORDER>
<TR> <TH><I>Title</I></TH> <TH><B>Price</B></TH> </TR>
  
<TR> <TD><I> The Busy Executive's Database Guide </I> </TD>
    <TD ALIGN=RIGHT><B> $19.99</B></TD> </TR>
  
</TABLE>
<P>
  
<HR>
  
  
<A HREF = "http://www.w3.org.pub/WWW/">The World Wide Web Consortium</A><P>
<A HREF = "http://www.w3.org/hypertext/WWW/MarkUp/html3/contents.html">HTML 3 specs</A><P>
<A HREF = "http://www.microsoft.com">MICROSOFT</A><P>
  
</BODY>
  
</HTML>

This is the second file of the results set called C:\WEB\DATAINS2.HTM:

<HTML>
  
<HEAD>
  
<TITLE>SQL Server Multiple Queries, Data Insert Markers, & Template Web Sample</TITLE>
  
<BODY>
  
<H1>Books For Sale</H1>
<HR>
  
  
<P>
<TABLE BORDER>
<TR> <TH><I>Title</I></TH> <TH><B>Price</B></TH> </TR>
  
<TR> <TD><I> Cooking with Computers: Surreptitious Balance Sheets </I> </TD>
    <TD ALIGN=RIGHT><B> $11.95</B></TD> </TR>
  
</TABLE>
<P>
  
<HR>
  
  
<A HREF = "http://www.w3.org.pub/WWW/">The World Wide Web Consortium</A><P>
<A HREF = "http://www.w3.org/hypertext/WWW/MarkUp/html3/contents.html">HTML 3 specs</A><P>
<A HREF = "http://www.microsoft.com">MICROSOFT</A><P>
  
</BODY>
  
</HTML>

E. Execute Query Using @blobfmt

This example executes a single query and places the information into HTML documents. The publishers table is linked with the pub_info table to provide company logos in the HTML documents.

This is the template file called C:\WEB\BLOBSMP.TPL:

<HTML>
  
<HEAD>
  
<TITLE>Publishers PR Info</TITLE>
  
</HEAD>
  
  
<BODY>
  
<HR>
  
<PRE>
  
<%insert_data_here%>
  
  
</PRE>
  
  
</BODY>
  
</HTML>

This is the query:

USE pubs
go
EXECUTE sp_makewebtask @outputfile = 'C:\WEB\BLOBSMP.HTM',     
@query = 'SELECT pr_info, pub_name, city, state, country, logo, 
pub_info.pub_id FROM pub_info, publishers 
WHERE pub_info.pub_id = publishers.pub_id', 
@webpagetitle = 'Publishers Home Page', 
@resultstitle = 'Premier Publishers and Their Home Page Links', 
@whentype = 9, @blobfmt='%1% FILE=C:\WEB\BLOBSMP.HTM 
TPLT=C:\WEB\BLOBSMP.TPL %6% FILE=C:\WEB\PUBLOGO.BMP', @rowcnt = 2
go

This is the main HTML document BLOBSMP.HTM, which contains hyperlinks to the logo bitmaps and to the publisher's home web pages.

<HTML>
  
<HEAD>
  
<TITLE>Publishers Home Page</TITLE>
  
<BODY>
  
<H1>Premier Publishers and Their Home Page Links</H1>
<HR>
  
<PRE><TT>Last updated: Jun 28 1996  3:15PM</TT></PRE>
  
<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>pr_info</TH><TH ALIGN=LEFT>city</TH><TH ALIGN=LEFT>state</TH><TH ALIGN=LEFT>country</TH><TH ALIGN=LEFT>logo</TH></TR>
<TR><TD NOWRAP><A HREF = "file:///c:\web\blobfmt1.htm">New Moon Books</A></TD><TD NOWRAP><TT>Boston</TT></TD><TD NOWRAP><TT>MA</TT></TD><TD NOWRAP><TT>USA</TT></TD><TD NOWRAP><A HREF = "file:///c:\web\publogo1.bmp">0736</A></TD></TR>
<TR><TD NOWRAP><A HREF = "file:///c:\web\blobfmt2.htm">Binnet & Hardley</A></TD><TD NOWRAP><TT>Washington</TT></TD><TD NOWRAP><TT>DC</TT></TD><TD NOWRAP><TT>USA</TT></TD><TD NOWRAP><A HREF = "file:///c:\web\publogo2.bmp">0877</A></TD></TR>
</TABLE>
<HR>
  
  
</BODY>
  
</HTML>

This is the first file of the results set called C:\WEB\BLOBSMP1.HTM:

<HTML>

<HEAD>

<TITLE>Publishers PR Info</TITLE>

</HEAD>

<BODY>

<HR>

<PRE>

This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Books is located in Boston, Massachusettes.

This is sample text data for New Moon Books, publisher 0736 in the pubs database. New Moon Boo

</PRE>

</BODY>

</HTML>

This is the second file of the results set called C:\WEB\BLOBSMP2.HTM:

<HTML>
  
<HEAD>
  
<TITLE>Publishers PR Info</TITLE>
  
</HEAD>
  
  
<BODY>
  
<HR>
  
<PRE>
  
This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washington, D.C.
  
This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washington, D.C.
  
This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washington, D.C.
  
This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washington, D.C.
  
This is sample text data for Binnet & Hardley, publisher 0877 in the pubs database. Binnet & Hardley is located in Washington, D.C.
  
  
  
  
</PRE>
  
  
</BODY>
  
</HTML>

Permission

The user must have SELECT permissions to run the specified query and CREATE PROCEDURE permissions in the database in which the query will run. The SQL Server account must have privileges to write the generated HTML document to the specified location. Only the system administrator can impersonate other users.

@bold	@singlerow
@fixedfont	@tabborder
@HTMLHeader	@table_urls
@italic	@URL
@lastupdated	@url_query
@reftext	@webpagetitle
@resultstitle