• Find out how to database-enable your site - You'll see how to use the Internet Database Connector to add dynamic Web pages to your site.

• See how to use the SQL Web Page Wizard - SQL Server's Web Page Wizard helps you create pages that can call stored procedures, run scheduled queries and more.

• Learn key settings to make logging access to databases work for you - Without some key entries in your system configuration, you may have a difficult time getting valuable logging information from your system. Find out the key ingredients for successful access to your database.

Corporate database systems are built on making information widely available to qualified users. The information in these situations comes from many sources, ranging from discussion groups to proprietary systems. Probably the biggest repository of "mass" information is the database. If you've ever just browsed a database without specifying meaningful criteria, you know what I mean by "mass" information. There is often so much information represented as to make the information less useful.

By making databases available on your Intranet, you can help users make sense of this information and, at the same time, you'll be able to provide access, presentation without the need for any special software to begin using the database effectively. In short, you have a new avenue to provide this access to the information that is probably already stored on your network.

Database access with the IIS system is provided by giving you ODBC connectivity to the HTML pages that execute on the server when the user makes a request of the system. In this chapter, you'll see how to set up pages, what types of information you can provide and how you can enhance the presentation of the information to make it the most meaningful to the people that request it.

Be aware that the database connector files are likely to contain and convey sensitive and sometimes very confidential information. For example, they'll contain query information that calls out column names, table names and database sources that map to your ODBC configurations on the server. In addition, when users click on a link to a database connector file, they'll be able to see where you're keeping your scripts and other programs, as it will show up in the URL that is displayed to them. It is extremely important that your programs, scripts and supporting files reside in the scripts subdirectory structure and that you provide Execute only privileges on that directory. Be sure you do not provide Read privileges. This will open your system to unneeded possibilities for trouble as people would be able to browse and review the applications that are the core of your system. For more information, please refer to Chapter 3, "Installing and Understanding Internet Information Server."

Setting up the Internet Database Connector

Fig. 11.1 - The IDC provides for access to any ODBC data source.

When users specify the IDC file in the URL from the browser, they are asking the IIS system to use the IDC file and its statements to query the database and return the results. The IDC is specified in the URL, but the HTX file, or HTML Extension file, is what is actually returned to the user. The HTX file, still a standard HTML file, indicates how the resulting data is displayed, what lines constitute the detail lines of information, and more.

From Fig. 11.1, you can see that the engine that is doing the database work with ODBC is HTTPODBC.DLL. This DLL, included when you install the IIS system, is an Internet Server API (ISAPI) application that runs as an extension to the server software. This extension is database-aware and is able to uses the two source files, the IDC and HTX files, required to give the information back to the user.

If you did not install the ODBC component of IIS, you'll need to do so to use the IDC. This installs not only the ODBC portions of the environment, but it also configures the server to be aware of the IDC files you'll be using. If you do not install the ODBC components, when users click on the IDC link on their web page, they'll see a prompt to download the IDC file, rather than view the results of the query. See Fig. 11.2 for an example of this prompt.

Fig. 11.2 - If the server does not recognize the database connector, it will try to download the IDC page to the requesting user, rather than processing it and returning the results.

When you install IIS, the ODBC option must be selected. Though it may not indicate disk space requirements if you have already installed ODBC from other applications, it will still be necessary to install it to activate the IDC capabilities. See Fig. 11.3

Fig. 11.3 - Select the ODBC option to install IIS.

It's a good rule of thumb to select ODBC for all installations. Databases are sure to be involved in your system and the disk space requirements are very minimal. The setup program is also careful not to overwrite any newer versions of the drivers you may have installed on your system.

Listing 11.1 shows a sample IDC file, taken from the samples included with the server. The sample installs into the \SCRIPTS\SAMPLES subdirectory on your system in the IIS directory structure.

Listing 11.1 11LIST01--A Simple IDC Source File

Data source: web sql
Username: sa
Template: sample.htx
SQLStatement:
+SELECT au_lname, ytd_sales from 
pubs.dbo.titleview where ytd_sales>5000

Any time you work with the Registry you should use extreme caution. Making entries in incorrect locations or with incorrect syntax can render your system inoperable.

You'll use the Regedt32
application to modify the registry.  When you start it,
look for the following key location:HKEY_LOCAL_MACHINE
SYSTEM
CurrentControlSet
Services
W3SVC
Parameters
ScriptMap

If you are indicating parameters to the call, you can use a "%s" on the key value where you indicate the application to run. For example, suppose you have a DLL that you want to run any time a request is received to open a file with a .FUN extension. Your entry would be as follows:

.fun = c:\inetsrv\scripts\test\fundll.dll %s %s

For the examples here, the URL that would be used would be something like the following:

http://holodeck3/scripts/search.fun?text+to+find

The resulting command line would be:

c:\inetsrv\scripts\test\fundll.dll search.fun text+to+find

Because parameters are passed as a single string to your application, as is the preceding example with the "text+to+find" string, your application must be able to parse out the plus signs and rebuild the string, most likely in a buffer that can be used by your application to search the database or text file as needed.

The results-formatting file, or HTX file, is where things can get a little tricky. As you'll see throughout this chapter, the real power and capability of your system is exposed with the HTX file. Until the information is provided to the template, it's of no use to the requester as he will have not yet seen the information. You can have one of the best, most comprehensive databases around, but if the presentation of the data is not what your audience needs, the information might as well be under lock and key.

Listing 11.2 shows a simple HTX template, provided in the samples with the IIS product, that displays the results of a query.

Listing 11.2 11LIST02--HTX source files provide template information for the display of results from database queries.

