This post addresses an edge-case programming issue, but if you’ve ever run into into it, this may be a useful trick to have in your back pocket. This discussion applies to both SQL Server 2000 and SQL Server 2005; in SQL Server 2005, all databases involved must have SET TRUSTWORTHY ON run against them.
A colleague wished to call a user-defined function (UDF) housed on a linked server. SQL Server does not allow cross-server calls to a user-defined function, so this call..
FROM OPENQUERY(ServerX, ‘SELECT String FROM MDPGJan031Issue.dbo.ExplodeSet(”MEDPSDEV”)’)
.. produces an error message. The solution in this case is to place a stored procedure on ServerX that calls the UDF:
FROM dbo.ExplodeSet (‘MEDPSDEV’)
.. and then call the stored procedure over the linked server connection:
FROM OPENQUERY(ServerX, ‘EXEC MDPGJan031Issue.dbo.CallTheUDF’)
This is a relatively simplistic example; you can also put the parameter to the UDF into a memory variable if you wish, which is certainly much more flexible.