Database Programming: User-Defined Functions And Linked Server Connections

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..

SELECT

  String
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:

CREATE

PROCEDURE dbo.CallTheUDF
AS
SELECT  String
FROM dbo.ExplodeSet ('MEDPSDEV')

.. and then call the stored procedure over the linked server connection:

SELECT

String
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.

     -wp

Comments

• Anonymous
  January 01, 2003
  @Tom Stone: thanks for your comment, Tom.  I'm glad this helped you! Thanks for stopping by..     -wp

• Anonymous
  June 18, 2009
  Thanks. This helped tremendously. I was trying to query sysobjects in a Sybase database using a linked server on a MS SQL Server database ... and got the error "SELECT permission denied on column audflags of object sysobjects" even though the audflags column was not in my select clause.  Creating a Sybase stored procedure with "select name, id from sysobjects ..." did the trick. Thanks again. Tom

• Anonymous
  November 19, 2012
  Check This Link, it worked for me. developersmania.blogspot.com/.../call-user-defined-function-on-linked.html