Remote table-Valued Function Calls are not allowed

Question

How can I make this work?Im running a table valued function from a remote linked server. i tried adding no lock to this 4 part naming but still i get the same error. Im using mssql-2008

select * from [110.10.10.100].testdbname.dbo.ufn_getdata('4/25/2013') as tb;(NOLOCK)

Answer 1

You need to add WITH (NOLOCK). Not entirely sure why but I just ran into this issue today and this solved my issue.

SELECT * 
FROM [110.10.10.100].testdbname.dbo.ufn_getdata('4/25/2013') AS tb WITH (NOLOCK);

Answer 2

Nolock doesn't work for me.
However, using OPENQUERY does...

Replace DMS_DB.dbo.tfu_V_DMS_Desktop(''de'')' with [110.10.10.100].testdbname.dbo.ufn_getdata(''4/25/2013'')

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tfu_V_DMS_Desktop]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[tfu_V_DMS_Desktop]
GO



CREATE FUNCTION [dbo].[tfu_V_DMS_Desktop] 
(   
    @param1 int 
)
    RETURNS table 
AS
RETURN 
(
    -- Add the SELECT statement with parameter references here
    -- SELECT 0 as abc
    SELECT * FROM OPENQUERY(CORDB2008R2, 'SELECT * FROM DMS_DB.dbo.tfu_V_DMS_Desktop(''de'')') 
)

GO

On a footnote, the problem is OPENQUERY doesn't allow a variable, so you cannot have variable parameters. You can however reference all tables and views as views from a remote server, and just create the table-valued function 1:1 locally. This will probably be slow, however.

Answer 3

Following SQL OpenQuery command should be working

Parameter values which are surrounded with ' are replaced with double ''

So in this case there is no need to create a stored procedure on the target instance database

SELECT * 
FROM OPENQUERY(
    [110.10.10.100],
    'SELECT * FROM testdbname.dbo.ufn_getdata(''4/25/2013'')'
) as oq