Creating SQL Server 2000 Virtual Directories

SQL Server 2000 includes new features that let you use XML to query your database server either directly from a browser or through ADO code. If you've used these features, you know that you must create a Microsoft IIS virtual directory that lets you access the SQL Server data. Virtual directories let you map physical folders to directories that IIS exposes to the public. In SQL Server 2000, you must not only set up virtual directories but also set several properties associated with each directory, including security settings, a data source to use when providing XML data, and permissions that let users access that data.

Creating the virtual directories manually—or automating them by using a Windows Script Host (WSH) script with Microsoft Active Directory Service Interfaces (ADSI)—isn't difficult. But walking through the manual process with more than one user will quickly sour you on the concept. Sure, you can instruct the user who is deploying your application to select Start, Programs, Microsoft SQL Server, then select the Microsoft Management Console (MMC) Configure SQL XML Support in IIS snap-in. This tool lets you create the new virtual directory manually. Then, you can walk your user through the necessary virtual directory settings (which let users access SQL Server data by using XML) on the IIS Virtual Directory Management for SQL Server multi-tabbed dialog box. Or, you can take a shortcut by using the SQLVDir component, which lets you automate the entire virtual directory setup process. If you like providing telephone support for hours on end, feel free to skip this information. Most of us, however, would rather write code to automate the creation of virtual directories.

The SQLVDir documentation is clear and concise, so I don't duplicate that information in this article. But to get you started, I explain SQLVDir's various objects and their members and show you how to use Visual Basic (VB) 6.0 to create a virtual directory programmatically.

The SQLVDir Object Model

To use SQLVDir's functionality in your applications, you must set a reference to the Microsoft SQL Virtual Directory Control 1.0 Type Library (sqlvdir.dll) in the References dialog box under VB's Tools menu. This type library contains just a few objects, which Figure 1 shows.

The SQLVDir object model is simple. You can create a new instance of the SQLVDirControl object, which lets you work with the other objects in the object model. Then, you can retrieve a reference to the SQLVDirs collection property. Each member of that collection is an ISQLVDir object that has several properties defining the behavior of a particular virtual directory. One ISQLVDir object property is the VirtualNames collection property. Each member of this collection defines the behavior of one virtual name associated with the virtual directory. (I discuss virtual names later.)

After you set the reference to the SQLVDir type library, you can use the objects that it provides. Using this type library, you can instantiate only one object—the SQLVDirControl object. This object, which lets you interact directly with IIS, provides the members that Table 1 shows. Neither of the object's properties returns anything useful until you've successfully connected to a running installation of IIS by using the Connect method. For example, you might write code like the following to connect to IIS running on your local computer:

Dim svdc AS SQLVDirLib.SQLVDirControlSET svdc = New SQLVDirLib.SQLVDirControlsvdc.Connect

To connect to a specific server and a specific Web site on that server, modify the final line as follows:

svdc.Connect {}, {}]

where servername is the name of the server and sitenumber specifies the Web site you want to connect to on the server. (Because a Web server might host more than one Web site, you can specify by number which site you want to connect to.) If you try to connect to an invalid IIS server name, the Connect method returns an error message that reports the failure to connect.

Working with Virtual Directories

After you connect to IIS, you can use the SQLVDirs property to retrieve a collection containing all the information about the virtual directories installed on the specified server, then start interacting with SQL Server 2000's virtual directories. By retrieving a reference to the SQLVDirs property of the SQLVDirControl object you've created, you can work with any existing virtual directory or create new ones.

The collection object that the SQLVDirs property returns provides the Item and Count members, with Item as the default member. In addition, the collection object provides the AddVirtualDirectory and RemoveVirtualDirectory methods, letting you create and delete virtual directories on your server. Table 2 shows members of the collection object that the SQLVDirs property returns. In SQL Server 2000 Books Online (BOL), you'll find that the SQLVDirs object also provides several other methods: Clone, Next, Reset, and Skip. These methods don't appear in VB's object browser because they're hidden in the type library. And VB developers don't need these methods—you can simply iterate through the collection, just as you would with any collection, by using a For...Each...Next loop. But scripting languages that don't provide this type of loop (e.g., JScript) can move through the collection by using these hidden SQLVDir methods. Listing 1 shows a code example that demonstrates the SQLVDirControl object and its SQLVDirs property.

