The processing method of MSSQL SQL distributed query

SQL Server does not allow ad hoc distributed queries using OPENROWSET and OPENDATASOURCE. This option sets

For 1 o'clock, SQL Server allows for ad hoc access. If this option is not set or set to 0, SQL Server

Ad hoc access is not allowed.

Ad hoc distributed queries connect to remote data using OLE DB using the OPENROWSET and OPENDATASOURCE functions

Source. OPENROWSET and OpenDataSource should only be used when referencing OLE DB data sources that are infrequently accessed. For the

To frequently access data sources, you should define a linked server.

Security Note:
Allowing temporary names means that any authenticated logins to SQL Server can access the provider

。 SQL Server Administrators should enable this feature for any access interface that can be securely accessed by any local logon name. More about the letter

See the DisallowAdhocAccess option in accessing external data.

Example
The following example enables ad hoc distributed queries and then uses the OPENROWSET function to query a server named Seattle1

Copy Code
sp_configure ' show advanced options ', 1;
Reconfigure;
sp_configure ' Ad Hoc distributed Queries ', 1;
Reconfigure;
Go

SELECT a.*
From OPENROWSET (' sqlncli ', ' server=seattle1; Trusted_connection=yes; ',
' SELECT groupname, Name, DepartmentID
From AdventureWorks2008R2.HumanResources.Department
Order by GroupName, Name ') as A;
Go

In Microsoft SQL Server 2000, distributed queries allow SQL Server users access to SQL-based

Server's servers (located on other servers running SQL Server or with OLE DB interfaces)

Other data sources). OLE DB provides a unified way to access list data from heterogeneous data sources.
　　
In this article, a distributed query is any reference to a table or rowset in one or more external OLE DB data sources.

SELECT, INSERT, UPDATE, or DELETE statements.
　　
Remote tables are those that are stored in an OLE DB data source and that are not on the server where SQL Server is executing the query.

Table. A distributed query can access one or more remote tables.
　　
OLE DB Provider Category
Depending on the capabilities of the OLE DB provider in distributed queries in SQL Server, we divide them into the following

Category. By definition, they are not mutually exclusive; a provider may belong to one or more categories:
　　
SQL command Provider
　　
　　
Indexing provider
　　
　　
Simple table Provider
　　
　　
Non-SQL command provider
SQL command Provider
Providers that support Command objects in SQL standard syntax (SQL Server-approved) belong to this class

Don't. The following are the prerequisites for the OLE DB provider to be considered SQL command providers by SQL Server:
　　
The provider must support the Command object and all its mandatory OLE DB interfaces: ICommand, ICommandText

, IColumnsInfo, ICommandProperties and IAccessor.
　　
　　
The SQL syntax supported by the provider must be at least a subset of SQL. The provider must Pass

Dbprop_sqlsupport property to report syntax.
Examples of SQL command providers are: Microsoft OLE DB Provider for SQL Server and

Microsoft OLE DB Provider for ODBC.
　　
Indexing provider
The index provider supports and provides an index corresponding to OLE DB, while also allowing the base table to be checked based on indexing

Find. The following are the prerequisites for an OLE DB provider being treated as an index provider by SQL Server:
　　
Providers must support the IDBSchemaRowset interface with TABLES, COLUMNS, and INDEXES schema rowsets.
　　
　　
The provider must support opening the rowset in the index via IOpenRowset (by specifying the index name and the corresponding basic

Table name).
　　
　　
The Index object must support all its mandatory interfaces: IRowset, IRowsetIndex, IAccessor,

IColumnsInfo, IRowsetInfo and Iconverttypes.
　　
　　
The set of rows opened with the indexed base table (by using IOpenRowset) must support the IRowsetLocate interface.

To locate on the line based on the bookmark.
If an OLE DB provider satisfies the above criteria, the user can set the provider option Index as

Access Path to allow SQL Server to use the provider's index to execute the query. By default, unless the selected

The item has been set or SQL Server will not attempt to use the provider's index.
　　
Note: SQL Server supports multiple options that affect the way SQL Server accesses OLE DB providers

。 You can use the Linked Server Properties dialog box in SQL Server Enterprise Manager to set these options.
　　
Simple table Provider
A simple table provider uses the IOpenRowset interface to represent how a rowset is opened based on a base table. These providers

The order is neither an SQL command provider nor an index provider, but rather a SQL Server distributed query that can handle

The simplest class in the provider.
　　
For this type of provider, SQL Server can only perform table scans while the distributed query is running.
　　
Non-SQL command provider
This class provider supports the Command object and all its mandatory interfaces, but does not support SQL Server-approved SQL

Standard syntax.
　　
Two examples of non-SQL command providers are: Microsoft OLE DB Provider for indexing

Service and Microsoft Windows NT? Active Directory? Service interfaces (ADSI) OLE

DB Provider.
　　
Transact-SQL subset
If the provider supports the required OLE DB interfaces, the following Transact-SQL statement categories can be used to distribute

Type query.
　　
In addition to using a remote table as the SELECT INTO statement of the destination table, all other SELECT statements can use the

