OPENQUERY (Transact-SQL)
Executes the specified pass-through query on the specified linked server. This 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 of an INSERT, UPDATE, or DELETE statement. This is subject to the capabilities of the OLE DB provider. Although the query may return multiple result sets, OPENQUERY returns only the first one.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
Syntax
OPENQUERY ( linked_server ,'query' )
Arguments
- linked_server
Is an identifier representing the name of the linked server.
- 'query'
Is the query string executed in the linked server. The maximum length of the string is 8 KB.
Remarks
OPENQUERY does not accept variables for its arguments.
In SQL Server 2000 and later versions, 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
Permissions
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. Executing a SELECT pass-through query
The following example creates a linked server named OracleSvr against an Oracle database by using the Microsoft OLE DB Provider for Oracle. Then, this example uses a pass-through SELECT query against this linked server.
Note
This example assumes that an Oracle database alias called ORCLDB has been created.
EXEC sp_addlinkedserver 'OracleSvr',
'Oracle 7.3',
'MSDAORA',
'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr, 'SELECT name, id FROM joe.titles')
GO
B. Executing an UPDATE pass-through query
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';
C. Executing an INSERT pass-through query
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');
D. Executing a DELETE pass-through query
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''');
See Also
Reference
DELETE (Transact-SQL)
FROM (Transact-SQL)
INSERT (Transact-SQL)
OPENDATASOURCE (Transact-SQL)
OPENROWSET (Transact-SQL)
Rowset Functions (Transact-SQL)
SELECT (Transact-SQL)
sp_addlinkedserver (Transact-SQL)
sp_serveroption (Transact-SQL)
UPDATE (Transact-SQL)
WHERE (Transact-SQL)
Other Resources
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
17 July 2006 |
|
14 April 2006 |
|