SQL uses OpenQuery for cross-Library operations

Excerpt from: http://www.cnblogs.com/aji88/archive/2009/11/06/1597263.html

Executes the specified pass-through query on the given linked server. The server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY can also be referenced as the target table for an INSERT, UPDATE, or DELETE statement. However, this depends on the functionality of the OLE DB access interface. Although the query may return more than one result set, OPENQUERY returns only the first one.

Transact-SQL Syntax conventions

Grammar

(, 'query')

Parameters

HTTP://DDUE.SCHEMAS.MICROSOFT.COM/AUTHORING/2003/5 NS = "HTTP://DDUE.SCHEMAS.MICROSOFT.COM/AUTHORING/2003/5"

Linked_server

Represents the identifier for the linked server name.

'  Query '

The query string executed in the linked server. The maximum length of the string is 8 KB.

Note

OPENQUERY A variable that does not accept its arguments.

In SQL Server 2000 and later versions, OPENQUERY cannot be used to perform extended stored procedures on linked servers. However, you can execute an extended stored procedure on a linked server by using the four-part name. For example:

EXEC SeattleSales.master.dbo.xp_msver

Permissions

Any user can execute OPENQUERY. The permissions used to connect to the remote server are obtained from the settings defined for the linked server.

Example A. Executing a SELECT pass-through query

The following example uses the Microsoft provider for Oracle to create a linked server named for the Oracle database OracleSvr . The example then uses the pass-through query for this linked server SELECT .

Attention:

This example assumes ORCLDB that an Oracle database alias named is already created.

' Oraclesvr ',    ' Oracle 7.3 ',    ' Msdaora ',    ' orcldb 'goSELECT *go   

B. Executing an UPDATE pass-through query

The following example uses a pass-through query for the linked server created in example A UPDATE .

' Adifferentname ';

C. Performing an INSERT pass-through query

The following example uses a pass-through query for the linked server created in example A INSERT .

' SELECT name from Joe.titles ')VALUES (' newtitle '); 

D. Executing a DELETE pass-through query

The following example uses DELETE a pass-through query to delete rows inserted in example C.

' SELECT name from joe.titles WHERE name = 'newtitle '); 

SQL uses OpenQuery for cross-Library operations