After several close calls and a couple of restores, those of us responsible for the integrity of the data need a safe method to give a users the information they need. Microsoft SQL Server has given us a great tool called "sp_makewebtask, " whose idea is to easily produce professional reports for the users in HTML format. The HTML document can easily be read using their web browser and these can be made to run weekly, daily, once an eon, or whatever. The user doesn’t touch the database but gets the information they need. You can email the file, drop it in the users directory, in a directory on your Moon based public shared server, and perhaps even fold, spindle or mutilate them. The best part is that sp_makewebtask does all the tedious coding without having to code a zillion lines of code.
The following sample in meant to be run using the "Northwind" database. It is intentionally garish. If it looks cool, you might be colorblind so have an associate check your final reports. This was done to facilitate locating code changes or deletion areas to produce a more attractive report.
The template.tpl is where the headers, font, color and real magic occurs. The template is great because you can add a caveat paragraph qualifying the data retrieved to a specific time or telling the user not to use the data incorrectly thus avoiding the dreaded "I was told we had these Rolls Royce Phantoms in inventory and priced at $1,000 each, and it’s our database gurus who told me the misleading info."
The sp_makewebtask part is the easiest part of the process . . . almost. In our example I am only using three elements of the sp_makewebtask : the output file, the output template and the output query. As you can imagine there are a slew of parameters for sp_makewebtask, but the goal here is to get your feet wet using the basics. You can go bananas later.
To run this place the files output.tpl and output.sql in your "development" SQL Server c: drive in a folder called "output", preferably not on your production server, unless it’s the only one you have and you have permission. If placed elsewhere you will have to make sure the paths are changed to match the new location.
The output.tpl (Template file) below is a very basic html page. I will highlight some of the HTML basics. The tags (things in brackets <>) like HTML, HEAD, TITLE, BODY are the basic building blocks for a HTML page. Most tags require ending tags like </something>. Don’t forget them.
<H1>, <H2>, <H3>, etc. are headline tags. The <BR> tag is just a line break , line spacing in your code is ignored in HTML. The <HR> is a hard rule line. The page is centered with the <CENTER>. The tags <B> and <I> are bold face and italic. So far so good.
The tables in HTML are constructed with TABLE, TR, TH or TD tags. <TABLE> starts the table and has various parameters like bgcolor, background, width, cellpadding and cellspacing. The TR tag is just a row tag. If we eliminated the Middle </TR> and <TR> tags in the first table. The table would change from 3 rows into 3 columns. The <TD> is the basic column tag. The Title tag <TH> automatically makes the entry bold face and centered. You can change color, width, alignment of these tags also.
The outputs from the queries (there are 2) are placed in the <%insert_data_here%> sections between the <%begindetail%> and <%enddetail%> tags. The first <%insert_data_here%> inserts the Converted Getdate() result into the table. The second <%insert_data_here%> inserts the results of the query "SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder, UnitPrice FROM Northwind.dbo.products" line by line and column by column into the HTML code for you using the template as a guide. No coding required. Very neat!
After you run and look at the result, save the original template file with a new name. Modify the template file to see how the color, size, font, spacing, alignment, flavoring, and padding affects the result. Try variations, the original will still be there in case you mess up the template.
The output template file : output.tpl is below:
<HTML>
<HEAD>
<TITLE>>PRODUCT WEB SAMPLE HTML></TITLE>
<BODY BGCOLOR="TAN">
<CENTER>
<H2><FONT COLOR="BLUE">NORTHWIND PRODUCT INFO</FONT></H2>
<TABLE BORDER="2" BGCOLOR="WHITE" CELLPADDING="2" CELLSPACING="6">
<TR><TD>This data is valid for 2 hours from the date and time displayed.</TD></TR>
<%begindetail%>
<TR>
<TD ALIGN="CENTER" BGCOLOR="BLACK"><FONT COLOR="WHITE">
<%insert_data_here%>
</FONT></TD>
</TR>
<%enddetail%>
<TR>
<TD>Any user found using data after the 2 hour period will be blebbed.</TD>
</TR> </TABLE>
<HR>
<P>
<TABLE BORDER="2" BORDERCOLOR="BROWN" BGCOLOR="YELLOW" CELLPADDING="4" CELLSPACING="4"> <TR>
<TH VALIGN="MIDDLE">PRODUCT<BR>ID</TH>
<TH VALIGN="MIDDLE">PRODUCT<BR>NAME</TH>
<TH VALIGN="MIDDLE">UNITS<BR>IN<BR>STOCK</TH>
<TH VALIGN="MIDDLE">UNITS<BR>ON<BR>ORDER</TH>
<TH VALIGN="MIDDLE">UNIT<BR>PRICE</TH>
</TR>
<%begindetail%>
<TR>
<TD BGCOLOR="GREEN" ALIGN="CENTER">
<FONT COLOR="WHITE"><B><%insert_data_here%></B></FONT></TD>
<TD BGCOLOR="WHITE">
<FONT COLOR="RED"><I><%insert_data_here%></I></FONT></TD>
<TD BGCOLOR="ORANGE" ALIGN="RIGHT">
<FONT COLOR="BLACK"><%insert_data_here%></FONT></TD>
<TD BGCOLOR="WHITE" ALIGN="RIGHT">
<FONT COLOR="BLUE"><B><%insert_data_here%></B></FONT></TD>
<TD BGCOLOR="PURPLE" ALIGN="RIGHT">
<FONT COLOR="WHITE"><I><%insert_data_here%></I></FONT></TD>
</TR>
<%enddetail%>
</TABLE>
<HR>
</CENTER>
</BODY>
</HTML>
Run the output.sql either from the query analyzer. A quick explanation of the output.sql is: the stored procedure sp_makewebtask is executed (EXEC sp_makewebtask). It has the following parameters @outputfile which is: c:\output\output.html - which is what you want to call and where you want to put the output HTML file. In this example there are two queries (@query): the Getdate and the Select from Products from the Northwind database. Note they are written ‘query1 query2’ , single quotes around both NOT ‘query1’ ‘query2’. You can have one, two or more queries. You can run a full query, followed by a stored procedure, followed by another query. Last the variable name @templatefile=’c:\output.tpl’ so that sp_makewebtask can find the template file.
The output sql file : output.sql is below:
EXEC sp_makewebtask
'c:\output\output.html',
'SELECT CONVERT(varchar,GETDATE())
SELECT ProductID,ProductName,UnitsInStock,UnitsOnOrder,UnitPrice
FROM Northwind.dbo.products',
@templatefile = 'c:\output\output.tpl'
Your real report can be build as a stored procedure, run as a job, use osql with a batch file. A sample report file is here.
Conclusion
The idea of this introduction is to produce professionally reported data with minimal effort that is not connected directly to the database. These can be automatically routed to your Intranet, Extranet or even Internet for quick accurate access to dynamically changing data to match your users time frames.
Change the color, size, fonts, and so forth. Pretty soon you will be adding photos, textured background, animations and who knows, perhaps eventually an Oscar with your name awaits. Enjoy.
Additional resources:
If you use "SQL Books On Line" the sp_makewebtask will show you all the parameters you can play with using sp_makewebtask.
HTML colors - type HTML colors in Google and you will get the "Web Safe" colors about 216, and the hex codes for RGB colors which can be used.
WEBMASTER IN A NUTSHELL O'Reilly books
