Using OPENQUERY in SQL Server

OPENQUERY is a function falling under the Rowset preferences. This function can be used to fetch the records or information from one server to another by specifying a linked server and desired commands.

The resemblance of using OPENQUERY is as though the records are being fetched directly from the table using the FROM clause.

Format:

SELECT * FROM OPENQUERY (

[linkedserver_name],

'your query'

)

From the format, we can notice that the OPENQUERY allows two arguments to be passed. One for passing the linked server and the other to fill in your desired query.

Example:

USE SQLArena_TEST

SELECT * FROM OPENQUERY (

[link_test],'

SELECT COUNT(1) AS COUNT from ArenaLink_Testing..PT')

From the example, we can notice that the current database from where I have been accessing is SQLArena_TEST. Additionally, by filling in the arguments with the linked server name (link_test) and my SELECT query, we can fetch the results of the corresponding server data.

The OPENQUERY is an alternate for using linked servers in the query directly. Sometimes, we can come across an error as “NT AUTHORITY\ANONYMOUS LOGON” error. If you come across this error while accessing the query, then make sure the outbound configuration and Kerberos settings are set up correctly.

Additionally, we can as well fetch the server information using a global function that cannot be achieved by passing directly the linked server name. Getting remote server configuration details can be achieved only using the OPENQUERY in SQL Server.

Further, OPENQUERY as well supports pass-through execution commands on INSERT, UPDATE and DELETE operations.