SQL Server Database Access with IIS

Editor's Note: Each month, this column discusses various aspects of the advanced administration of e-business sites. This month's column examines the configuration of IIS for Microsoft SQL Server access—specifically, how you can leverage the power of SQL Server in HTML and Active Server Pages (ASP) on IIS.

Most Web applications built for the Internet or corporate intranets or extranets require a database for data storage and retrieval. SQL Server 2000 and SQL Server 7.0 are fantastic database servers for Web-based applications. You can use many tools, tips, codes, and tricks to access data as it relates to pages that IIS serves. To use the solutions I present here—which I've aimed to administrators new to this application—you need access to SQL Server 2000 or SQL Server 7.0.

The SQL Server Web Assistant
First, let's look at the SQL Server Web Assistant, a simple and powerful tool that creates HTML files based on SQL Server queries without the need for any programming. You can use the Web Assistant Wizard to generate standard HTML files from SQL Server data one time only or as a regularly scheduled SQL Server task. The Web Assistant Wizard generates HTML files by using T-SQL queries or stored procedures. (I'll explain these technologies later.) Here's how you use the tool.

Run the SQL Server Enterprise Manager from Start, Programs, Microsoft SQL Server, Enterprise Manager. Drill down to your server by expanding Microsoft SQL Server and SQL Server Group to the server list, then select your server. From the Enterprise Manager main menu, choose Tools, Wizards. On the Select Wizard dialog box, click Management, then Web Assistant Wizard, as Figure 1 shows. Let's walk through the wizard's pages.

1. On the wizard's Select Database dialog box, select the pubs database from the drop-down list box. The pubs (i.e., Publications) database is SQL Server's sample database. Click Next. On the dialog box that Figure 2 shows, specify a name for the Web Assistant job (under the assumption that you'll run it again later manually or as part of a recurring schedule); the default name is pubs Web Page.
   
   
2. Choose the method by which you retrieve data from SQL Server to display in the Web page that the wizard automatically creates. These choices are available:
   
   
   • Data from the tables and columns that I select—This option sends you down a path in the wizard where you can graphically choose tables and columns that will be the source of the data in the HTML file that this tool generates. This option is the default.
     
     

   • Tip: To run the Web Assistant Wizard, you must have certain permissions. These permissions are Create Procedure permissions in the selected database Select permissions on the chosen columns Permissions to create files in the account in the instance of SQL Server

     Result set(s) of a stored procedure I select—This option lets you choose a stored procedure to execute columns that will be the source of the data in the HTML file. A SQL Server stored procedure is a precompiled collection of T-SQL statements stored under a name and processed as a unit. SQL Server supplies stored procedures for managing SQL Server and displaying information about databases and users. SQL Server-supplied stored procedures are called system stored procedures. Software developers write custom stored procedures for speed and reusability.
     
     
   • Data from the Transact-SQL statement I specify—This option lets you type a T-SQL statement. T-SQL is the language that contains the commands used to administer instances of SQL Server; create and manage all objects in an instance of SQL Server; and insert, retrieve, modify, and delete data in SQL Server tables. T-SQL is an extension of the language defined in the SQL standards that the International Organization for Standardization (ISO) and ANSI publish.
   
   
   For this example, choose the first option—Data from the tables and columns that I select—and click Next to bring up the dialog box that Figure 3 shows.
   
   
3. Select a table and the columns you want to display on the Web page. From the Available tables drop-down list box, choose authors; click Add All to select all the columns in the table. Click Next.
   
   
4. In the Select Rows dialog box, specify the rows of the table you want to be visible on the Web page that you're creating. At this point, you can limit the query by entering criteria in a logical and/or situation; or, if you're proficient in T-SQL, you can type a T-SQL WHERE clause. For simplicity here, accept the default All of the Rows. Click Next.
   
   
5. On the Schedule the Web Assistant Job dialog box, you can the specify the frequency for generating the Web page. The options include Only one time when I complete this wizard, On demand, and When SQL Server data changes. For this example, accept the default Only one time when I complete this wizard to run the query that creates the page only once. Click Next.
   
   
6. On the Publish the Web Page dialog box, choose the filename and directory in which you want to place the Web page. If you want IIS to make the page available on your Web server, place the page somewhere in the folder structure of your site. I'm placing the file in the IISAdministrator folder, which I'm creating under \inetpub\wwwroot. However, because you're creating a static Web page (with dynamic data from SQL Server), you can place the file anywhere and use Microsoft Internet Explorer (IE) to open it. After you've chosen a location and a name for the HTML page, click Next.
   
   
7. Choose either to let the Web assistant format the HTML file or to use a template file for formatting. (Consult the SQL Server product building template files.) In this case, accept the default Yes, help me format the Web page. You can also choose a different character set, such as an international character set. Choose the character set appropriate for your language, or leave the default Unicode (UTF-8). Click Next.
   
   
8. Specify titles for the Web page. Change the default Web page title to a more descriptive title (e.g., Authors from the PUBS database). Enter a title for the HTML table that contains the data. Enter a font size for the HTML table's title, and be sure you select the Apply a time and date stamp to the Web page check box. Click Next.
   
   
9. Choose column and border formatting and font characteristics. For this example, leave the defaults and click Next.
   
   
10. Choose one or more hyperlinks to add to the Web page, if you want the links. Let's leave the No default. Click Next.
    
    
11. You can limit the number of rows that SQL Server returns from the query to the page and the number of rows that are displayed on the page, as Figure 4 shows. To have the entire authors table appear on the Web page, keep the No, return all rows of data default, but choose Yes, link the successive pages together, then type 15 to limit each page to 15 rows of data. Click Next.
    
    
12. The Web Assistant Wizard's final page summarizes the options you've chosen, as Figure 5 shows. On this page, you can click Write Transact-SQL to File to keep the statement you used to query the database. Click Finish, and the wizard will generate the HTML page for you.

   Previous  [1]  2  Next