Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have one SQL server which contains a link to another server that is on a different box. I would like to create a user-defined-function on ServerA which returns the value of the udf on ServerB, however SQL is telling me that Remote function calls are not allowed within a function.

Is there a way around this?

The udf does have a parameter, so I cannot use OPENQUERY. I thought I had it working by using sp_executesql, however although this works in a blank query window, it doesn't work from a user-defined function.

declare @someValue bit

exec ServerB.MyDatabase.dbo.sp_executesql 
    N'SELECT @someValue = dbo.[SomeUDF](@id)',
    N'@id int, @someValue bit OUTPUT', 
    @id=@id, @someValue=@someValue OUTPUT

select @someValue

The entire function gets called as part of a query, so I don't think I can use a stored procedure

FROM SomeTable
WHERE dbo.SomeUDF(Id) = 1
share|improve this question
Late to this thread, but I'm looking to do something similar. I found this and will try it after I look around some more.… – Rogue Feb 24 '13 at 18:46
Hi, and welcome to DBA.SE. Link-only answers are frowned upon all across the Stack Exchange network - your post loses all its value as soon as that link goes dead. Please sum up your findings in your answer (by editing it) if they work. (In general, please avoid posting solutions you haven't tried.) – Mat Feb 24 '13 at 19:46

1 Answer 1

up vote 3 down vote accepted

You can use OPENQUERY. You just have to use dynamic SQL to build the OPENQUERY call

DECLARE @sql nvarchar(4000);

SET @sql = 'SELECT * FROM OPENQUERY(linkedServerName, 
                          ''SELECT @someValue = dbo.[SomeUDF](' + CAST(@id etc) + ')'')';
EXEC (@sql)

However, you can't build this into a larger query on the local server.
UDFs + linked servers + parameters just don't play nice together...

share|improve this answer
Hrrrm so you're saying there's no way to use a UDF from a linked server as part of a larger query? I figured if anyone knew of a way around that you would. Thank you though. – Rachel Feb 29 '12 at 5:54
@Rachel: AFAIK, no. – gbn Feb 29 '12 at 6:27
@gbn: No you can't, exec requires a stored procedure, not a table-valued function. – Quandary Oct 3 '14 at 9:50
:@Quandary: what do you mean? EXEC (@SQL) execute the string as a query. No UDFs ndded – gbn Oct 7 at 21:19

protected by Community Sep 3 '14 at 4:13

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.