<HTML>
<HEAD><TITLE>Authors and YTD 
Sales</TITLE></HEAD>
<BODY BACKGROUND="/samples/images/backgrnd.gif">
<BODY BGCOLOR="FFFFFF">
<TABLE>
<TR>
<TD><IMG SRC="/samples/images/SPACE.gif" 
ALIGN="top" ALT=" "></TD>
<TD><A HREF="/samples/IMAGES/db_mh.map"><IMG 
SRC="/SAMPLES/images/db_mh.gif" ismap BORDER=0 
ALIGN="top" ALT=" "></A></TD>
</TR>
<tr>
<TD></TD>
<TD>
<hr>
<font size=2>
<CENTER>
<%if idc.sales eq ""%>
<H2>Authors with sales greater than 
<I>5000</I></H2>
<%else%>
<H2>Authors with sales greater than 
<I><%idc.sales%></I></H2>
<%endif%>
<P>
<TABLE BORDER>
<%begindetail%>
<%if CurrentRecord EQ 0 %>
<caption>Query results:</caption>
<TR>
<TH><B>Author</B></TH><TH><B>YTD 
Sales<BR>(in dollars)</B></TH>
</TR>
<%endif%>
<TR><TD><%au_lname%></TD><TD 
align="right">$<%ytd_sales%></TD></TR>
<%enddetail%>
<P>
</TABLE>
</center>
<P>
<%if CurrentRecord EQ 0 %>
<I><B>Sorry, no authors had YTD sales greater 
than </I><%idc.sales%>.</B>
<P>
<%else%>
<HR>
<I>
The web page you see here was created by merging the 
results 
of the SQL query with the template file SAMPLE.HTX.  
<P>
The merge was done by the Microsoft Internet Database 
Connector and 
the results were returned to this web browser by the 
Microsoft Internet Information Server.
</I>
<%endif%>
</font>
</td>
</tr>
</table>
</BODY>
</HTML>

<%if idc.sales eq ""%>
<H2>Authors with sales greater than 
<I>5000</I></H2>
<%else%>
<H2>Authors with sales greater than 
<I><%idc.sales%></I></H2>
<%endif%>

As mentioned earlier, the IDC source file indicates the ODBC data source that is used to access the database on your system. From the IDC file listing, you'll notice the "Data source" item. This item indicates that the "web sql" data source will be used. Before this sample will work on your system, you must have installed and configured the data source for that name using the ODBC control panel applet.

In the next couple of sections, you'll see how to set up the ODBC data sources for both SQL Server and Microsoft Access. You can use any 32-bit ODBC data source with your IIS application, and changes between setting up other data sources should be minimal, so if you use the information presented here, you'll find that the IDC can work with nearly any database installation you may need to use.

Building ODBC Data Sources for SQL Server Databases

You may recall that IIS is running as a service. This means that, while it's running, it's not logged in as you, the administrator, but running in the background, logging in when needed as either the anonymous user you've set up, or as the validated user that's been authenticated by the NT security subsystem. Since you want to give this service access to a database, and since you don't know whom the service will be logging in as, you need to set up the database source a bit differently than you may be accustomed to.

Microsoft has added a new option to the ODBC configurations to support a System DSN. These special data sources give you a way to set up a globally available data source. Since users that logon may be set up to have different access to your system and resources, you need to use the System DSN to make sure they have access to the right databases, regardless of where they login, or who they login as. Fig. 11.4 shows the ODBC setup dialog box, started from the Control Panel.

If you find that you receive errors trying to access an ODBC data source from your Web pages, one of the first things you should check is to make sure the data source you're referencing is set up as a system data source. If, when you start the ODBC manager utility, the data source is listed in the initial dialog box, it's defined as a user-based data source, not a system DSN. Remove the user-based DSN and redefine it as a System DSN and you'll be able to see the database. Remember, the only data sources that the Database Connector can use are the System-level data sources.

Fig. 11.4 - ODBC setup for IIS requires that you select the System DSN to configure the driver.

Once you select System DSN, you'll be able to use essentially the same options to set up the drivers. Note, too, that you can have more than one driver set up at the system level. This allows you to set up drivers for the different applications that you'll be running on the Web. Fig. 11.5 shows the data source set up dialog box.

Fig. 11.5 - Setting up a system-level ODBC driver configuration is much the same as establishing a new ODBC configuration. You'll need to indicate the driver, database, and other information required to connect to your database engine.

The Data Source Name you provide is what you'll be using in the IDC file as the data source, so be sure to make note of the database configuration names you set up.

In most cases, you'll want the configuration to be as specific as possible. If it's possible to indicate a default database and/or table, be sure to do so. It will take some of the variables out of the Web page design that you'll be doing. By specifying as much information as possible, you'll be helping to make sure that the information is accessible.

Building ODBC Database Sources for Microsoft Access Databases

Of course, there will likely be changes in the SQLStatement options you indicate in the IDC file. These differences relate to how Access interprets the SQL language elements. By and large, however, the statements should be nearly identical, especially in those cases where you're issuing SQL statements that are basically SELECT statements, rather than calling stored procedures, which are not supported by Access.

When you create the DSN, you'll be prompted to select the database with which ODBC should connect. Be sure to provide this information because, even though you can indicate it in code, you can make the connection to the database far more bullet-proof with this option turned on. The system won't have to guess where to go to get the information.

User Rights and Security Concerns

The way the login process works is to first validate the user using the anonymous login if it's enabled. If enabled, and that user indicated as the anonymous user does not have sufficient rights, he'll receive an error message that indicates the fact that he may not have rights to the object(s) requested.

If anonymous login is disabled, the IDC will use the current user's name and password to logon to the database. If this fails to gain access, the request is denied and the user is prevented from accessing the database requested.

In short, if you want anonymous users gaining access to your system, you'll need to create the user account that you want to access the information. Next, assign the user to the database and objects, allowing access to the systems needed.

The second option you have is to use NT's integrated security with SQL Server. Using this method, the currently logged-in user will be logged on to SQL Server and the same rights will be in force.

Building Dynamic Web Pages

There are three components to this type of page:

• Initial source HTML document often containing form fields or other options

• The IDC file for making and carrying out the database commands and data acquisition

• The HTX file for presenting the information returned

