tetrode
asked on
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('abcd e')
Server: Msg 170, Level 15, State 31, Line 2
Line 3: Incorrect syntax near '('.
Any ideas?
thanks,
Mark
I want to do a
select *
from linkedserver.database.dbo.
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.
Server: Msg 170, Level 15, State 31, Line 2
Line 3: Incorrect syntax near '('.
Any ideas?
thanks,
Mark
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER