SQL Server 2012 - Running Dynamic Query Inside of Function (or exec function across linked server)

Question

I have a function in a server which is linked to my current server. This function also exists in the local server, however, to maintain an exact copy, we would have to deploy any changes in two locations (not ideal).

Is it possible to either: 1. Execute a function across a linked server or 2. Execute a dynamic sql query such that the below function can execute correctly?

Create Function [dbo].[Build_HH_Key] (@lastname varchar(60),@address varchar(150), @zip varchar(10))
returns varchar(30)
as
Begin

    Declare @out_var as varchar(30)
    Declare @innerSQL as varchar(1000) = 'select out_var = [MyDatabase].[dbo].[Build_HH_Key]('+@lastname+','+@address+','+@zip+')'
    Declare @sql as varchar(1000) = 'select @out_var = out_var from openquery([MyServer],'' '+ @innersql +' '' )'

    Declare @Return as varchar(30) 
    exec sp_executesql @sql, N'@out_var varchar(30) OUTPUT', @Return = @out_var output;

    Return @Return; 

End

like so? http://stackoverflow.com/questions/4125820/sql-server-how-to-call-a-user-defined-function-udf-on-linked-server — S3S, Aug 10 '16 at 16:40
@scsimon - I looked through this question earlier and checked the guy's link. Comments pointed to a possible version error as he was using SQL Server 2005 and getting success while 2008 or later was getting errors. — rdbradshaw, Aug 10 '16 at 16:42

S3S · Answer 1 · 2016-08-10T21:57:19.380

On your local server where the function exists you can create a view to store the function definition like so:

create view myFunctionDefinition as

SELECT sm.object_id,   
   OBJECT_NAME(sm.object_id) AS object_name,    
   sm.definition 
FROM Server.Database.sys.sql_modules AS sm  
WHERE sm.object_id = OBJECT_ID('dbo.ufn_myTestFN')

Of course you could pull in ALL functions and limit the one you want in the next part.

Then you can create a job on your linked server which brings in the definition (code) of that function, and alters the current version on it's database with what was in the view.

declare @fn varchar max
set @fn = select definition from Server.Database.dbo.myFunctionDefinition
set @fn = replace(@fn,'create','alter')
sp_execute @fn

SQL Server 2012 - Running Dynamic Query Inside of Function (or exec function across linked server)

1 Answers1