Ward Pond's SQL Server blog

Ruminating on issues pertinent to the design and development of sound databases and processes under Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 (while reserving the right to vent about anything else that's on my mind)

Database Programming: User-Defined Functions And Linked Server Connections

Database Programming: User-Defined Functions And Linked Server Connections

  • Comments 3
  • Likes

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

  • @Tom Stone: thanks for your comment, Tom.  I'm glad this helped you!

    Thanks for stopping by..

        -wp

  • Check This Link, it worked for me.

    developersmania.blogspot.com/.../call-user-defined-function-on-linked.html

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment