sp_makewebtask

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

Note  All Web jobs are categorized as Web Assistant in the Job Categories dialog box in SQL Server Enterprise Manager. For more information, see Defining Jobs.

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]
    [, [@nrowsperpage =] n]
    [, [@datachg =] table_column_list]
    [, [@charset =] characterset]
    [, [@codepage =] codepage]

Arguments

[@outputfile =] 'outputfile'

Is the location of the generated HTML file on an instance of Microsoft® SQL Server™. It can be a UNC name if the file is to be created on a remote computer. outputfile is nvarchar(255), with no default.

[@query =] 'query'

Is the query to be run. query is ntext, with no default. 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 result in multiple tables being displayed in outputfile.

[@fixedfont =] fixedfont

Specifies that the query results be displayed in a fixed font (1) or a proportional font (0). fixedfont is tinyint, with a default of 1.

[@bold =] bold

Specifies that the query results be displayed in a bold font (1) or nonbold font (0). bold is tinyint, with a default of 0.

[@italic =] italic

Specifies that the query results be displayed in an italic font (1) or nonitalic font (0). italic is tinyint, with a default of 0.

[@colheaders =] colheaders

Specifies that the query results be displayed with column headers (1) or no column headers (0). colheaders is tinyint, with a default of 1.

[@lastupdated =] lastupdated

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

[@HTMLheader =] HTMLheader

Specifies the HTML formatting code for displaying the text contained in resultstitle. HTMLheader is tinyint, and can be one of these values.

[@username =] username

Is the username for executing the query. username is nvarchar(128), with a default of the current user. The system administrator or database owner can specify another username.

[@dbname =] dbname

Is the database name to run the query on. dbname is nvarchar(128), with a default of the current database.

[@templatefile =] 'templatefile'

Is the path of the template file used to generate the HTML document. The template file contains information on the formatting characteristics of HTML documents and contains the tag <%insert_data_here%>, which indicates the position to which the query results will be added in an HTML table. templatefile is nvarchar(255).

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

• Specify a general table format by including the <%insert_data_here%> marker, which indicates the position to add the query results in an HTML table. There are no spaces in the tag. When <%insert_data_here%> is used, the fixedfont, bold, italic, colheaders, and tabborders values are applied to the query results.
  
  
• Specify a complete row format to produce a more precise layout. Use the <%begindetail%> and <%enddetail%> markers and define a complete row format between them with <TR>, </TR>, <TD>, and </TD> HTML tags. For each column to be displayed in the result set, insert the <%insert_data_here%> marker. When the complete row format is used, these sp_makewebtask parameters are ignored:

  Bold	lastupdated	table_urls
  Colheaders	reftext	URL
  Fixedfont	resultstitle	url_query
  HTMLheader	singlerow	webpagetitle
  Italic	tabborder

  
  

  The extended procedure that is called by sp_makewebtask can read both Unicode and non-Unicode template files. If a Unicode file contains a signature header, the header is removed when the HTML file is generated.

[@webpagetitle =] 'webpagetitle'

Is the title of the HTML document. webpagetitle is nvarchar(255), with a default of SQL Server Web Assistant. For a blank title, specify two space characters for the title, or edit the HTML source to remove the <TITLE> and </TITLE> tags and the text of the title between the tags.

[@resultstitle =] 'resultstitle'

Is the title displayed above the query results in the HTML document. resultstitle is nvarchar(255), with a default of Query Results.

[@URL =] 'URL'

Is a hyperlink to another HTML document. The hyperlink is placed after the query results and at the end of the HTML document. URL is nvarchar(255). If URL is specified, reftext must also be specified, and table_urls and url_query cannot be specified.

[@reftext =] 'reftext'

Is the hyperlink that describes to which HTML document the hyperlink should take the user. reftext is nvarchar(255). The hyperlink text describes the destination and the hyperlink address comes from URL.

[@table_urls =] table_urls