。
　　
　　
If the provider supports the interfaces that are required for the insert operation, the INSERT statement can be used for remote tables. About INSERT language

For more information about OLE DB requirements for a sentence, see the INSERT statement later in this article.
　　
　　
If the provider satisfies the requirements of the OLE DB interface on a specific table, the UPDATE and DELETE statements can also be used to

Remote table. For requirements and conditions that the OLE DB interface must meet when you update or delete a remote table, see the following

UPDATE and DELETE statements.
Cursor support
If the provider supports the required OLE DB functionality, the distributed query supports both snapshots and keyset cursors. Distribution

Dynamic cursors are not supported for query-type queries. Dynamic cursors for distributed queries requested by the user are automatically demoted to keyset cursors.
　　
The snapshot cursor is written when the cursor is opened, and the result set remains unchanged; update, insert, and delete the basic table

Do not reflect in the cursor.
　　
Keyset cursors are written when the cursor is opened, and the result set remains unchanged throughout the lifetime of the cursor. However, as

The rows in the base table are updated or deleted, and changes can be seen in the cursor when the rows are accessed. If you plug in the base table

This change is not visible if the in operation may affect the cursor member.
　　
If the provider satisfies the criteria for updating and deleting a remote table, you can use a cursor defined in a distributed query

and a reference to the remote table to update and delete the remote table, for example: Table Update | DELETE < remote table > WHERE

Current of < cursor name >. For more information, see the UPDATE and DELETE statements later in this article.
　　
Requirements to support keyset cursors
If the conditions for all Transact-SQL grammars are met and one of the following two conditions is true, then the distributed

Keyset cursors are supported in queries:
　　
In a query, the OLE DB provider supports reusable bookmarks on all remote tables. Reusable bookmarks can be used from a given table

And is then used on other rowset in the same table. Support for reusable bookmarks is done through the

IDBSchemaRowset TABLES_INFO schema rowset by assigning the bookmark_durability column

Set to bmk_durability_intransaction or some kind of higher durability.
　　
　　
All remote tables list unique keys through the INDEXES rowset of the IDBSchemaRowset interface. should exist

An index entry in which the UNIQUE column is set to VARIANT_TRUE.
A keyset cursor is not supported for distributed queries that contain OpenQuery functions.
　　
Requirements to support updatable keyset cursors
You can update or delete a remote table by using a keyset cursor defined on a distributed query, for example: Update | DELETE

< remote table > WHERE Current of < cursor name >. The following are the allowed use of updatable cursor bars in distributed queries

Thing
　　
An updatable cursor is allowed if the provider also satisfies the criteria for updating and deleting a remote table. Yes

For more information, see the UPDATE and DELETE statements later in this article.
　　
　　
All updatable keyset cursor operations must be in a user-defined transaction that uses an isolation level that is either repeating or higher.

In addition, the provider must support distributed transactions with the ITransactionJoin interface.
OLE DB Provider Interaction phase
There are six operations for all distributed query execution scenarios:
　　
Establishes a connection and retrieves a property operation, specifies how SQL Server connects to the OLE DB provider, and uses the

Which attributes of the provider.
　　
　　
Table name parsing and retrieving metadata operations, specifying SQL Server to use the remote table name (when specifying the two methods

One: resolves to a method of the corresponding data object in the provider based on the name of the linked server or a special name. This also includes

The table metadata that SQL Server retrieves from the provider for compiling and optimizing distributed queries.
　　
　　
A transaction management operation that specifies all interactions related to the transactions of the OLE DB provider.
　　
　　
A data type processing operation that specifies that when SQL Server is executing a distributed query from the OLE DB

The method that SQL Server handles OLE DB data types when the provider obtains or exports data to it.
　　
　　
Error handling operation, which specifies how SQL Server uses extended error information obtained from the provider.
　　
　　
Security operation, which specifies how SQL Server security and provider security interact.
Establishing connections and retrieving properties
SQL Server supports two kinds of remote data object naming protocols using the OPENROWSET feature: Linked server based

Four-paragraph name and special name.
　　
Based on the name of the linked server
　　
A linked server is an abstraction of an OLE DB data source. The name based on the linked server is divided into four segments:< linked servers

>.< Directory >.< schema >.< object, where < link server > is the name of the linked server. SQL Server will < link

Server > is considered the source of OLE DB provider and connection properties (used to identify the data source to the provider). Other three

A name segment is interpreted by an OLE DB data source as the identity of a specific remote table.
　　
Special name
　　
A special name is a name based on the OPENROWSET or OpenDataSource function. It is included in the distributed query

All connection information for each reference to the remote table, including the OLE DB provider used, the genus used to identify the data source

Sex, user ID, and password).
　　
By default, only system administrators are allowed to use special names. To use a special name based on an OLE DB provider

said that the provider's disallowadhocaccess option should be set to 0.
　　
If you use a linked server name, SQL Server extracts the OLE DB provider in the linked server definition

The name and initialization properties of the provider. If you use a special name, SQL Server will be from the parameter of the OPENROWSET function

The same information is extracted from the number.