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.
I appreciate the insightful article! I'd also suggest considering the article (https://blog.devart.com/openquery-in-sql-server.html) for those keen on delving further into this subject.
ReplyDelete