SQL Server Database Access with IIS - 19 Dec 2000 | ITPro Today: IT News, How-Tos, Trends, Case Studies, Career Tips, More

ASP files facilitate access to SQL Server data

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.

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.

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.

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.
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.
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.
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.
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.
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.
Choose column and border formatting and font characteristics. For this example, leave the defaults and click Next.
Choose one or more hyperlinks to add to the Web page, if you want the links. Let's leave the No default. Click Next.
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.
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.

Now, use Windows Explorer to navigate to the location in which the wizard placed the file. Two files (authors1.htm and authors2.htm) appear in that location because each page has a maximum of 15 rows from the SQL Server database and the Authors table had more than 15 rows. Run a browser, and navigate to the HTML file authors1.htm. Because I placed my file on my IIS Web server, I navigate to http://localhost/IISAdministrator/Authors1.htm, as Figure 6 shows.

Tip: A great reference for ADO in its most granular detail is David Sussman, et al., Professional ADO 2.5 Programming (Wrox Press, 2000).

Notice that there is a Next link on the bottom of each page. Note, however, that these links are static HTML files—snapshots of the database at the time you ran the wizard. Now, let's look at how to create dynamic SQL Server-driven Web pages with ActiveX Data Objects (ADO) and ASP.

Accessing SQL Server Data Dynamically

ADO is a powerful and easy-to-use object model for accessing data. ADO is the preferred data-access interface for developing Web-based applications when you're using IIS and ASP. ADO is the application programming interface to many sources of data, not just SQL Server.

Microsoft provides ADO as an interface layer to OLE DB. ADO is generic enough to accommodate any OLE DB provider (e.g., SQL Server, Microsoft Exchange Server, Active Directory—AD). ADO is most commonly used in ASP files in conjunction with a Data Source Name (DSN). A DSN is the logical name that ODBC uses to refer to the information required to access data. IIS uses a DSN for a connection to an ODBC data source, such as the SQL Server Pubs database.

You need to create a DSN on your IIS Web server machine. An ASP file leverages the DSN to access data from the pubs database on your SQL Server machine. To create a DSN in Windows NT 4.0, double-click the Control Panel Data Sources (odbc) applet. In Windows 2000, double-click the Control Panel Administrative Tools applet, then double-click Data Sources (odbc) to bring up the ODBC Data Source Administrator dialog box, which Figure 7 shows.

You want to create a System DSN that will be available to all users on the machine that includes the services running as a part of the OS (i.e., IIS). User and File DSNs are available to the currently logged-on user only. Go to the System DSN tab, then click Add and follow these steps:

Tip: Click Client Configuration to make sure your DSN is using TCP/IP as the network library. TCP/IP provides the fastest and most reliable access to SQL Server and is certainly the only way to make SQL Server connections in firewall and demilitarized zone (DMZ) scenarios.

To create a new data source, first identify the type of data you want to connect to—SQL Server, in this case. On the Create a New Data Source dialog box, scroll to the bottom of the list, and choose SQL Server. Click Finish to proceed with the New Data Source to SQL Server Wizard.

On the next wizard page, name your DSN pubs. Software developers usually name the DSN after the SQL Server database it's attaching to. This convention helps alleviate confusion on IIS machines that have many DSNs connecting to many SQL Server machines.
Add a description of the data source. Software developers usually include the name of the SQL Server machine and the database that the DSN is attaching to. In this case, however, the description is System DSN for the PUBS database.
Choose the name of the SQL Server machine that you're attaching to. You might have to enter its name. Click Next.
Choose the authentication credentials for the System DSN. The default choice, With Windows NT authentication using the network login ID, uses the credentials of the currently authenticated user. IIS certainly can't use this authentication because IIS administrators usually log off the system when they walk away from the machine. In that case, IIS would fail its authentication when it needed to use the System DSN to attach to the SQL Server. Therefore, choose With SQL Server authentication using a login ID and password entered by the user, then type a valid SQL server username and password that has sufficient privileges to read from the pubs database. Because I haven't written this code to run in a production environment, I've used the all-powerful sa account for my System DSN to authenticate with. Click Next.
For the Change the default database to option, choose the pubs database from the drop-down list box. You know that you already have connectivity to the SQL Server machine because the wizard lists all the databases on the SQL Server machine you've attached to. Keep the defaults for the rest of the choices, and click Next to continue.
The next page in the wizard presents options with regard to language, encryption, and logging. Retain the defaults, and click Next to continue to the final page of the wizard, which summarizes all your choices, as Figure 8, page 15, shows. Click Test Data Source to test connectivity to SQL Server.

Great! You've successfully created a System DSN that your developers can use in ASP. Now you can begin to leverage the power of SQL Server in ASP files on your IIS servers. This step is the first and most important one in database communications between IIS and SQL Server. Future articles will dive deeper into the topic and provide alternatives for SQL Server communications with IIS and practical examples of application code.

Comments

Plain text