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