Appendix B - SQL Server and Web Pages
Many companies today are using SQL Server to run their businesses. It is a back-end product that is used by entire companies and possibly even the companies' customers to view data. Company employees might have an application that was developed for them to view the data or they might use a product such as Microsoft Access or Microsoft Excel to draw the data from the SQL Server database and view it. In either case, there is a cost associated with the purchase or writing of software and the training necessary to make the software useful.Web Page is one technology that is very promising because of its ease of use and ability to remain current (Web Page is discussed in further detail in the section entitled "Using the Web Page Wizard" Web pages are viewed using Web Browsers. Web Browsers are very easy to use and most share standard conventions for navigating the data that is displayed on the Web Pages. This common interface reduces or eliminates training costs when a Web Browser is used to view data. Another advantage to using Web Pages to display data is the fact that they're easy to update. Web Pages are files that can simply be replaced when data is added, modified, or when data becomes obsolete. Finally, Web Pages can be made widely available by placing them on the Internet. A company can allow customers or potential customers to view up-to-date information about its products and services.
Microsoft—recognizing the opportunity for businesses to leverage the data stored in their SQL Server databases using Web Pages,—has included three new system-stored procedures that aid in the creation and maintenance of such material. Microsoft also wrote a graphical interface that does the following:
- Walks a user through the thought process necessary for using the stored procedures
- Writes the stored procedures according to the information provided by a user
- Executes the stored procedures according to the information provided by a user
While Microsoft is committed to SQL Server running on a variety of chip architectures, the SQL Server Web Assistant and associated stored procedures can be used on Intel-based computers only. In addition, they can run on SQL Server 6.5 databases only. Running them on earlier versions will result in an error.
Following the easy-to-use example of their Office products, Microsoft has included several Wizards (or Assistants) in SQL Server 6.5. One of these Wizards is used to generate files containing Hypertext Markup Language (HTML). With the appropriate server software, these HTML files or Web Pages can be made accessible to a company intranet or the World Wide Web (WWW) on the Internet.
Using a Log In
Just as users must login to SQL Server to work in Enterprise Manager or ISQL/w, login information is required on the first page of the SQL Server Web Page Wizard. The name of the SQL Server to use must be supplied along with a valid user name and password. If trusted connections are enabled on the chosen server, the user name and password can be omitted and the Use Windows NT Security... check box can be selected (see Fig. 24.1).Fig. 24.1 - The Web Page Wizard can only be used if a valid login is provided.
At any time, the More Info button can be clicked to obtain more detailed descriptions of items on the page and further instructions on how to use them.
Once this page is completed, the Next button will attempt to login to the specified server (using the login settings provided) and will close this page. If the login fails, a message box will notify the user of the error and allow him to correct the information on the login page. When the correct information is given, the login is completed successfully and the Query page is shown.
Using a Query
After successfully logging into SQL Server, a query must be provided that will be used to supply the information to be included in an HTML file. There are three ways that the query can be built:
- Using a database hierarchy model
- Using a free-form text query
- Using a stored procedure
Depending upon which query type chosen, the SQL Server Web Page Wizard will prompt the user for information specific to that method. These methods are discussed in greater detail in the following three sections. Regardless of the method chosen, SQL Server will verify that the query is valid and prompt the user for modifications (if it isn't valid).
If the wrong SQL Server name was accidentally provided on the Login page, clicking the Back button will return the Wizard to that page and allow a different value to be supplied. However, when the Next button is clicked, the login settings must be verified again.
Using the Database Hierarchy to Build a Query
Building a query using the database hierarchy is done by simply browsing the database hierarchy in the middle of the Query page and selecting one or more tables, or individual columns within a table, to be included in the result set of the query. Figure B.2 shows this method being used to select the first and last name of authors with the last name 'Warner'.Fig. 24.2 - Columns are chosen graphically but additional criteria must be keyed in.
Selected tables or columns are indicated by a green highlight around their representing icon. If any additional criteria is necessary to obtain a specific result set from the query, it must be entered in the text box below the database hierarchy. In Figure B.2, this is where the authors returned by the query are limited to those with the last name 'Warner'.
Using Free-Form Text to Build a Query
If the database hierarchy method is not flexible enough for the query needed to populate a web page, the query can be entered free-form by selecting the Enter a Query as Free-Form Text radio button, choosing the database you want to use, and providing the actual query text. Figure B.3 shows a free-form query that will return a result set identical to that returned by the query in Figure B.2.Fig. 24.3 - Column data can be formatted and specifically labeled by a free-form query.
While the query in Figure B.3 returns a result set identical to the one returned by the query in Figure B.2, it formats and concatenates the data and applies a more descriptive label to the concatenated columns.
Free-form queries might already exist in a system if commonly used queries are saved in text files. If a query exists that returns the necessary data in an acceptable fashion, it has already tested and can be used as is or with formatting modification.
Using a Stored Procedure to Build a Query
Many times stored procedures exist that produce useful data that is otherwise difficult to obtain. Stored procedures that produce a result set can be used to obtain the result set for the SQL Server Web Page Wizard. An example of a stored procedure that returns a result set is shown in Figure B.4.Fig. 24.4 - Even stored procedures that require parameters can be used to obtain a result set.
When using the stored procedure method to provide a query to the SQL Server Web Page Wizard, a database must be chosen along with a stored procedure that already exists in that database. New stored procedures can't be created in the Web Page Wizard.
Using Scheduling
Data can be very volatile. For data to remain valuable it must be timely. By scheduling a query to create or update a web page at a particular time, at certain intervals, or when the data is changed in the database, SQL Server takes on the task of keeping data on Web Pages up-to-date with the data in the database. When there are hundreds of pages to keep current, this becomes a very powerful and time-saving feature.For most applications data is good for a certain period of time. The period of time might vary from hours to months. As shown in Figure B.5, the creation of a web page can be scheduled hourly, daily, or weekly.
Fig. 24.5 - Data can be out-of-date in a matter of hours.
The creation of web pages can also be scheduled to occur on particular days of the week at a specified time. This can be useful for information that is specific to a day of the week.
As well as recurring tasks that create web pages over and over at scheduled times, web pages can be created on a one-time basis. This single task can be run immediately or scheduled for a later time.
Some data might be so volatile—or so involatile—that web pages displaying the data should be updated when the data changes. As with the frequency that such data might change, a web page would have to be created quite frequently or very rarely. In either case, the update would be performed automatically by SQL Server. An entire table can be selected to trigger this event or a single column. Figure B.6 shows changes in the last names of authors, triggering SQL Server to build a new web page.
Fig. 24.6 - Frequent or infrequent changes to data make it difficult to use timed updates.
Using the File Options Page
At this point, all of the information necessary to retrieve the data to be placed on a web page has been supplied. Now the formatting and placement of the web page must be determined. As shown in Figure B.7, the File Options page defines where the web page is placed, what it will look like, and what (if any) additional web pages will be linked to it.Fig. 24.7 - Web pages can be customized and even derived from templates that are built or purchased.
A template can be used for the creation of the web pages, or SQL Server can create a generic page with a title for both the page and the query results. There is space to fill in both titles on the File Options page.
If a template isn't used, titles don't have to be provided. Titles should be used, but they aren't necessary if there is no need for them.
Many web pages contain links to other web pages. Like titles, links aren't necessary but they do make the page more appealing for most users. A single link can be given or a list of links can be supplied. If a list of links is used, a query must be supplied that returns the address of the web page and a description or reference for the page. The reference text will become the link when the page is created.
Using the Formatting Page
Finally the Formatting page of the SQL Server Web Page Wizard allows for some custom formatting of the web page. A header style must be chosen for the title of the query results. If you didn't supply a title, just accept the default because it won't make any difference. The query results must be presented in either a fixed or proportional font and can also be formatted to be bold, italic, or both. Figure B.8 shows an example of this page.Fig. 24.8 - Users may want to limit the number of rows displayed if the page is a ranking and only the top items should be shown.
Additional information can be automatically supplied by choosing formatting options at the bottom of the Formatting Page. For example, a date and time stamp can be included at the top of pages. You can include the column headings and you can limit the number of data rows placed on the page.
When you click Finish, the task you have developed is created and a final page (see Fig. 24.9) indicates the status of the created task.
Fig. 24.9 - The SQL Server Web Page Wizard alerts users when the chosen tasks have been completed and scheduled if necessary.
Table of Contents Appendix A - Installation and Setup of the Server and Client Software
