Can I execute a user defined function in a linked server

I have two sql servers, named mainserver and linkedserver
I want to do a

select *
from linkedserver.database.dbo.table
where field = 'something'

on the mainserver and I noticed that this is quite a performance hit, because where clause of the query is apparantly executed on the mainserver.

So I considered creating a function on the linkedserver:

CREATE FUNCTION func(@sWhere char())
RETURNS TABLE AS
RETURN (
SELECT *
FROM database.dbo.table
WHERE field = @sWhere
)

I can execute this function on linkedserver, but not on mainserver, it gives me:

SELECT * FROM linkedserver.database.dbo.func('abcde')

Server: Msg 170, Level 15, State 31, Line 2
Line 3: Incorrect syntax near '('.

Any ideas?

thanks,

Mark
LVL 1
tetrodeAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
The linked server does not support executing function and stored procedures.

Now, as far as i know, you might try to use the OPENQUERY and/or OPENROWSET functions, which should be able to pass the query completely to the "linkedserver"...
CHeers
0
 
tetrodeAuthor Commented:
Excellent. You not only answered fast, but understood my problem and provided a solution that works!

0
All Courses

From novice to tech pro — start learning today.