OPENQUERY (Transact-SQL)OPENQUERY (Transact-SQL)

适用对象: 是SQL Server 是Azure SQL 数据库(仅限托管实例)否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server yesAzure SQL Database (Managed Instance only) noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

在指定的链接服务器上执行指定的传递查询。Executes the specified pass-through query on the specified linked server. 该服务器是 OLE DB 数据源。This server is an OLE DB data source. OPENQUERY 可以在查询的 FROM 子句中引用,就好象它是一个表名。OPENQUERY can be referenced in the FROM clause of a query as if it were a table name. OPENQUERY 也可以作为 INSERT、UPDATE 或 DELETE 语句的目标表进行引用。OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement. 但这要取决于 OLE DB 访问接口的功能。This is subject to the capabilities of the OLE DB provider. 尽管查询可能返回多个结果集,但是 OPENQUERY 只返回第一个。Although the query may return multiple result sets, OPENQUERY returns only the first one.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

OPENQUERY ( linked_server ,'query' )  

参数Arguments

linked_server linked_server
表示链接服务器名称的标识符。Is an identifier representing the name of the linked server.

' query ' ' query '
在链接服务器中执行的查询字符串。Is the query string executed in the linked server. 该字符串的最大长度为 8 KB。The maximum length of the string is 8 KB.

RemarksRemarks

OPENQUERY 不接受其参数的变量。OPENQUERY does not accept variables for its arguments.

OPENQUERY 不能用于对链接服务器执行扩展存储过程。OPENQUERY cannot be used to execute extended stored procedures on a linked server. 但是,通过使用四部分名称,可以在链接服务器上执行扩展存储过程。However, an extended stored procedure can be executed on a linked server by using a four-part name. 例如:For example:

EXEC SeattleSales.master.dbo.xp_msver  

FROM 子句中对 OPENDATASOURCE、OPENQUERY 或 OPENROWSET 的任何调用与对用作更新目标的这些函数的任何调用都是分开独立计算的,即使为两个调用提供的参数相同也是如此。Any call to OPENDATASOURCE, OPENQUERY, or OPENROWSET in the FROM clause is evaluated separately and independently from any call to these functions used as the target of the update, even if identical arguments are supplied to the two calls. 具体而言,应用到上述任一调用的结果的筛选器或联接条件不会影响其他调用的结果。In particular, filter or join conditions applied on the result of one of those calls have no effect on the results of the other.

权限Permissions

任何用户都可以执行 OPENQUERY。Any user can execute OPENQUERY. 用于连接到远程服务器的权限是从为链接服务器定义的设置中获取的。The permissions that are used to connect to the remote server are obtained from the settings defined for the linked server.

示例Examples

A.A. 执行 UPDATE 传递查询Executing an UPDATE pass-through query

以下示例针对示例 A 中创建的链接服务器使用 UPDATE 传递查询。The following example uses a pass-through UPDATE query against the linked server created in example A.

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')   
SET name = 'ADifferentName';  

B.B. 执行 INSERT 传递查询Executing an INSERT pass-through query

以下示例针对示例 A 中创建的链接服务器使用 INSERT 传递查询。The following example uses a pass-through INSERT query against the linked server created in example A.

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')  
VALUES ('NewTitle');  

C.C. 执行 DELETE 传递查询Executing a DELETE pass-through query

以下示例使用 DELETE 传递查询删除示例 C 中插入的行。The following example uses a pass-through DELETE query to delete the row inserted in example C.

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');  

D.D. 执行 SELECT 传递查询Executing a SELECT pass-through query

以下示例使用 SELECT 传递查询选择示例 C 中插入的行。The following example uses a pass-through SELECT query to select the row inserted in example C.

SELECT * FROM OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');  

另请参阅See Also

DELETE (Transact-SQL) DELETE (Transact-SQL)
FROM (Transact-SQL) FROM (Transact-SQL)
INSERT (Transact-SQL) INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL) OPENDATASOURCE (Transact-SQL)
OPENROWSET (Transact-SQL) OPENROWSET (Transact-SQL)
行集函数 (Transact-SQL) Rowset Functions (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL) sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL) sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL) UPDATE (Transact-SQL)
WHERE (Transact-SQL)WHERE (Transact-SQL)