Is whether hyperlinks are included on the HTML document and come from a SELECT statement executed on SQL Server. table_urls is tinyint, with a default of 0, which indicates that no query will generate hyperlinks. A value of 1 indicates that a list of hyperlinks will be created by using url_query.

Important  If table_urls is 1, url_query must be included to specify the query to be executed for retrieving hyperlink information, and URL and reftext cannot be specified.

[@url_query =] 'url_query'

Is the SELECT statement to create the URL and its hyperlink text. URLs and hyperlink text come from a SQL Server table. With this parameter, multiple URLs with associated hyperlinks can be generated. Use url_query with table_urls. url_query is nvarchar(255). url_query must return a result set containing two columns: the first column is the address of a hyperlink; 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 =] whentype

Specifies when to run the task that creates the HTML document. whentype is tinyint, and can have these values.

Important  SQL Server Agent must be running when a job is scheduled to run periodically; otherwise, the HTML page is not generated.

[@targetdate =] targetdate

Specifies the date the page should be built. The format is YYYYMMDD. When targetdate is omitted, the current date is used. If whentype is 2 (later), 3 (dayofweek), 4 (periodic), or 6 (now and later), targetdate is required. targetdate is int, with a default of 0.

[@targettime =] targettime

Specifies the time the HTML document should be created. targettime is int, with a default of 12:00 A.M. The format is HHMMSS.

[@dayflags =] dayflags

Specifies the day of the week to update the HTML document. dayflags is required when whentype is 3 (dayofweek) or 7 (now and dayofweek). dayflags is tinyint, and can be one of these values.

To specify multiple days, add the values. For example, to specify Monday and Thursday, set dayflags to 18.

[@numunits =] numunits

Specifies how often to update the HTML document. numunits is used only when whentype is 4 (periodic) or 8 (now and periodically thereafter). For example, if whentype is 4, numunits is 6, and unittype is 1 (hours), the specified HTML document is updated every six hours. numunits is tinyint, with a default of 1. Values can range from 1 through 255.

[@unittype =] unittype

Specifies how often the HTML document should be updated when numunits is 4 (periodic) or whentype is 8 (now and later). unittype is tinyint, and can be one of these values.

[@procname =] procname

Is the procedure or task name for the HTML document. If procname is not specified, the procedure name generated by sp_makewebtask is in the form of Web_YYMMDDHHMMSS<spid>. If procname is user-specified, the procedure name must meet the conditions for valid procedure names, and must be unique. procname is nvarchar(128). If procname is longer, it is truncated.

[@maketask =] maketask

Specifies whether a task should be created to execute an internal stored procedure that generates an HTML document. maketask is int, and can be one of these values.

