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

適用物件: 是 SQL Server  是 azure SQL 受控執行個體  否 AZURE Synapse Analytics (SQL DW)  否 平行處理資料倉儲APPLIES TO: YesSQL Server YesAzure SQL Managed Instance 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. 您可以依照資料表名稱的相同方式,在查詢的 FROM 子句中參考 OPENQUERY。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_serverlinked_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.

備註Remarks

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)
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)