Extending SQL Server with SQLXML 3.0 and IIS

SQLXML 3.0 provides a way of gathering data and generating XML documents. Although it is no substitute for what can be achieved with ADO.NET, it is a way to extend the functionality of SQL Server through a browser interface for creating reports, monitoring activity, and doing other tasks.

To use SQLXML, you must have Internet Information Server (IIS) and SQL Server 2000 installed, as a version of SQLXML installs with SQL Server 2000. Our environment consists of Windows XP Professional, IIS 5.1, and SQLXML 3.0.

SQLXML extends the functionality of SQL Server 2000 by allowing you to query the database through an HTTP request. This capability has many advantages along with some security concerns. Most of these concerns can be lessened by enforcing Windows Authentication on both the virtual directories that will execute queries and the database itself.

Note

You can download SQLXML 3.0 free of charge from Microsoft at http://msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/824/msdncompositedoc.xml


Installing and Configuring SQLXML 3.0

Once you have downloaded SQLXML, simply double-click on the downloaded executable file to begin installation. From there the Installation Wizard is intuitive and asks only a couple of questions: Do you agree to the license? and Do you want a custom or typical installation? The only difference between a custom and a typical installation is the ability to change the directory the files will be placed in during installation.

Configuring SQLXML is relatively simple. To begin, navigate through your Start Menu to Programs, Microsoft SQL Server, Configure SQL XML Support in IIS. If all is well, an MMC Console similar to that shown in Figure 9.3 will appear.

Figure 9.3. IIS Virtual Directory Manager for SQL Server

graphics/09fig03.jpg

Note that the pane on the right side has only one column, Computer. Double-clicking on the computer name listed there will expand a list of all of the Web sites running on the machine, as illustrated in Figure 9.4. Also note that the column name has changed to Web Site Name.

Figure 9.4. List of Web sites

graphics/09fig04.jpg

Double-clicking on the Default Web Site on either pane displays a list of configured virtual directories on the right. Figure 9.5 shows this window.

Figure 9.5. Virtual Directory pane, with no configured directories

graphics/09fig05.jpg

To begin configuring a new virtual directory, right-click on the Default Web Site node and from the context menu select New, Virtual Directory, as shown in Figure 9.6.

Figure 9.6. Context menu selection

graphics/09fig06.jpg

Before completing the next step, create the directory c:\inetpub\wwwroot\novelty. You can do so by using Explorer or clicking on Start, Run and typing "cmd". In the resulting command window, type "mkdir c:\inetpub\wwwroot\novelty", and press Enter. Within that directory, make a subdirectory and name it Templates. (All these steps will make sense shortly).

Note

Unless otherwise noted, all commands in quotes are meant to be typed without the quotes around them.


Once you've selected a virtual directory, a new dialog is presented with six tabs across the top. The first tab, General, asks you to specify a name for the virtual directory, as well as a directory to hold any files that you may want to show. Type "Novelty" in the textbox within the frame labeled "Virtual Directory Name". Next, type or click on Browse to locate and set a local directory for the virtual directory to use. Although it won't necessarily contain files, the directory must exist. Then type "c:\inetpub\wwwroot\novelty" as the local path, as shown in Figure 9.7

Figure 9.7. Setting the Virtual Directory name and local path

graphics/09fig07.jpg

Next, click on the Security tab at the top of the window and select the option Use Windows Integrated Authentication. This step is based on the assumption that SQL Server is set up to use either Mixed-Mode Authentication or Windows Authentication. If your server isn't set up this way, you can use either of the other two options, depending on the level of security you expect. If security isn't a big issue for example, if the server you're working on isn't connected to any kind of external network the first option will work well. It allows you to cache credentials in much the same way a connection string works. The third option uses HTTP-Based Basic Authentication to authenticate the user, based on the SQL Server account. Figure 9.8 shows the suggested configuration.

Figure 9.8. Security tab settings

graphics/09fig08.jpg

Now click on the Data Source tab. This dialog asks you to indicate the instance of SQL Server that should be connected to and the database being accessed. In the SQL Server frame is a textbox; enter the name of the SQL Server you want to connect to in this case "(local)" works just fine.

Note

The use of "(local)" is a "friendly name" for the server running on the local machine. If you have replication configured on your SQL Server, either as a subscriber or a publisher, the friendly names won't work and you'll have to use the actual machine name instead.


Next, in the frame labeled "Database", click on the down arrow of the drop-down select list to select the Novelty database. Note that the databases listed reflect what the credentials provided in the security settings have rights to. This window is shown in Figure 9.9.

Figure 9.9. Data Source settings

graphics/09fig09.jpg

Now click on the Settings tab at the top of the window. Be sure that the selections for Allow URL queries, Allow template queries, and Allow XPATH are selected as shown in Figure 9.10.

Figure 9.10. Settings options

graphics/09fig10.jpg

Finally, click on the Virtual Names tab. In the frame labeled "Defined Virtual Names", click on the New button. A new dialog is presented. For the Virtual name field, enter "templates". From the Type select box, select template and in the Path textbox, type "c:\inetpub\wwwroot\templates" or click on the "… " button and browse to that location. Figure 9.11 shows this dialog box. When you've filled in everything, click on Save.

Figure 9.11. Virtual Name Configuration dialog

graphics/09fig11.jpg

A window similar to that shown in Figure 9.12 should appear. Click on OK to close the New Virtual Directory window.

Figure 9.12. Virtual Names tab configured

graphics/09fig12.jpg

You now have successfully configured a virtual directory through IIS that can execute SQL queries against an SQL Server database. If you double-click on the Default Web Site node, a window similar to that shown in Figure 9.13 should appear. Now close the IIS Virtual Directory Manager for SQL Server, as it is no longer needed.

Figure 9.13. IIS Virtual Directory Manager for SQL Server with Novelty site configured

graphics/09fig13.jpg

Configuration Results

Now let's take a look at what that entire configuration has allowed you to do. Open Internet Explorer 6.0 or higher and type the URL shown in the following code. The results will look exactly like the XML document shown in Listing 9.4. We created the simple2.xml file in exactly this way. Once we had visited the URL, performing a simple Save As from the File menu in Internet Explorer created the file:

 http://localhost/Novelty?sql=select top 10 *  from tblCustomer FOR XML AUTO&root=customer 

Note that the root parameter at the end of the URL specifies what the root element of the document will be. Without it, you'll have a hard time displaying HTTP queries in a Web browser.



Database Access with Visual Basic. NET
Database Access with Visual Basic .NET (3rd Edition)
ISBN: 0672323435
EAN: 2147483647
Year: 2003
Pages: 97

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net