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.

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

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.

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

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

C. Executing an INSERT pass-through query

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

D. Executing a DELETE pass-through query

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

Reference

Other Resources

Help and Information