If you retrieve a reference to a particular virtual directory, you might want to investigate or modify the directory's properties. And if you create a new virtual directory, you must set at least some of its properties so that your new virtual directory can provide the functionality you need.

Table 3 shows the members that SQL Server 2000's ISQLVDir object (which represents a virtual directory and its properties) provides along with the members' associated dialog box options. I've listed the members in the order that their associated options appear in the Virtual Directory Properties dialog box. A property or value for each option is available in the dialog box. Read the information in this table carefully to understand the interactions between values for such properties as SecurityMode, UserName, Password, and EnablePasswordSync. (For additional member settings and other recent updates, you can download XML for Microsoft SQL Server 2000 Web Release 2 beta 1.)

To create a new virtual directory named nwind, you might write code like Listing 2 shows. Your administrator might not let VB applications run on your server. In such a situation, you can use VBScript. Web Listing 1, available online, shows an example of the code in Listing 2 converted to VBScript. (See "Download the Code" link at the top of the page to download listings.)

The nwind virtual directory is associated with the Northwind sample database; the code adds username and password settings and allows only the available types of queries. This code doesn't, however, add virtual names to your new virtual directory, which you'll need in most real applications. Note that this code sets the AllowFlags property to 0 while it's creating the virtual directory so that no one can access the directory until all the settings are complete. (For further details about setting up nwind, see the Microsoft article "Creating the nwind Virtual Directory.")

Adding Virtual Names

If you want your application to be able to use an XML template query or if you want to supply an XML schema that allows XPath queries against the XML data that SQL Server returns, you need to set up virtual names as part of your virtual directory. SQL Server supports three types of virtual names: template, schema, and dbobject. The template virtual name lets you specify the path to a folder associated with templates. Using this virtual name, you can run a template query from a URL by typing a URL like the one below:

http://iisserver/nwind/templatevirtualname/template.xml

This URL specifies the virtual name associated with the template instead of the physical path to the folder. (Note that a virtual name can also specify a template directly—that is, you don't need to append a filename.) The same rules apply to creating virtual names for schemas. You use the dbobject virtual name to instruct SQL Server 2000 to retrieve data directly from the database. (For more information about virtual names and their uses, see "Using IIS Virtual Directory Management for SQL Server Utility" in SQL Server 2000 BOL.)

To add virtual names to your virtual directory, you use members of the collection object that the VirtualNames property of a ISQLVDir object returns. Table 4 shows members of the VirtualNames collection object. Just as the collection returned by the SQLVDirs property does, this collection contains some members that you won't use from within VB. If you turn on the display of hidden members in VB's object browser, you'll find the Clone, Next, Reset, and Skip methods, which you need only if you're using scripting languages that don't support the For...Each...Next loop construct.

The AddVirtualName method returns a VirtualName object, although you'll rarely need to use that object because you've set all its properties in your call to the AddVirtualName method. If, however, you want to iterate through all the virtual names that a virtual directory contains, you might use a VirtualName object to control the loop.

To code the finished procedure that creates the sample nwind virtual directory and its three virtual names, add the following lines to the code at callout A in Listing 2:

With .VirtualNames    .AddVirtualName "Templates", vtTEMPLATE, conPath    .AddVirtualName "Schemas", vtSCHEMA, conPath    .AddVirtualName "Objects", vtDBOBJECT, ""  End With

You can specify the same path for the virtual directory and for its template and schema virtual name (i.e., you don't have to specify separate folders). But you can't specify a path for the vtDBOBJECT virtual name; doing so will trigger a runtime error.

A Simple Object Model

That's all there is to using the SQLVDir object model, whose objects and members give you all the tools you need to automate the management of your SQL Server 2000 virtual directories. Code that handles all these tasks is a great addition to your applications' installations, and the time you spend writing the code will certainly be less than an installation visit to each client or a phone call to each site.