Use IIS virtual directory management utility for SQL Server

zhaozj2021-02-16  106

The appropriate virtual directory must be installed before using the IIS virtual directory management utility used for SQL Server to access the Microsoft® SQL Server® 2000 database using HTTP. Running Microsoft Internet Information Services (IIS)

On the computer, use the IIS virtual directory management utility for SQL Server (click "Configuring SQL XML Support in IIS" in IIS in IIS in IIS.

And register a new virtual directory, that is, the so-called virtual root. This utility indicates that IIS creates associations between new virtual directories and Microsoft SQL Server instances. About this utility

For information on the user interface, see the IIS Virtual Directory Management utility.

The name of the IIS server and virtual directory must be specified as part of the URL. The information in the virtual directory (including login, password, and access) is used to establish a connection to a specific database.

And execute the query.

You can specify the URL:

Access database objects directly, such as a table. In this case, the URL will include a virtual name of the DBObject type.

Execute a template file. The template is a valid XML document consisting of one or more SQL statements. When specifying a template file in the URL, the SQL command stored in the template file is executed. Can be directly in URL

Specify SQL queries in the middle, but considering security, it is not recommended.

Execute an XPath query. Perform XPath queries for mapped map architecture files that are part of a part of the URL.

Virtual Name To make template files, mapping architectures, or database objects (such as tables or views) become part of the URL, you must create a virtual name for Template, Schema, and DbObject types.

. Specify a virtual name as part of the URL to perform a template file, perform XPath queries or directly access the database.

The virtual name type (Template, Schema, DBObject) specified in the URL is also used to determine the file type specified in the URL (template file or mapping schema file). E.g,

The following URL uses templates to access SQL Server databases:

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

TemplateVirtualName is a virtual name for the template type that identifies the specified file (Template.xml) is a template file.

Create new information in the NWIND virtual directory - SQL Server 2000 SP3.

The next example creates a NWIND virtual directory. In the example used to explain the URL access to Microsoft? SQL Server? 2000, use the NWIND virtual directory.

Before creating a NWIND virtual directory, you need a physical directory associated with the virtual directory to be created (for example, c: / inetpub / wwwroot / nwind, here nwind is to

The physical directory associated with the NWIND virtual directory created in the following procedure).

You also need to create two subdirectories in a physical directory associated with the virtual directory (for example, C: / INETPUB / WWWROOT / NWIND / TEMPLATE and

C: / inetpub / wwwroot / nwind / schema). These are directorys associated with the Template and Schema type virtual names, which are created nwind

Create a part of the 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 click the desired Web site.

Point to the "New" submenu on the "Operation" menu and click the "Virtual Directory" command. The properties page of the new virtual directory will be displayed on the screen.

Enter the name of the virtual directory on the General tab of the New Virtual Directory Properties dialog. For this example, type NWIND and physical directory paths (for example,

C: / inetpub / wwwroot / nwind, assuming that the NWIND subdirectory has been created in the C: / Inetpub / wwwroot directory). Or you can use the Browse button to select the directory.

On the Security tab, select "SQL Server" and "Using Windows Integration Authentication".

On the Data Source tab, enter the name of the server in the "SQL Server" box, for example, (local); if multiple instances are installed on the specified computer, enter SQL Server

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

On the Settings tab, select 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:

Enter in the Virtual Name box

Template (can be any name specified by the user). In the "Type" list, select "Template". Enter path (for example, c: / inetpub / wwwroot / nwind / template, assumption and virtual

There is a subdirectory Template in the physical directory associated with the directory, but does not check if 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 of the schema type. Enter in the Virtual Name box

Schema (can be any name specified by the user). In the "Type" list, select "Architecture". Enter path (for example, c: / inetpub / wwwroot / nwind / schema, assumptions and virtual eyes

There is a subdirectory Schema in the physical directory of the record. Click the "Save" button to save the virtual name. On the Virtual Name tab, click the New button to create a virtual name for templates and schema types. Enter dbObject in the Virtual Name box (any name specified by the user). 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 a virtual directory NWIND. By default, the specified query is performed using the virtual directory to the Northwind database.

To test a virtual directory, type: / nwind? Sql = select "> http: // / nwind? Sql = select * from Employees for xml auto & root = root and press Enter.

Use HTTP to access SQL Server Add Information - SQL Server 2000 SP3.

You can use HTTP to access Microsoft? SQL Server? 2000. For more information on the URL syntax supported by SQL ISAPI extensions, see URL Access. Can be used

HTTP specifies that before the query must be created with a virtual root using the IIS virtual directory management utility used for SQL Server. For more information, see Creating a NWIND Virtual Directory.

SQL Server HTTP access capabilities make you:

Specify SQL queries 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 element element.

Template directly in the URL. The template is a valid XML document containing one or more SQL statements. The template allows you to put the data together to form a valid XML document, but do not directly specify the query in the URL

It must be the case. E.g:

SELECT * FROM CUSTOMERS FOR XM '> http: // Iisserver / nwind? Template = SELECT * From Customers for XM

L auto

Specify a template file in the URL. Write a long SQL query in the URL. 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. The template is a valid XML document containing one or more SQL statements and XPath queries. You can specify a template file directly in the URL, for example:

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

In the URL, TemplateVirtualName is a virtual name for the Template type created using the IIS virtual directory management utility used for SQL Server.

The template file also deletes details from the user's database query to enhance security. By storing template files in the virtual root directory (or their subdirectory) where the registration database is located,

Deleting URL query processing services on virtual roots and only allows SQL Server XML ISAPI to process files and return result sets, thereby enhancing security.

Specifies the XPath query executed on the XML data simplification (XDR) architecture (also known as a mapping architecture) with annotation. Conceptually, write XPath queries to map architecture and create a view using the CREATE VIEW statement and write SQL query on the view, for example:

http://iisserver/nwind/schemavirtualname/schemafile.xml/customer [@ Customerid = "Alfki"] In this URL:

SchemaVirtualName is a virtual name for the Schema type created using the IIS virtual directory management utility for SQL Server.

Customer [@ Customerid = "ALFKI"] is an XPath query executed on schemafile.xml specified in this URL. Specify database objects directly in the URL. You can specify a database object (such as a table and view) as part of the URL and specify the XPath query for the database object, for example:

http: // Iisserver / NWIND / DBOBJECTVIRTUALNAME / XPATHQUERY

In this URL, DBOBJECTVIRTUALNAME is a virtual name for DBObject types created with IIS virtual directory management utilities for SQL Server.

Note When performing an operation requiring resources (such as memory) in the URL (create a temporary table and temporary stored procedure, declared cursor, execution sp_xml_prepaaredocument, etc.)

Perform appropriate commands such as Drop Table, Drop Procedure, Deallocate cursors, or Execute SP_XML_REMOVEDOCUMENT to release resources.

XML Documents and Document Sections When performing templates or queries with root parameters, the result is a complete XML document with a single top element. For example, the following URL performs template:

http://iisserver/virtualroot/templatevirutalname/mytemplate.xml

Below is an example template file (MyTemplate.xml):

SELECT * from Customers for XML Auto

marked in the template provides a single top element with the XML document.

You can specify a query directly in the URL. In this case, the root parameter specifies the top layer element in the document:

http: // Iisserver / VirtualRoot? SQL = Select * from customers for xml auto & root = root

If the root parameter is not used when writing the above query, an XML document clip will be returned (i.e., an XML document that is missing a single top element). This snippet has no title information. For example, the following

The URL will return to the document segment:

http: // Iisserver / VirtualRoot? SQL = Select * from customers for xml auto

When requesting an XML document, the byte order tag for identifying the document encoding type will be returned. The byte order tag is a standard byte order to identify the XML document encoding type. Xml

The analyzer uses this byte order to determine the document encoding type (such as Unicode). For example, byte order marking "oxff, 0xfe" identifies the document as Unicode. Analysis by default

Assume that UTF-8 is a document encoding type.

The byte order tag is not returned when the XML fragment is requested, as the byte order is marked belongs to the XML document title, and there is no title in the XML segment. Safety Note The Microsoft SQLXML Web release enhances support for this XML function. To use the latest enhancements, install Microsoft from the Microsoft Web site

SQLXML 3.0 Service Pack 1. The documentation included with this Service Pack provides information on security matters that should be considered when developing applications.

转载请注明原文地址:https://www.9cbs.com/read-10706.html

New Post(0)