OPENQUERY (Transact-SQL)

Applies to: SQL Server (all supported versions)

Executes a query against an Open Data Services (ODS) server.

Syntax

OPENQUERY ( { <linked_server_name> | @@PROCID } , '<query>' )

Arguments

<linked_server_name>

Is the name of the linked server to query. Linked servers are defined using sp_addlinkedserver.

@@PROCID

Is the system function that returns the object ID of the current stored procedure or trigger.

'<query>'

Is a string containing the query to be executed on the linked server. The query is typically a SQL statement.

Permissions

Requires ALTER ANY LINKED SERVER permission on the server, or the db_owner or db_ddladmin fixed database roles.

Examples

A. Executing a SELECT statement on a linked server.

SELECT *
FROM OPENQUERY (MyLinkedServer, 'SELECT * FROM Production.Product');

B. Executing an INSERT statement on a linked server.

INSERT INTO OPENQUERY (MyLinkedServer, 'SELECT * FROM HumanResources.Employee')
VALUES (1, 'John', 'Doe', '1980-05-15');

C. Executing a stored procedure on a linked server.

EXECUTE
   (N'EXECUTE Sales.usp_MyStoredProcedure @p1 = 10, @p2 = ''SomeValue''')
   AT MyLinkedServer;

Notes

See Also

• sp_addlinkedserver (Transact-SQL)
• sp_serveroption (Transact-SQL)
• Linked Servers (Database Engine)
• Four-Part Names