While it's not the intent of this book to teach all aspects of HTML, it's important to keep in mind that the examples provided are just that--samples. You'll need to take these samples and adapt them to your organization's way of doing business on the Intranet. In short, the HTML that may be required are the field, listbox and checkbox options provided by HTML. Using these options, and the ODBC connectivity, you can allow the user to search the possibilities for making a meaningful interface for the user.

When you create a form that you'll be using to prompt the user for information, you create fields and other controls much like you do when creating an application. You'll name the fields, and then pass the name and its value to the IDC to be used in your database query if you desire. In the next sections, you'll see how you go about creating these files and what makes them drive the output pages with the information from the database.

Building Initial Forms To Prompt For Values

Listing 11.3 11LIST03--Simple HTML Form To Initiate a Database Query - (queform.htm)

<HTML>
<HEAD>
<TITLE>
Que Publishing's Very Simple Demonstration Form
</TITLE>
</HEAD>
<h1>Sample Form for Database Access</h1>
<FORM METHOD="POST" ACTION="/scripts/que/QueForm1.idc">
Enter Name to Find in the Pubs Database: <INPUT 
NAME="au_lname">
<p>
<INPUT TYPE="SUBMIT" VALUE="Run Query">
</FORM>
</BODY>
</HTML>

The letter case is not significant when you're specifying HTML tags. "INPUT" is the same as indicating "input" and will not cause any different results when it's processed by IIS.

It's not immediately apparent what those parameters might be, but if you examine the one or more INPUT fields, you can see that they are named. The following syntax is the basic, required element if you need to pass information back to the host in a forms-based environment:

<INPUT NAME="<variable name>">

<INPUT NAME="MyName" VALUE="Wynkoop">

Fig. 11.6 - Allowing the user to indicate values to pass to the database engine adds polished, functional benefits to your application.

As you can see, the text box size is automatically determined for you as a default. There are Maxlength and Size tags that you can place in the INPUT NAME directive if you need to increase the size of the text box. You'll also notice that, if you press Enter while you're using this form, the form will automatically be submitted to the server just as if you'd pressed the Submit button. Since there is only a single button on this form, the browser interprets this as a type of "there is only one thing for me to do, so I'll just do it automatically" situation.

What happens in this example from Listing 11.3 is that the browser opens a new URL on the server with the specification:

http://holodeck3/scripts/que/QueForm1.idc?au_lname=<name>

If you watch your Web browser, it may only indicate that it's loading the URL that is included up to the "?" in these examples. The protocol is still passing the parameters to the host, they are simply not shown during the transfer by some browsers.

The <name> is the name you indicate in the text box prior to pressing Enter or clicking the submit button. The next step in the process is to run the query against the database engine and see what results are returned.

Building Server Query Source Files

To reiterate the note earlier about security, be sure you place your IDC files in directories that have been set up with Execute, but not Read, privileges. This is important because, if users can review your source files, they can see column names, table names, SQL login names and passwords, etc. This is information you want to make sure is private.

Listing 11.4 11LIST04--The IDC file that is called by QUEForm.HTM - (queform1.idc)

Datasource: web sql
Username: sa
Template: queform1.htx
SQLStatement:
+SELECT au_lname, phone, address, city, state, zip
+ from authors
+ where au_lname like '%au_lname%%'

In the example in Listing 11.4, you'll notice that the Where clause specifies "like" and that there is an extra percent sign in the comparison field. This is standard SQL syntax that allows you to search for wildcarded strings. You specify the part you know and the IDC will append an extra "%" character at the end. Since the percent sign is the wildcard for SQL Server, you'll be able to return all items that start with "B" for example.

Some basics about this source file are important to understand prior to working down through it to explain how it works. First, to reference a variable, you place it between percentages, as is the case with '%au_lname%' above. Note that the single quotes are required as the field is a text-based type.

You can reference variables anywhere in the script. This means that, even for the items that are seemingly hard-coded, like parameters, you can allow the user to specify them and then call them dynamically from the IDC file.

Second, in cases where your line length is shorter than your actual line, you can call out the item you want to work on, begin specifying the values and continue indicating the expanding values as long as you place the "+" in the first column of the file. The plus sign acts as a line-continuation character for these source files.

The Data source indicated in the IDC relates to the ODBC data source you establish with the ODBC manager in the control panel. Remember, the data source you use with the IDC must be a system DSN. If it's not, the call to the database will fail.

The Username, and optionally the Password, will override any settings you may have established in ODBC, and they'll override the current user name as well as it relates to the execution of the query. Other parameters that may be of interest or use in your integration of the IDC file into your installation are shown in Table 11.1.

Table 11.1-IDC Optional Parameters

Expires

If you submit a query over and over again, you may find that you're retrieving a cached copy of the information, rather than an updated database query. This can be especially problematic when developing applications as you'll be continually testing the system, re-submitting queries, etc. By setting the Expires tag, established in seconds, to a value that represents a timeframe that should have passed before the query is retried, you will avoid this problem. In other words, how long will it be before the information should be considered "stale" or in need of being refreshed for viewing.

MaxRecords

If you are connected over a slower speed connection, there are few things more frustrating than receiving a huge data file, then realizing that you only needed certain bits of information. For example, you may have a need to return only the first 100 rows of a table as they will provide the most current, meaningful data to your sales effort. By limiting the MaxRecords, you can indicate this in the IDC file, limiting traffic and database interaction with the new option.

You can call SQL Server's stored procedures from an IDC file if you want to specify it in the SQL Statement portion of the file. To do so, use the following syntax: EXEC MySP_Name Param1[, Param2...] Include the name of your stored procedure in place of "MySP_Name." In the stored procedure, be sure you're returning results sets, even if they represent only a status value indicating success or failure on the operation. Remember, as with other ODBC data sources, the stored procedure will be passed to the server and the client will await the response. If your stored procedure does not return a value to the calling routine, you may give the user the impression that you've caused the browser to become frozen.

From here, once you've retrieved the values you want to display, you can move on to the Results-set source files. These files do the work of formatting and displaying information to the user and are explained next.

Building Results Source Files

