Publish Dynamic Data with SQL Server's Web Assistant Wizard

As the Web and its development mature, more systems and applications release their own Web interfaces. If, for instance, you buy a wireless access point, it can be configured through a Web page. Or, consider Internet Information Server, which has its own local Website next to the standard Windows Console. In a broader sense, you will most likely have accessed your mail or news server through an online interface.

Database applications commonly use Web interfaces. MySQL, for example, can attribute part of its success to convenient online database administration tool, PHPMyAdmin. Its big brother, SQL Server, lacks such a useful interface, though it does offer the frequently overlooked Web Assistant Wizard.

Microsoft SQL Server's Web Assistant Wizard generates HTML on the fly from database data. Its major benefit is that you don't have to program anything. In order to show your dynamic data in an HTML page, you would typically need to write ASP, PHP or other server side code to create objects and connect to the database. After that, you’d need to open a recordset and loop through it. This is all taken care of by SQL Server itself: the data is forwarded to the Web server the moment it is updated in the database.

Another key benefit is performance. Instead of connecting to the database and creating the proper objects on every page request, the Wizard takes initiative on every change. Whenever relevant data is updated, SQL Server will ensure the underlying Web page is brought up to date as well. This means that the visitors to your site will be served much faster: there's only a static HTML page for the server to handle.

How Does It Work?

Step 1: Prerequisites

To use the Web Assistant Wizard, you obviously need to equip yourself with SQL Server. You don't need to be an experienced SQL Server user to understand the Web Assistant Wizard, but if you're a beginner, you might find Mitchell Harper's article, 'Your First SQL Server 2000 Database', interesting. The examples I use here are based on SQL Server 2000, but the Wizard exists in earlier versions of SQL Server as well. Note that the SQL Server Agent should be running.

In addition to that, it's convenient to have direct access to your Web server's virtual directory. SQL Server can write its HTML output to a normal folder, but in that case you’d have to automate the file transfer to your Web server yourself.

Step 2: Get Started

Open your SQL Server Enterprise Manager and choose Wizards under Tools. This will provide you with an overview of available Wizards. You will find the Web Assistant Wizard when you open the Management tree view item.