Use the IIS virtual Directory Management utility for SQL Server

Source: Internet
Author: User
Tags contains iis microsoft sql server query valid xpath
iis|server| virtual directory using the IIS virtual Directory Management utility for SQL Server
Using HTTP to access Microsoft? SQL Server? 2000 database, you must install the appropriate virtual directory. In running Microsoft Internet Information Services (IIS)

On your computer, use the IIS virtual Directory Management utility for SQL Server (click Configure SQL XML support in IIS in the SQL Server Tools program group) definition

and register the new virtual directory, which is called the virtual root. This utility instructs IIS to create an association between a new virtual directory and a Microsoft SQL Server instance. About this utility

For information about the user interface, see IIS virtual Directory Management utility.

The name of the IIS server and virtual directory must be specified as part of the URL. Information in virtual directories (including logins, passwords, and access rights) is used to establish a connection to a specific database

and execute the query.

You can specify a URL:

Direct access to database objects, such as tables.
In this case, the URL will include the virtual name of the DBObject type.

Executes the template file.
A template is a valid XML document that consists of one or more SQL statements. When you specify a template file in a URL, the SQL commands stored in the template file are executed. Can be directly in the URL

The SQL query is specified in, but is not recommended for security reasons.

Executes an XPath query.
Performs an XPath query on a annotated mapped schema file that specifies as part of the URL.

Virtual name
To make a template file, a mapped schema file, or a database object (such as a table or view) part of a URL, you must create a virtual name for the template, schema, and DBObject types

。 Specifies the virtual name as part of the URL to execute the template file, perform an XPath query on the mapped schema file, or access the database directly.

The virtual name type (template, schema, dbobject) specified in the URL is also used to determine the type of file (template file or mapping schema file) that is specified in the URL. For example

The following URL uses a template to access the SQL Server database:

Http://IISServer/nwind/TemplateVirtualName/Template.xml

Templatevirtualname is the template type of virtual name that identifies the specified file (template.xml) as the template file.

Create a nwind virtual directory
New information-SQL Server SP3.

The following example creates a nwind virtual directory. In most of the instructions for Microsoft? SQL Server? 2000 of the URL access examples, all use the nwind virtual directory.

Before you create a nwind virtual directory, you need a physical directory associated with the virtual directory you will create (for example, C:\Inetpub\Wwwroot\nwind, where Nwind is the

The physical directory associated with the nwind virtual directory that was created in the following procedure.

You also need to create two subdirectories under the physical directory associated with the virtual directory (for example, C:\Inetpub\Wwwroot\nwind\template and

C:\Inetpub\Wwwroot\nwind\schema). These are the directories associated with the virtual names of the template and schema types, both of which are created as Nwind

Created as part of a virtual directory.

Create a nwind virtual directory

In the Microsoft SQL Server program group, click Configure SQL XML support in IIS.


Expand the server, and then click the Web site you want.


On the Action menu, point to the New submenu, and then click Virtual Directory. The property page for the new virtual directory appears on the screen.


On the General tab of the new Virtual Directory Properties dialog box, enter the name of the virtual directory. For this example, type the nwind and physical directory path (for example,

C:\Inetpub\Wwwroot\nwind, assume that the NWIND subdirectory has been created in the C:\Inetpub\Wwwroot directory. Alternatively, you can use the Browse button to select the directory.


On the Security tab, select SQL Server and use Windows integrated authentication.


On the Data Sources tab, enter the name of the server in the SQL Server box, such as (local), or enter SQL Server if more than one instance is installed on the specified computer

2000 the name of the instance (optional). In the Database box, enter the name of Northwind as the default database.


On the Settings tab, select the Allow URL query, allow template query, allow XPath, and allow POST options.


On the Virtual Name tab, click the New button to create a virtual name for the template type.
In the Virtual Name Configuration dialog box:

In the Virtual Name box, enter

Template (can be any name specified by the user). In the Type list, select Templates. Enter the path (for example, C:\Inetpub\Wwwroot\nwind\template, hypothetical and virtual

The physical directory associated with the directory has a subdirectory template, but does not check whether the directory exists. Click the Save button to save the virtual name.
On the Virtual Name tab, click the New button to create a virtual name for the schema type.
In the Virtual Name box, enter

Schema (can be any name specified by the user). In the Type list, select Schema. Enter the path (for example, C:\Inetpub\Wwwroot\nwind\schema, assumption and virtual mesh

There is a subdirectory schema under the physical directory associated with the recording. Click the Save button to save the virtual name.
On the Virtual Name tab, click the New button to create a virtual name for the template and schema type.
Enter DBObject in the Virtual Name box (which can be any user-specified name). In the Type list, select DBObject. Click the Save button to save the virtual name.
Click the OK button to save the settings.
This creates the virtual directory nwind. By default, the specified query is executed against the Northwind database using this virtual directory.

To test the virtual directory, type in the browser:/nwind?sql=select ">http://<iisserver>/nwind?sql=select * from Employees for XML auto& Root=root and press the ENTER key



accessing SQL Server using HTTP
New information-SQL Server SP3.

Can I use HTTP to access Microsoft? SQL Server? 2000. For more information about the URL syntax supported by the SQL ISAPI extension, see URL Access. When you can use

Before HTTP specifies a query, you must first create a virtual root with the IIS virtual Directory Management utility for SQL Server. For more information, see Creating nwind virtual directories.

The HTTP access capabilities of SQL Server allow you to:

Specify the SQL query directly in the URL, for example:
Http://iisserver/nwind?sql=select+*+from+customers+for+xml+auto&root=root

Specifies that the FOR XML clause returns the result in the form of an XML document rather than a standard rowset. The root parameter identifies a single top-level element.

Specify the template directly in the URL.
A template is a valid XML document that contains one or more SQL statements. Templates allow you to put data together to form a valid XML document, but not when you specify a query directly in the URL

It must be so. For example:

Select+*+from+customers+for+xm ' >http://iisserver/nwind?template=<root+xmlns:sql= ' urn: Schemas-microsoft-com:xml-sql "><sql:query>select+*+from+customers+for+xm

L+auto</sql:query></root>

Specify the template file in the URL.
Writing a long SQL query in a URL can be cumbersome. In addition, the browser may have restrictions on the amount of text that can be entered in the URL. To avoid these problems, you can write a template and store it

In the file. A template is a valid XML document that contains one or more SQL statements and XPath queries. Template files can be specified directly in the URL, for example:

Http://IISServer/nwind/TemplateVirtualName/templatefile.xml

In the URL, TEMPLATEVIRTUALN



Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.