OPENQUERY
OPENQUERY is a Transact-SQL statement that executes a specified pass-through query on a remote server. It is used to send command to linked servers and retrieve the result sets back to the calling server. The function allows read and write operations to the specified linked server.
OPENQUERY( linked_server ,‘query’ )
- linked_server: The name of a linked server that is used as the target of a query. This parameter represents the linked server that the OPENQUERY function will access to execute the specified ‘query’. The linked server should be previously set up using the sp_addlinkedserver procedure.
- ‘query’: A pass-through query to the specified linked_server. OPENQUERY sends the entire ‘query’ to the linked server for execution and returns the result set. The ‘query’ is specified as a string, and it should be written in a language that the linked server understands.
Example
SELECT * FROM OPENQUERY(TestLinkServer,'SELECT EmployeeID, FirstName, LastName FROM TestDB.dbo.Employees')Output
| EmployeeID | FirstName | LastName ||------------|-----------|----------|| 1 | John | Doe || 2 | Jane | Doe || 3 | Jim | Smith |Explanation
In the example, OPENQUERY is used to execute the embedded SQL “SELECT EmployeeID, FirstName, LastName FROM TestDB.dbo.Employees” on a linked SQL server named “TestLinkServer”. The result of the query is a list of employees with their IDs and names.