Using Sql Server 7 Web Assistant to Improve performance of Asp PagesBy 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
database and the
jobs table for our example. Imagine that we are displaying
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
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):
The HTML code between
<%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
jobs table from the drop down. And select
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
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...