» » Calling Function / UDF using linked server

Calling Function / UDF using linked server

Edit


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
') 


If one needs to call a UDF or System function Calling Function / UDF using linked server

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.