To read the article online, visit

Using Sql Server 7 Web Assistant to Improve performance of Asp Pages

By Venkatraman Ambethkar

The SQL Server 7 Web Assistant feature can be used to generate html pages which can be directly published on the web. There is a wizard to do this task in Sql server 7. The advantages are:

    1. No need to open connection with the database for each and every request.
    2. No need for any Asp code itself.(No ado object creations,looping through the recordset etc).
    3. Automatically updates the content that is displayed when the underlying table's data changes.

Let's take the common problem of generating dynamic combo boxes and use the pubs database and the jobs table for our example. Imagine that we are displaying the job_desc columns in a combo box. The following steps can be taken to generate a snapshot of the data in the pubs table and displaying our combo box.

Step1: Creating the template file
A template file is one which is nothing but a text file where we specify the format the SQL Server Web Assistant should follow while generating the HTML file. Open your text editor and type in the following few lines (or just copy and paste this):

<select name="cbo1">
    <option value="<%insert_data_here%>"><%insert_data_here%>

The HTML code between <%begindetail%> and <%enddetail%> are the markers. The portion within these markers are repeated for each row of the recordset in the output file generated by the web assistant. The first <%insert_data_here%> marks where the first field of the recordset will be inserted and so on.

Save the file as template.tpl in your virtual directory. Not necessarily in virtual directory. It can be in any directory accessible to SQL server.

Step2. Creating the Job using the Sql Server 7 Web Assistant Wizard
Open your SQL Server 7 Enterprise Manager, expanding your server and locating "Web Publishing" under Management. Right click on that you should get, New Web Assistant Job.... Select it, now the wizard walks you through the rest of the process. The following steps will serve as a guide... (View a screenshot of the SQL Server 7 Web Assistant Wizard.)

    1. Click Next on the opening window.

    2. Select the database which you want to use. Select pubs for our example.(click next)

    3. Give a name for the job. (This is the name of the task through which the sql server is going to manage the whole process). And by default the "Data from the tables and columns that I select" has been selected. Leave it as it is. Click next

    4. Select jobs table from the drop down. And select Job_id and Job_desc from the left hand list. We are going to use job_id in the value of the option element which you can find out from the Template file which we have created earlier. Click next.

    5. "All of the rows" option has been selected by default. Leave it as it is. Click next.

    6. In the "Schedule the Web Assistant Job" select "When the SQL server data changes". This also selects an option in the bottom as "Generate a web page when the wizard is completed". Leave it as it is. Click next.

    7. In the "Monitor a Table and Columns". Select the jobs table and add Job_id and Job_desc which we are displaying the final drop down box. This makes the SQL Server to create insert, update and delete triggers on the Jobs table which will fire the process of generating the HTML file only when the values in these fields changes. If unencrypted triggers are already existing on the jobs table, these triggers will get appended at the the end. If there is an encrypted trigger, the wizard will fail. Click next.

    8. In the "Publish the web page" window, browse to the directory which has been configured as virtual directory. And type in a file name in which the output will be written by SQL Server. Let's name it output.html. Click next.

    9. In the "Format the Web Page" window, check "No,use the template file from" option and Browse to give the path where we have saved our template.tpl. Leave the "Use character set" drop down as Unicode(UTL-8). Click next.

    10. In the "Limit Rows" window, leave the existing default options "No, return all rows of data" and "No,put all data in one scrolling page" as they are. Click next.

    11. In this final window, click "Write Transact Sql to file..." to save the stored procedure which the Web Assistant has generated based on the inputs given by you in the previous windows. Running this query against the database creates the job in one go. This is useful for creating the job from an application like ASP.

    12. Click Finish. You should get the message box saying, the job is created successfully.

In Part 2 we'll continue our discussion on using the SQL Server 7 Web Assistant...

  • Read Part 2!

  • Article Information
    Article Title: Using Sql Server 7 Web Assistant to Improve performance of ASP Pages
    Article Author: Venkatraman Ambethkar
    Published Date: Thursday, June 22, 2000
    Article URL:

    Copyright 2019 QuinStreet Inc. All Rights Reserved.
    Legal Notices, Licensing, Permissions, Privacy Policy.
    Advertise | Newsletters | E-mail Offers