[@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.

[@tabborder =] tabborder

Specifies whether a border should be drawn around the results table. tabborder is tinyint. If tabborder is 1 (the default), a border is drawn. If 0, no border is drawn.

[@singlerow =] singlerow

Specifies whether the results are to be displayed as one row per page. singlerow is tinyint. If singlerow is 0 (the default), all results appear on the same page and in the same table. If singlerow is 1, a new HTML page is generated for every qualifying row in the result 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 file name, pages are called Web1.html, Web2.html, and so on.

[@blobfmt =] blobfmt

Specifies whether all columns of ntext or image data types should be embedded in the same results page (NULL, the default), or whether these columns should be saved in another page and linked to the main HTML document by a URL. blobfmt is ntext or image.

To place the ntext or image data in a separate HTML page, use this format to specify a value for blobfmt:

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

where

n

Is the column number in the result list corresponding to a text field, and n+1 is the URL hyperlink text to the separate ntext or image HTML file.

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

Output file names end with a number that indicates successive rows, similar to singlerow. output_filename is required, but template_filename and url_link_name are optional. The FILE = output_filename is the full path to the output file location. If provided, url_link_name is the http:// link to the file that is accessible through the World Wide Web. If url_link_name is not provided, the full physical file name preceded by the file:/// tag is used as the url_link_name. The same syntax in blobfmt (%n% FILE=...) can be repeated for multiple text or image columns.

If template_filename is provided, use the <%insert_data_here%> method to indicate the data insertion point.

The URL text is part of the result set and is always the column after the original ntext or image column. This URL text column is not displayed as a separate column in the result set.

[@nrowsperpage =] n

Specifies that the result set should be displayed in multiple pages of n rows in each page, and the successive pages are linked with NEXT and PREVIOUS URLs. n is int, with a default of 0, which means all results are to be displayed in a single page. If singlerow is specified, this parameter cannot be used.

[@datachg =] table_column_list

Is the list of table and optional column names that triggers the new page creation when the data changes. table_columns_list is ntext. The format of this value is:

{TABLE=name[COLUMN=name]}[,...]

This parameter is required when whentype is 10. With this option, three triggers (UPDATE, INSERT, and DELETE) are created on the specified table and columns, executing the Web task when these triggers are fired. If there are preexisting triggers on the table, sp_runwebtask is added to the end of the trigger, provided that the trigger is not created with WITH ENCRYPTION, and the COLUMN field specification in this parameter is ignored. If there is an existing trigger on the table created with the WITH ENCRYPTION option, sp_makewebtask fails.

[@charset =] characterset

Is a character set alias code that is recognized by Microsoft Internet Explorer or compatible browsers. characterset is nvarchar(25) with a default value of N'utf-8'. characterset is used to specify a value for the META element charset tag in the .htm file.

[@codepage =] codepage

Is a numeric value corresponding to the character set. For example, code page 65001 corresponds to character set UTF-8. codepage is int with a default of 65001. For a complete list of supported code pages, use sp_enumcodepages.

Return Code Values

0 (success) or nonzero (failure)

Important  The return code values have changed from earlier versions of Microsoft SQL Server.

Result Sets

None

Remarks

For scheduled tasks, all errors are reported in the Microsoft Windows NT® application log on an instance of SQL Server, and have a source of xpsqlweb.

Important  The sp_dropwebtask, sp_makewebtask, and sp_runwebtask stored procedures can be run only on SQL Server versions 6.5 and later.

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 Using the Web Assistant Wizard.

Fonts available for HTML documents depend upon the capabilities of your Web browser software. For more information about font availability and HTML formatting, see the browser software documentation.

Important  All Microsoft Windows® 95 and Microsoft Windows 98 Web Assistant users must have user accounts in the database being used. Use sp_adduser to add accounts to each database a user may access. When running the Windows 95 or Windows 98 operating system, an on-demand task can only be run by the task owner or the system administrator.

Permissions

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 permissions to write the generated HTML document to the specified location. Only the members of the sysadmin fixed server role can impersonate other users.

Examples

A. Create multiple queries by using a template file

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

This section shows the template file named C:\Web\Multiple.tpl.

Note  For this example to work properly, the template file code presented here must be saved in a file named C:\Web\Multiple.tpl. You must also create the C:\Web directory before saving the template in the C:\Web directory.

<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>Publisher'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.microsoft.com">Microsoft</A><P>
<A HREF = "http://msdn.microsoft.com">MSDN</A><P>
  
  
</BODY>
  
</HTML>

This section of the example shows using sp_makewebtask to execute 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

Here is the result 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.9900</B></TD> </TR>

<TR> <TD><I> Cooking with Computers: Surreptitious Balance Sheets </I> </TD>
    <TD ALIGN=RIGHT><B> $11.9500</B></TD> </TR>

<TR> <TD><I> You Can Combat Computer Stress! </I> </TD>
    <TD ALIGN=RIGHT><B> $2.9900</B></TD> </TR>

<TR> <TD><I> Straight Talk About Computers </I> </TD>
    <TD ALIGN=RIGHT><B> $19.9900</B></TD> </TR>

<TR> <TD><I> Silicon Valley Gastronomic Treats </I> </TD>
    <TD ALIGN=RIGHT><B> $19.9900</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><TT>Bennet</TT></TD><TD><TT>Abraham</TT></TD></TR>
<TR><TD><TT>Blotchet-Halls</TT></TD><TD><TT>Reginald</TT></TD></TR>
<TR><TD><TT>Carson</TT></TD><TD><TT>Cheryl</TT></TD></TR>
<TR><TD><TT>DeFrance</TT></TD><TD><TT>Michel</TT></TD></TR>
<TR><TD><TT>del Castillo</TT></TD><TD><TT>Innes</TT></TD></TR>
</TABLE>
<HR>

  
  
<P>
  
<TABLE BORDER>
<TR> <TH ALIGN=CENTER>ID</TH> <TH ALIGN=LEFT><I>Publisher'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><TT>Bennet</TT></TD><TD><TT>Abraham</TT></TD></TR>
<TR><TD><TT>Blotchet-Halls</TT></TD><TD><TT>Reginald</TT></TD></TR>
<TR><TD><TT>Carson</TT></TD><TD><TT>Cheryl</TT></TD></TR>
<TR><TD><TT>DeFrance</TT></TD><TD><TT>Michel</TT></TD></TR>
<TR><TD><TT>del Castillo</TT></TD><TD><TT>Innes</TT></TD></TR>
</TABLE>
<HR>

  
  
<P>
  
  
<A HREF = "http://www.microsoft.com">Microsoft</A><P>
<A HREF = "http://msdn.microsoft.com">MSDN</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.

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.microsoft.com', 'Microsoft Home Page')
INSERT my_favorite_web_sites(url_def) VALUES ('http://www.widgets.microsoft.com')
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

Here is the result set:

<HTML>

<HEAD>

<TITLE>Microsoft SQL Server Web Assistant</TITLE>

</HEAD>

<BODY>

<H1>Query Results</H1>
<HR>

<P>
<P><TABLE BORDER=0>
<TR><TD><TT>But Is It User Friendly?</TT></TD><TD><TT>22.9500</TT></TD></TR>
<TR><TD><TT>Computer Phobic AND Non-Phobic Individuals: Behavior Variations</TT></TD><TD><TT>21.5900</TT></TD></TR>
<TR><TD><TT>Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean</TT></TD><TD><TT>20.9500</TT></TD></TR>
<TR><TD><TT>Secrets of Silicon Valley</TT></TD><TD><TT>20.0000</TT></TD></TR>
<TR><TD><TT>The Busy Executive's Database Guide</TT></TD><TD><TT>19.9900</TT></TD></TR>
<TR><TD><TT>Straight Talk About Computers</TT></TD><TD><TT>19.9900</TT></TD></TR>
<TR><TD><TT>Silicon Valley Gastronomic Treats</TT></TD><TD><TT>19.9900</TT></TD></TR>
<TR><TD><TT>Prolonged Data Deprivation: Four Case Studies</TT></TD><TD><TT>19.9900</TT></TD></TR>
<TR><TD><TT>Sushi, Anyone?</TT></TD><TD><TT>14.9900</TT></TD></TR>
<TR><TD><TT>Cooking with Computers: Surreptitious Balance Sheets</TT></TD><TD><TT>11.9500</TT></TD></TR>
<TR><TD><TT>Fifty Years in Buckingham Palace Kitchens</TT></TD><TD><TT>11.9500</TT></TD></TR>
<TR><TD><TT>Is Anger the Enemy?</TT></TD><TD><TT>10.9500</TT></TD></TR>
<TR><TD><TT>Emotional Security: A New Algorithm</TT></TD><TD><TT>7.9900</TT></TD></TR>
<TR><TD><TT>Life Without Fear</TT></TD><TD><TT>7.0000</TT></TD></TR>
<TR><TD><TT>You Can Combat Computer Stress!</TT></TD><TD><TT>2.9900</TT></TD></TR>
<TR><TD><TT>The Gourmet Microwave</TT></TD><TD><TT>2.9900</TT></TD></TR>
<TR><TD><TT>The Psychology of Computer Cooking</TT></TD><TD>n/a</TD></TR>
<TR><TD><TT>Net Etiquette</TT></TD><TD>n/a</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
<A HREF = "http://www.widgets.microsoft.com">http://www.widgets.microsoft.com</A><P>


</BODY>

</HTML>

C. Execute multiple queries with single-row mode

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

Here 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

Here is the first file of the result set called C:\Web\Srow1.htm:

<HTML>

<HEAD>

<TITLE>Single row SQL Web Assistant</TITLE>

</HEAD>

<BODY>

<H1>One row per page results</H1>
<HR>

<PRE>Last updated: Jun 17 1997  9:14AM</PRE>

<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>title</TH><TH ALIGN=LEFT>price</TH></TR>
<TR><TD>But Is It User Friendly?</TD><TD>22.9500</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
<TABLE BORDER=0 CELLPADDING=6>
<TR><TD>More results can be found in:</TD>
<TD><A HREF = "SROW2.HTM">Next</A></TD>
</TR></TABLE>

</BODY>

</HTML>

Here is the second file of the result set called C:\Web\Srow2.htm:

<HTML>

<HEAD>

<TITLE>Single row SQL Web Assistant</TITLE>

</HEAD>

<BODY>

<H1>One row per page results</H1>
<HR>

<PRE>Last updated: Jun 17 1997  9:14AM</PRE>

<P>
<P><TABLE BORDER=1>
<TR><TH ALIGN=LEFT>title</TH><TH ALIGN=LEFT>price</TH></TR>
<TR><TD>Computer Phobic AND Non-Phobic Individuals: Behavior Variations</TD><TD>21.5900</TD></TR>
</TABLE>
<HR>
<A HREF = "http://www.microsoft.com">Microsoft Home Page</A><P>
<TABLE BORDER=0 CELLPADDING=6>
<TR><TD>More results can be found in:</TD>
<TD><A HREF = "SROW1.HTM">First</A></TD>
<TD><A HREF = "SROW1.HTM">Previous</A></TD>
<TD><A HREF = "SROW3.HTM">Next</A></TD>
</TR></TABLE>

</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 named C:\Web\Datains.tpl:

<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.microsoft.com">Microsoft</A><P>
<A HREF = "http://msdn.microsoft.com">MSDN</A><P>
  
</BODY>
  
</HTML>

Here 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

Here is the first file of the result 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.9900</B></TD> </TR>

</TABLE>
<P>
  
<HR>
  
  
<A HREF = "http://www.microsoft.com">Microsoft</A><P>
<A HREF = "http://msdn.microsoft.com">MSDN</A><P>
  
<TABLE BORDER=0 CELLPADDING=6>
<TR><TD>More results can be found in:</TD>
<TD><A HREF = "DATAINS2.HTM">Next</A></TD>
</TR></TABLE></BODY>
  
</HTML>

Here is the second file of the result 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.9500</B></TD> </TR>

</TABLE>
<P>
  
<HR>
  
  
<A HREF = "http://www.microsoft.com">Microsoft</A><P>
<A HREF = "http://msdn.microsoft.com">MSDN</A><P>
  
<TABLE BORDER=0 CELLPADDING=6>
<TR><TD>More results can be found in:</TD>
<TD><A HREF = "DATAINS1.HTM">First</A></TD>
<TD><A HREF = "DATAINS1.HTM">Previous</A></TD>
</TR></TABLE></BODY>
  
</HTML>

E. Execute query using @blobfmt

This example executes a single query and places the information in 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.GIF', @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.gif">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.gif">0877</A></TD></TR>
</TABLE>
<HR>


</BODY>

</HTML>

Here are the first few lines of the first file of the result set called C:\Web\Blobsmp1.htm:

Note  Not all output lines are shown here. Complete output appears in 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, Massachusetts.

...

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


</PRE>


</BODY>

</HTML>

Here is the second file of the result 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.




</PRE>


</BODY>

</HTML>

See Also

sp_enumcodepages

sp_runwebtask

System Stored Procedures