Recent Posts

Posts RSS

Openquery in SQL Server


It helps to execute the specified pass-through query on the specified linked server. This server is an OLE DB data source. Following features can be drawn for OPENQUERY:

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

Syntax to Write:

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.
You can use this OPENQUERY in following ways:
1.    SELECT Statement:
 SELECT * FROM OPENQUERY (SQLSvr, 'SELECT name, id FROM joe.titles')   

2.    UPDATE Statement:
 UPDATE OPENQUERY (SQLSvr, 'SELECT name FROM joe.titles WHERE id = 101')
 SET name = 'ADifferentName';
3.    INSERT Statement:
 VALUES ('NewTitle');
4.    DELETE Statement:
 DELETE OPENQUERY (SQLSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

Hope It Helps!

kick it on