Click to Rate and Give Feedback
MSDN
MSDN Library
SQL Server
SQL Server 2008
Database Engine
Technical Reference
 OPENQUERY (Transact-SQL)
Community Content
In this section
Statistics Annotations (2)
Collapse All/Expand All Collapse All
Other versions are also available for the following:
SQL Server 2008 Books Online (October 2009)
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.

Topic link icon Transact-SQL Syntax Conventions

OPENQUERY ( linked_server ,'query' )
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.

OPENQUERY does not accept variables for its arguments.

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

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.

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.

ms188427.note(en-us,SQL.100).gifNote:
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''');
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
FILTERS ON OPENQUERY      DAVE_UIO ... Thomas Lee   |   Edit   |   Show History

when we're going to use filters on the openquery select syntax It's important to take into account that filters can not be inside the string parameter cause it causes conflicts, instead you must place parameters outside the openquery function, and it's better practice to use aliases for the return of the openquery, Also you have to take into account that what you are returning on openquery is like a logical table so if you are returning a select from some tables it ist better to name all the result with an alias name.

Ex:
Select QUERY.* from openquery(LinkedServerName, ' select tableA.row2 as R1,tableB.row1 as R2 from schema.tableA inner join schema.tableB on tableA.row1=tableB.row1') as QUERY
where QUERY.R1 = @var1
and QUERY.R2 = @var2

That's not compLex and helps you to understand how to use aliases, and the reasson why is 'cause it's more understandable in the way you know which row and wich table exactlly you're refering to.

Hope it can be useful for community.

David Estrella Maldonado.
Quito - Ecuador

Select with alias is alright, but what about an Update with alias?      MPatAK   |   Edit   |   Show History
The following is not working:

update Query
set Query.col1 = 1
from openquery(LinkedServer, 'select col1, col2 from me.Table where col1 = 2') as Query
, OtherTable as SQLTable
where SQLTable.col2 = Query.col2

You will receive an error that the object 'Query' is unknown.

Seems the alias for openquery in updates is not working!?
Please comment.
Thanx

Processing
© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement | Site Feedback
Page view tracker