Listing 11.5 shows the sample HTX file for the example you've been reviewing throughout this chapter. You can see that it has a few extra, not-yet-standard items that make the display of information from the database possible.

Listing 11.5 11LIST05--A sample HTX file - (QueForm1.htx)

<!-- Section 1>
<HTML>
<HEAD>
<TITLE>Authors Details</TITLE>
</HEAD>
<TABLE>
<tr>
<TD>
<hr>
<P>
<TABLE BORDER>
 <caption>Query results:</caption>
 <TR>
 <TH><B>Author</B></TH>
 <TH><B>Phone</B></TH>
 <TH><B>Address</B></TH>
 <TH><B>City</B></TH>
 <TH><B>State</B></TH>
 <TH><B>Zip</B></TH>
 </TR>

<!-- Section 2>
<%begindetail%>
 <TR>
 <TH><B><%au_lname%></B></TH>
 <TH><B><%phone%></B></TH>
 <TH><B><%address%></B></TH>
 <TH><B><%city%></B></TH>
 <TH><B><%state%></B></TH>
 <TH><B><%zip%></B></TH>
 </TR>
<%enddetail%>

<!-- Section 3>
<P>
</TABLE>
<%if CurrentRecord EQ 0%>
  <H2>Sorry, no authors match your search criteria 
(<%idc.au_lname%>).</H2>
<%else%>
  <H2>Authors with names like "<I><%idc.au_lname%></I>"</H2>
<%endif%>
</center>
</td>
</tr>
</table>
</BODY>
</HTML>

The lines starting with "<!--" are comments and are not interpreted by the HTML client.

When you design data-oriented pages, you'll want to make sure you take advantage of HTML's start and end-tag metaphor. To put it simply, for many of the different items in HTML, when you establish a tag, for instance <HEAD>, until the reciprocal argument, </HEAD>, is encountered, the feature you enabled with the first instance of the keyword is in force.

As you can see in Fig. 11.7, the example file turns on the H1 heading style and then doesn't turn it off, resulting in the entire page using oversized fonts.

Fig. 11.7 - Since HTML tags are evaluated in pairs, missing the closing tag can make a style run through the balance of your HTML document.

In the sample HTML in Listing 11.5, you'll notice that there are three sections called out. These sections are inserted only to make reading and explaining the HTML a bit easier. They aren't necessary for the functioning of the document.

In Section 1, the entire purpose is to set up the page. You'll need to establish fonts, set up background images, do any initial formatting, etc. You'll also need to start any tables that you want to use. Since you initiate a table, add the rows to it, then turn off the table, tables represent an excellent way to present data that will include an unknown number of rows.

For example, as in Fig. 11.8, though two rows are shown, there could just as easily have been 20. The other advantage of using tables to display your database information is that the table will size to the user's visible browser area automatically. You won't need to worry about column widths and other formatting issues.

Fig. 11.8 - If you can use tables to display data to the user, you'll be keeping with an already familiar metaphor for the presentation. People are used to table-based data represented in a columnar fashion.

Section 2 is where you'll work with the detail lines that are returned as part of the data set. You'll notice that this section is bracketed with a <%begindetail%> and <%enddetail%> tags. Everything between these two tags will repeat once for every row returned in the data set. In the example above, section 2 consists largely of building the table that will display the information that was returned.

<! Section 2>
<%begindetail%>
 <TR>
 <TH><B><%au_lname%></B></TH>
 <TH><B><%phone%></B></TH>
 <TH><B><%address%></B></TH>
 <TH><B><%city%></B></TH>
 <TH><B><%state%></B></TH>
 <TH><B><%zip%></B></TH>
 </TR>
<%enddetail%>

You can do comparisons in your file as well. For example, if you wanted to check to make sure that the State was returned as "AZ," you could do so in one of two ways. Obviously the preferred method would be to change your Where clause in the IDC to reflect the fact that you want to filter out non-Arizona states.

Alternatively, you could indicate here that you want to test. Consider the following code sample:

<! Section 2>
<%begindetail%>
<%if <%state%> eq "AZ"%>
<TR>
<TH><B><%au_lname%></B></TH>
<TH><B><%phone%></B></TH>
<TH><B><%address%></B></TH>
<TH><B><%city%></B></TH>
<TH><B><%state%></B></TH>
<TH><B><%zip%></B></TH>
</TR>
<%endif%>
<%enddetail%>

<%if <%idc.au_lname%> eq "Wynkoop">
     <TH><B>Building series...</B></TH>
<%endif%>

You can also use <%else%> in your If...else...endif loop.

Table 11.2-Comparison Operators for Use in HTX Files

EQ	indicates an equivalent test. "Is item A equal to item B?"
GT	Tests for a condition where one item is greater than the other.
LT	Tests for the condition where one item is less than the other.

In addition, there are two different data set related variables. CurrentRecord allows you to reference the number of times the Detail section has executed. If, after the detail loop has run, you want to determine whether there are records in the data set, you can test this variable to see if it's 0. If it is, no information was returned and you should display a meaningful message to that effect.

<%if CurrentRecord EQ 0>
  <H2>Sorry, no authors match your search crit...
<%else%>
  <H2>Authors with names like "<I><%idc.au_lname%>...
<%endif%>

You use both CurrentRecord and MaxRecords in conjunction with <%if%> statements. They are implemented as controlling variables that help in your structuring of the logical flow of the HTX file. Just keep in mind that, after the processing of the detail section has completed, if CurrentRecord EQ 0, there were no results returned from the call.

The final section of the HTX file is used largely to close different HTML tags that were used to set up the display of information on the resulting page. Remember, HTML expects most tags in pairs, so it's a good idea to close each item properly.

<!-- Section 3>
<P>
</TABLE>
<%if CurrentRecord EQ 0%>
  <H2>Sorry, no authors match your search criteria 
(<%idc.au_lname%>).</H2>
<%else%>
  <H2>Authors with names like 
"<I><%idc.au_lname%></I>"</H2>
<%endif%>
</center>
</td>
</tr>
</table>
</BODY>
</HTML>

