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.

Step 3: Choose the Data Source from which you want to Publish Online

For sake of this example, we’ll use the Northwind database, which is created by default when installing SQL Server. After choosing the database mentioned above, you will notice the Wizard has three different ways of selecting data:

• Data from tables
• Results of stored procedures
• Data from a T-SQL statement

If you know how to write your own stored procedures and T-SQL statements (SQL Server’s own dialect of the ordinary Structured Query Language), these can offer you great powers. Here, however, we’ll continue with the much simpler first option.

Step 4: Choose the Data you want to Publish Online

After picking that first and most straightforward option, you will get the opportunity to choose the appropriate tables and columns. Choose Employees, add the columns with first and last name, and click Next twice.

Step 5: Schedule the Update

Usually, you’d like to update your pages when SQL Server data changes, so pick the fourth option for this example. As you can see, it’s also possible to schedule updates at regular intervals. Make sure you leave the option “Generate a Web page when the wizard is completed” on, so you can watch the results once we’ve finished.

Step 6: Indicate which Changes to Monitor

Last but not least, SQL Server needs to know which changes the HTML page should implement. Data modifications to any column will lead to the generation of a new Web page. Add the first and last name columns of the table Employees again, and click Next.

Step 7: Choose the Location of your Web Page

Pick a new name for the HTML page that SQL Server should generate. As previously mentioned, ideally you should refer to a directory on your Web server here, to ensure you have online access to the published data.

As you can see, it’s possible to influence the layout of the HTML page generated by SQL Server. We will just use the default options as we approach the closing stages. Click Next until you reach Finish, and SQL Server will confirm that it has successfully completed the task.

Step 8: Verify that your File was Created

Use your browser to reach the file you created in the previous step.

Step 9: Make Changes and Check the Update

Notice that the first employee in your Web page is Nancy Davolio.

Now, revert to the Enterprise Manager and view the data of the Employees table in your Northwind database. Change the LastName value of Davolio to, say, Johnson.

Refresh your Web page, and you’ll see that Nancy is now called Johnson online as well.

Advanced Options

To keep the example simple, I’ve ignored a lot of the Web Assistant’s extra options. I won’t go into detail here, but the Wizard contains many possibilities for beginners and advanced users alike. Once you’ve become familiar and comfortable with the Web Assistant Wizard as well as with SQL Server, you might consider experimenting with the following, more advanced applications:

• Tweaking the layout of the HTML output
• The use of templates
• The use of your own T-SQL queries and stored procedures
• Updates at regular intervals, for instance once a day
• The possibility of editing all your options under Management->SQL Server Agent->Jobs
• The possibility of publishing Database Maintenance information online
• The use of the predefined ‘sp_make_webtask‘ stored procedure in T-SQL queries

Each of these options deserves its own treatment in an article. Some have already appeared on SitePoint in one form or another — in David Clark’s ‘Stored Procedures on PHP and Microsoft SQL Server‘, for instance.

Conclusion

The Web Assistant Wizard is a great tool to help you quickly and easily publish database data. It enables you to view and share up-to-date data in any shape or form you choose, yet not to change or delete it. That restriction is the largest downside of the Wizard’s possibilities for me.

The performance win is clearly a big bonus. For larger Web development teams, the Web Assistant Wizard could also allow the sharing of responsibilities. The database manager will be responsible for the generation of data, while the Web developer can focus on building Web pages, i.e. the template in this case.

The Web Assistant is a powerful tool. Nonetheless, the Wizard and its benefits are little known among Web developers who work with SQL Server. Now that you have an idea of how to use it, consider what it can do to improve your online applications.

Frequently Asked Questions (FAQs) about Web Assistant Wizard and Servers