If one needs to call a UDF or System function ( ex: Serverproperty, Getdate(), etc ) on a remote server, then one cannot use the usual 4 part linked server type query.ie., Assume you want to find the Servername, instance name or the current time on a remote sql server then, querying [linked_server].master.dbo.getdate() or [linked_server].master.dbo.Servername('Servername') doesn't work.
In such a case OPENQUERY function comes to our rescue. How does that help? Assume we want to get the Servername,Instance name, Version, Service Pack levels of a remote server. Then use the following query
SELECT *
FROM Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
')
As you can see, the first parameter is the linked server name. The second paramater is the actual query we want to execute.Within the quotation we can run any query and obtain the results. Openquery sends the query to the remote server, executes it in the remote server and presents the result for processing on the local server. Note that this allows one to perform joins / filters with the local table after the results have been returned by the remote server using OpenQuery function. Ex :
SELECT *
FROM Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
')
WHERE remote_server_edition = Serverproperty('Edition')
The above query prints the result if and only if the local and remote server's SQL Editions were the same. On the queries above we have used additional quotations on serverproperty function as we have to specify quotation with in a quotation.
Also,one can also store the result into a temp/physical table or in a variable. Sample query for storing in temp table
INSERT INTO #temp
SELECT *
FROM Openquery([10.13.221.55], 'Select Serverproperty(''Servername'') as srvname,
Serverproperty(''instancename'') as instance_name,
Serverproperty(''productLevel'') as Service_pack,
Serverproperty(''productversion'') as Patch_Version,
Serverproperty(''Edition'') as Remote_Server_Edition
')
Sample query for storing the remote server's date in a variable
DECLARE @dt DATETIME
SELECT @dt = dt
FROM Openquery([10.13.221.55], 'Select getdate() as dt ')
PRINT @dt
So,Openquery provides easy way to access functions esp the system ones like getdate(),db_name(),Serverproperty,DMFs which can't be accessed directly using linked servers.