The results of a successful search are shown in Fig. 11.9.

Fig. 11.9 - A successful match will show the hits on the PUBS database table, and will then show the message indicating what was searched for.

If the search of the database tables is not fruitful, the HTX will display a different message indicating the failure of the process. Fig. 11.10 shows this dialog box.

Fig. 11.10 - If matches for information are not found, you should code a branch of logic to indicate the problem to the user.

Internet Database Connector: A Summary and Example

The result to the user is the display of the requested information in an HTML document style. Of course, the resulting document, based on the HTX file, can include further links to queries or drill-down information if needed. By using this technique, you can allow a user to select high-level values and then narrow the scope, but increase the detail level provided, for the information as the user is able to narrow the parameters for the operation.

An excellent example of the drill-down technique is provided in Microsoft's samples, in the GuestBook application. As you query the guestbook, you are returned high-level detail about the names found. Here's a look at the HTX file's Detail section to see what exactly is done to display the information from the database.

<%begindetail%>
Name: <a href="/scripts/samples/
details.idc?FName=<%FirstName%>&
LName=<%LastName%>"><b>
<%FirstName%> <%LastName%></b></a>
<p>
<%enddetail%>

Fig. 11.11 - The initial display of the guestbook contents allows the user to select a name and drill down into the details for that name.

When you click on a name to get the details, the IDC is called that retrieves the details from the Guests table on your system. If you take a look at the IDC, you'll see that it's quite simple, returning only a few columns of information based on the name selected from the previous query.

Datasource: Web SQL
Username: sa
Template: details.htx
SQLStatement:
+SELECT FirstName, LastName, Email, Homepage, 
Comment, WebUse
+FROM Guests
+WHERE FirstName = '%FName%' and 
LastName = '%LName%'

Listing 11.6 11LIST06--The Detail Section from the DETAILS.HTX file

<%begindetail%>
<h2>Here are details for 
<%FirstName%> <%LastName%>:</h2>
<p>
<b><%FirstName%> <%LastName%></b><br>
<p>

<%if Email EQ " "%>
<%else%>
Email Address: <%Email%> <br>
<%endif%>
<%if Homepage EQ " "%>
<%else%>
Homepage: <%Homepage%>
<%endif%>
<p>
Primary Web Role: <%WebUse%>
<p>
<%if Comment EQ " "%>
<%else%>
Comments: <%Comment%>
<%endif%>
<p>
<%enddetail%>

This technique is really great for supplying company information. You can provide overview type items at the highest level on everything from marketing materials to personnel manuals. Letting people select their research path also alleviates you from the responsibility of second-guessing exactly what the user is expecting of the system.

Using SQL Server's Web Page Wizard

With SQL Server 6.5, you have the ability to schedule a task in the system to automatically create these HTML documents at time intervals ranging from a one-time run to many times per day. You can use this capability for a number of things, including reporting on your server's activity levels to you as an administrator.

In this section, you'll see how to set up these automatically generating pages and what the results of the process are. It's not possible to go into great detail on how to use SQL Server, forming good database table design and other administrative issues regarding SQL Server as they warrant a much more comprehensive discussion than a single group of sections here.

Prerequisites for SQL Server

To confirm that the service is set to automatically start, select Services from the Control Panel. Scroll down the list of services that are installed until you see the SQLExecutive service. See Fig. 11.12.

Fig. 11.12 - Make sure the SQLExecutive service is listed with a Status of "Started" and that Startup is listed as Automatic.

If the service is not already started, click on the service and select Startup. You'll be able to set the options that govern when the service is active and, most importantly, when it will be started by the operating system. You'll want to make sure you indicate a valid user account that will be used to login to SQL Server. This account must exist in both SQL Server and the User database for your domain if you are not using Integrated security, and the user name and password must be the same in both SQL Server and the domain.

If you're using integrated security, selecting a user from the domain user's list will also provide the name to be logged in to the SQL Server.

It's a requirement that the information you provide as it relates to the user and password is valid in SQL server. You will also need to ensure that the account you indicate has access to the database you're reporting against, and the MSDB database. See Fig. 11.13.

Fig. 11.13 - The account you indicate for logon for the service must have access to the different objects in your database(s). Of extra importance is full access to the MSDB database and the database containing the information you want to report on, since these are key to the creation of the page.

If you do not set up the SQLExecutive to automatically start, the services required to generate the Web content you are setting up will not be available and the page will not be generated. If this is the first time you're setting these options, and the SQLExecutive was not previously started, when you select OK to save the user ID and startup option changes, you'll need to re-select the SQLExecutive service and then select Start.

Using the Wizard

Fig. 11.14 shows the initial SQL Server login dialog box that you're presented with from the Wizard. From this dialog box, you'll need to provide an appropriate login that will allow you access to all tables and databases you want to use in providing content for your page.

Fig. 11.14 - Logon to SQL Server is the first step to using the Wizard.

The option to use Windows NT Security to logon assumes you're using integrated security. If you are, selecting this item means that you don't have to provide separate login account and password information prompted for earlier in the dialog box and that the user will be logged on with their own security rights intact for SQL Server. Their NT logon name is used as their SQL Server logon name.

Selecting the Content for the Page

Fig. 11.15 - The easiest interface is the Database Hierarchy option as it allows you to select from the listing of objects on your server when deciding which items to provide for a report.

If you select the Free Form query option, you'll be able to create any SQL statement you need to fulfill the requirements for your software. Be sure to select the database you need to work against from the "Which database do you want to query?" list box, or you may end up querying the wrong table and you'll need to come back and rewrite the query or queries on which you based the page.

Using this option also means that you're taking all responsibility for the formation of SQL server specific calls. The query you enter will be passed along and executed by the server. Fig. 11.16 shows an example of how the dialog options work when working with the Wizard in this scenario.

Fig. 11.16 - Entering the query manually can be powerful and painful all at once. If you're not sure of the syntax, be sure to use the more automated features of the Wizard for a few instances of information you want to publish. Once you've completed a few, you'll begin to better understand the different things involved in creating the queries manually.

