There is also a semi-dynamic technique that you can use to display data from a SQL Server database as pure HTML. The utilities that are provided with SQL Server (in the Client Utilities installation) include SQL Server Web Assistant. This is a Wizard that can automatically create static HTML pages containing data drawn from your SQL Server.
The pages are built by stored procedures that the Wizard creates. The clever part is that the SQL Server Executive can then automatically re-execute the stored procedures at specified intervals, rebuilding the page to contain the most recent data. This is a great approach where you have data that changes relatively infrequently, or where the user doesn’t need to see the actual current content of the database. For example, if you wanted to display yesterday's breakdown of sales as a page on your Intranet, you could instruct the Wizard to build the page every morning from the previous day's data.
The big advantage is that the Web server itself can deliver an HTML file much more quickly and efficiently than a dynamic page created with ASP; and with less use of server resources. It doesn’t require an instance of the ASP interpreter, a database connection, or the services of the database system.
The Wizard is easy to use, and even intelligent in that it allows you to create the query that extracts the data for the page from a graphical view of the databases on the server:
The main problem is that the result is hardly attractive, using a simple table with visible borders. You have no control over the column headings, and little other real control over the remainder of the page:
However, you can use a template file that contains custom page headings and a footer, and have the Web Assistant insert the table into it where you specify. You can also have URLs in the database displayed as hyperlinks:
And, to some extent, you can specify the appearance of the page by specifying font details and other options:
At the end of the day, the Web Assistant may suit your needs—especially if content is more important than style in your particular application. However, we can do a lot better than this using ADO to extract the data and ASP to format it and write it to a text file. After all, HTML pages are simply text files with the
or .htm
file extension. .html
In fact, in Chapter 8, you'll see how we can build a semi-dynamic page using just this technique. We provide a list of links to sites that link to us (normally called referrers). The links are collected automatically and stored in a SQL Server table by IIS. All we have to do is convert the links into a text file, then insert the text file, using SSI, into an HTML page. This gives a result that is, essentially, the same as the Web Assistant provides.