Take the 2-minute tour ×
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

SELECT *
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. developer.sheikhharis.com/2012/11/… –  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
add comment

1 Answer

up vote 2 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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