The final option you have in setting up the source of information for your page is to call a stored procedure. When you select the Use a query in a stored procedure button, you'll be able to provide information on the database and stored procedure you want to use. You'll also notice that the text of the stored procedure is shown in the dialog box. You can use this information to verify that you have selected the correct stored procedure. See Fig. 11.17.

Fig. 11.17 - Calling a stored procedure can be a good way to share coding you've done for an application and put it to use on your Intranet.

Stored procedures are a powerful mechanism for optimizing your server and providing good database query tuning. You can also take advantage of the fact that, if you have another system based on SQL Server, and you're using a stored procedure to produce the results for a printed report, you may be able to reference the same stored procedure in the dialog box and create the report in HTML, making it available at any time.

The text box showing your stored procedure is provided as reference only; you cannot make changes to the code here. If you need to make changes to the code, you'll need to do so by updating the stored procedure in SQL Server. This can be done with ISQL/W, the SQL Enterprise Manager or any other utility you may be using to manage your stored procedures.

Setting the Update Interval

A frequency set to be too small will cause additional overhead on the server as it handles the request. The impact on performance should be minimal, but if there are many, many requests for data pages such as this, it may begin to show on the access times to the server.

Your time-frame options, and their associated parameters are:

• Now, No parameters

• Later, specify date and time that the page should be created (once only)

• When data changes, select data tables and indicate anticipated change that should be monitored as a trigger to update the HTML code.

• On certain days of the week, indicate day of week and time of day.

• On a regular basis, specify number of hours, days or weeks that should pass before the item is regenerated.

Fig. 11.18 - Be sure to select a useful time interval. Too frequent will force the server to rebuild the page without reason. Too infrequent of an update process makes the information a bit less useful as it may go out of date for the users of the information.

Setting Page Options

Fig. 11.19 - Review the formatting options carefully. These are the items that will be used to manage the content, appearance and other facets of your HTML's presentation.

Fig. 11.20 - If you are relying on the engine to do formatting for your tables lf data, be sure to select the "Include column or view column names with the query results when you set up the Wizard's Web page.

One thing that you'll want to consider including on every page you generate is a link to another page on your site. For example, you may want to always include a link with the following attributes: Description: Return to Home Page URL: http://www.<your site>.com/default.htm It's a good idea to come up with a set of links that you include on each page on your site. These may be back to the home page, back to a search page and to a copyright page, for example. Consistency across your site will make it much easier for users to navigate and understand.

Once you've made any changes you need, you can select Finish to generate the code that will be used to execute the different operations that manage the Wizard and its pages. Now, whenever the page is referenced by a browser, the HTML generated by the Wizard will be the results. The user will be able to see the new view you've constructed and will be assured of up-to-date information.

Seeing the Results in SQL Server

• A master stored procedure is inserted into your database

• A page-specific stored procedure is inserted into your database

• A new task is created to be run by the SQL Executive at the intervals you requested

The master stored procedure, sp_makewebpage, is created in the database you're setting up. This is used by the Wizard to create the code necessary to generate the page. You won't be making changes to this or the other stored procedures, and if you ever want to re-create it, you can run the Wizard again and create another page in the same database. This will create the stored procedure for you.

The page-specific stored procedure is created with a name that begins with "Web_" and includes a unique numeric name that included the date it was created. In the example shown in Fig. 11.21, you can see that the stored procedures created to support the demonstration pages are named Web_96042622390211 and Web_96042700554612. If you review the stored procedures, you'll see that they're encrypted, so you won't be able to make any changes to them directly. Of course, the easiest way to make any changes you need is to remove the stored procedures and their associated tasks from SQL Server and then re-create them using the Web Page Wizard.

Fig. 11.21 - Be sure not to remove the stored procedures from the database unless you also remove the corresponding task entries from the task scheduler.

By selecting Server, Tasks... from the menus, you can review the tasks that have been created to run the page at the intervals you set up. Fig. 11.22 shows what you'll see. You can quickly determine that the Web pages are queued up and ready to go.

Fig. 11.22 - After the Wizard has finished, the task is entered into SQL Server for processing as you established. If there is ever a question about whether a SQL database-oriented item ran, check the Task Manager and see if there are error messages indicating a problem with the configuration.

You can also check to make sure a Web page generation process is occurring as you'd expect by clicking on the History button from the Task Scheduling dialog box. When you do, you'll see the dialog box shown in Fig. 11.23, which indicates the times the procedure has run and whether it was successful. It's a good idea to start with the history review process in any diagnostics you need to run in the future should you encounter problems. In most cases, you can quickly determine exactly what's wrong by just doing some quick investigation with the Task Scheduler.

Fig. 11.23 - You can verify that the pages are being generated successfully by reviewing the History logs.

Keep in mind that you can also change the frequency at which your page is generated by modifying the task scheduling options. If you double-click the page you want to modify, you can set all of the different options that control how often the page is generated. This might be helpful if you find that, after installing several pages and your site traffic picks up, you need to lessen server load a bit to provide better throughput at peak times. Simply change the times at which the pages are generated and you'll be set.

Seeing the Results on Your Web Site

Listing 11.7 11LIST07-- A Sample Page Created By The Wizard - (Web.html)

<HTML>
<HEAD>
<TITLE>SQL Server Web Page Wizard</TITLE>
<BODY>
<A HREF = /default.htm>Back to top of site</A>.<P>
<HR>
<H1>Query Results</H1>
<HR>
<PRE><TT>Last updated: Apr 26 1996 10:43PM</TT></PRE>
<P>
<P><TABLE BORDER>
<TR><TH ALIGN=LEFT>
au_id</TH><TH ALIGN=LEFT>
au_lname</TH><TH ALIGN=LEFT>
au_fname</TH><TH ALIGN=LEFT>
phone</TH><TH ALIGN=LEFT>
address</TH><TH ALIGN=LEFT>
city</TH><TH ALIGN=LEFT>
state</TH><TH ALIGN=LEFT>
zip</TH><TH ALIGN=LEFT>
contract</TH></TR>
<TR><TD NOWRAP>
172-32-1176</TD><TD NOWRAP>
White</TD><TD NOWRAP>
Johnson</TD><TD NOWRAP>
408 496-11223</TD><TD NOWRAP>
10932 Bigge Rd.</TD><TD NOWRAP>
Menlo Park</TD><TD NOWRAP>
CA</TD><TD NOWRAP>
94025</TD><TD NOWRAP>
1</TD></TR>
<TR><TD NOWRAP>
213-46-8915</TD><TD NOWRAP>
Green</TD><TD NOWRAP>
Marjorie</TD><TD NOWRAP>
415 986-7020</TD><TD NOWRAP>
309 63rd St. 
#411</TD><TD NOWRAP>
Oakland</TD><TD NOWRAP>
CA</TD><TD NOWRAP>
94618</TD><TD NOWRAP>
1</TD></TR>

...
<Edited for brevity see disk file for 
full listing>
...

<TR><TD NOWRAP>
899-46-2035</TD><TD NOWRAP>
Ringer</TD><TD NOWRAP>
Anne</TD><TD NOWRAP>
801 826-0752</TD><TD NOWRAP>
67 Seventh Av.</TD><TD NOWRAP>
Salt Lake City</TD><TD NOWRAP>
UT</TD><TD NOWRAP>
84152</TD><TD NOWRAP>
1</TD></TR>
<TR><TD NOWRAP>
998-72-3567</TD><TD NOWRAP>
Ringer</TD><TD NOWRAP>
Albert</TD><TD NOWRAP>
801 826-0752</TD><TD NOWRAP>
67 Seventh Av.</TD><TD NOWRAP>
Salt Lake City</TD><TD NOWRAP>
UT</TD><TD NOWRAP>
84152</TD><TD NOWRAP>
1</TD></TR>
</TABLE>
</BODY>
</HTML>

Fig. 11.24 - The sample Web page includes information from the Pubs database and a link back to the site home page.

A great use of the Web Page Creation is to query the IIS logs that can be placed into SQL Server. For more information, see the next section which will show how this can be used to remotely monitor your site.

Logging IIS Accesses to ODBC Databases

As mentioned earlier in this chapter, to allow IIS to access your database, you'll need to set up a System DSN. Once you've completed this, you can create the database, table and user that you'll need to establish the Internet Service Manager for the Web, FTP and Gopher services you're using.

To change logging to use your ODBC database, first double-click the service from the Internet Service Manager that you want to set up. In the example shown in Fig. 11.25, you can see the logging options for the Web Services. Note that all of the different services use the same logging tab, so once you've set one up, you'll understand how to establish the remaining services.

Before you can point the services to the ODBC database, you'll need to create the database and corresponding table. Table 11.3 shows the column information for the table that will be used for the logging.

Table 11.3-Table Structure for Logging Table

Column	SQL Data Type*	Access Data Type	Size
ClientHost	Char	Text	50
UserName	Char	Text	50
LogDate	Char	Text	12
LogTime	Char	Text	21
Service	Char	Text	20
Machine	Char	Text	20
ServerIP	Char	Text	50
ProcessingTime	int	Number	Integer
BytesRecvd	Int	Number	Integer
BytesSent	Int	Number	Integer
ServiceStatus	Int	Number	Integer
Win32Status	Int	Number	Integer
Operation	Int	Number	Integer
Target	Char	Text	200
Parameters	Char	Text	200

*Note: Nulls are allowed for all columns in the case of SQL Server.

You may recognize this information from the discussions about installing and setting up the server components as the table structure maps directly to the different components of the standard log file when logged to ASCII files. Be sure to read over the information presented in Chapter 3 about reading and interpreting the log information.

You set up the log table to be used by all of the different services you're logging for. You'll notice the Service column will show exactly what was being done by the user and what operation was being performed by the server. Listing 11.8 shows the SQL Server script for creating the table.

Listing 11.8 11LIST07--The SQL Server Script To Create the Logging Database - (makelog.sql)

/****** Object:  Table dbo.LogTable    
Script Date: 4/28/96 10:04:11 PM ******/
if exists (select * from sysobjects 
   where id = object_id('dbo.LogTable') and 
   sysstat & 0xf = 3)
     drop table dbo.LogTable
GO

CREATE TABLE LogTable (
     ClientHost char (50) NULL ,
     UserName char (50) NULL ,
     LogDate char (12) NULL ,
     LogTime char (21) NULL ,
     Service char (20) NULL ,
     Machine char (20) NULL ,
     ServerIP char (50) NULL ,
     ProcessingTime int NULL ,
     BytesRecvd int NULL ,
     BytesSent int NULL ,
     ServiceStatus int NULL ,
     Win32Status int NULL ,
     Operation char (200) NULL ,
     Target char (200) NULL ,
     Parameters char (200) NULL 
)
GO

Fig. 11.25 - The next step to begin using ODBC for logging is to select the ODBC option and indicate the login and database information.

Once you've selected the Log to SQL/ODBC Database radio button, you'll be able to access the different set up fields for the logging. It's a good idea to set up a different database to manage the logging. If you do, you'll be able to more easily manage the logging information separately from the other information on your system.

In the example, a specific database, table and user have been created to use for the logging. If you create a user, be sure you set the rights to at least Insert when you establish them on the table. Once you apply the changes, stop and restart the service, you'll be logging all server accesses to the database.

If a user is accessing your server by using the FILE: protocol, the accesses will not be logged. These types of URLs are accessed by the client and handled by the client. Although the server will be providing the file to fulfill the request, it will not show up in the database. This is one detriment to using the FILE: type URL. If you want to be able to log accesses, consider making all links standard HTTP: type URLs, rather than providing direct links to the files.

When the logging is established, you can begin querying the database real-time to determine the activity on your server. In the next sections, you'll see some ways to provide this information in an easy-to-use and meaningful manner.

Sample Queries to Use in Reviewing Logs

• What pages are most popular?

• What time of day are people accessing the server?

• Who is accessing the server (by IP address)?

In the sample query in Listing 11.9, you can see that the database is examined to find out exactly this information, providing summary information for hits against the server.

For the scripts below, you'll need to change the database table that they reference to correctly identify your system configuration. Replace "wwwlog" and "ftplog" with the logging database that you use for your system logging.

Listing 11.9 11LIST09--Sample script to use for server reporting--Web Access - (www.sql)

SELECT "Total hits" = count(*),"Last Access" = max(logtime) 
FROM wwwlog
SELECT ""

SELECT "Hit summary" = count(*), 
"Date" = substring(logtime,1,8)
FROM wwwlog
GROUP BY substring(logtime,1,8)

SELECT ""

SELECT "Time of day"=substring(logtime,10,2), 
"Hits" = count(substring(logtime,10,2))
FROM wwwlog 
group by substring(logtime,10,2)

SELECT ""

SELECT "Page" = substring(target,1,40),
"Hits" = count(target) 
FROM wwwlog 
WHERE 
     (
      charINDEX("HTM",target)>0 
     )
GROUP BY target 
ORDER BY "hits" desc 

Listing 11.10 11LIST07-- A sample script to use for server reporting - FTP Access - (ftp.sql)

select "Summary of volume by day"

select substring(logtime,1,8), 
sum(bytessent), sum(bytesrecvd)
from ftplog 
where  bytessent > 1000 
group by substring(logtime,1,8)

select "" 

SELECT "Time of day"=substring(logtime,10,2), 
"Hits" = count(substring(logtime,10,2))
FROM ftplog 
group by substring(logtime,10,2)

select "" 

select "Target" = substring(target,1,40), 
       sum(bytessent), 
       sum(bytesrecvd),
       count(substring(target,1,40))
from ftplog 
where bytessent> 1000
group by Target
order by sum(bytessent) desc

Reviewing System Logs On-Line

Using this technique, you can create a Web page, similar to the one shown in Fig. 11.26, that will let you review your site activity all while online.

Fig. 11.26 - Keeping the site activity only a hyperlink click away is a good way to put the database query capabilities to use at your site.

From here, it's a matter of pruning your select statement to deliver more selective information. One idea would be to change the listing to only return the hits against the server by minute, for example. Then, by using an IDC link you can offer the user the ability to drill down into a given time slice to gain more detail about an activity.

Another approach to this is to provide summary information by user with links to detailed log information for that user. The initial HTML page will show all users. The viewer can select the user to review, and click on the name. The next HTML page shows the user's access times individually. By clicking on a specific access time, the reviewing party can see the details about what was accessed by that user during that session.

Integrating the IDC and drill-down informational research like this can really bring great leverage to you as an administrator. Remember, too, that you can place these pages into a protected subdirectory, making them available only to authorized users of your system.

Configuring IIS To Wait for SQL Server to Start

This comes from the fact that SQL Server requires more time to start than do the Internet services. Since the services start and immediately attempt access to the logging system, they are unable to get to the database because SQL Server is still starting. To avoid this, you'll need to make an entry in the system registry that tells IIS to wait on SQL Server when it starts the services.

From the Registry, select the following key:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\
 W3Svc\DependOnService

Add a new service as part of this key for each application that you need to have IIS wait on. With SQL Server, the service name is MSSQLSERVER. You'll also want to update the Gopher and FTP service keys. They reside in the same key location, but you'll need to substitute either GOPHERSVC or MSFTPSVC for W3Svc above. The resulting keys are:

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\
 GOPHERSVC\DependOnService

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\
 MSFTPSVC\DependOnService

Note that if you have selected logging to SQL Server and do not do these steps, the logging will fail to start and you'll end up with no logging occurring for your site. It is very important to set up these keys as soon as possible after you begin using SQL Server to log your site accesses.

The IntelliCenter Reality Check site puts into play nearly all of the different techniques listed here, including the ODBC logging. The review of the logs is a very frequent operation and is used to help drive course and online materials content from a marketing stance as well.

By putting content online, and announcing its availability both to internal student users and the customer community at large, IntelliCenter is able to see what types of materials interest people and what other classes and activities might be of interest to customers.

In addition, as class students are assigned accounts on the IntelliCenter system, the usage statistics help drive the online research content. By looking after the content that is most requested on their system, it's possible to increase the online research materials to meet demand.

Database access is used, along with the IDC interface, to provide transcripts of the classes taken by a student or a company's students, with access to the information guided by the individual that is signing in. A corporate leader from the company, as designated by the company, can review the different classes that have been taken by his employees. At the same time, an employee can review his own records, and can gain access to online materials that relate to the classes taken to date.

From Here

This chapter touches on a number of different things. More information is provided about these topics in the following areas:

• See Chapter 3, "Installing and Understanding Internet Information Server," for more information about the installation options regarding the logging of server accesses and how to interpret those logs.

• See Chapter 9, "Security Issues--Firewalls and Data Security," for more information about providing secure access to specific portions of your site. This is especially true if you're providing IIS logging in the way of HTML pages. This information should be kept secure in nearly all cases.

• See Que Publishing's "Special Edition, Using Microsoft SQL Server 6.5," for more information about using SQL server, creating queries and stored procedures against the server and more.

Copyright ©1996, Que Corporation. All rights reserved. No part of this book may be used or reproduced in any form or by any means, or stored in a database or retrieval system without prior written permission of the publisher except in the case of brief quotations embodied in critical articles and reviews. Making copies of any part of this book for any purpose other than your own personal use is a violation of United States copyright laws. For information, address Que Corporation, 201 West 103^rd Street, Indianapolis, IN 46290.

Notice: This material is from BackOffice Intranet Kit, ISBN: 0-7897-0848-5. The electronic version of this material has not been through the final proof reading stage that the book goes through before being published in printed form. Some errors may exist here that are corrected before the book is published. This material is provided "as is" without any warranty of any kind.