38

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)
tbone
  • 5,715
  • 20
  • 87
  • 134
anonymous1110
  • 885
  • 4
  • 14
  • 28

6 Answers6

71

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);
ExceptionLimeCat
  • 6,191
  • 6
  • 44
  • 77
  • 3
    This generates an error for me. It seems to conflict with the answer on http://dba.stackexchange.com/questions/71174/use-nolock-hint-when-calling-table-valued-function. – Evan M Jul 21 '14 at 19:41
  • 6
    This worked for me. Some old code just had **(nolock)** and I was getting an error using it in SSMS. Adding **WITH** fixed my issue. – Hannover Fist Nov 18 '15 at 22:01
  • 1
    This doesn't work for me either returning error "Remote table-valued function calls are not allowed." In which version you have it working? I tried with 2012. – Cetin Basoz Jan 20 '16 at 10:09
  • 1
    @HannoverFist exactly! I had this issue when doing an upgrade. Old queries did `select`s with just `(NOLOCK)` after the table instead of `WITH (NOLOCK)`; apparently older versions of SQL are more forgiving of that omission than newer versions. Good tip @ExceptionLimeCat! – NateJ Nov 16 '16 at 18:14
  • This works for me, too. When you work with Azure DB you have to use WITH – BHuelse Mar 30 '17 at 08:04
  • UNREAL: Changing "(NOLOCK)" to "WITH (NOLOCK)" actually worked. One of the strangest fixes ever. – Speednet Nov 08 '19 at 16:07
  • Changing "(NOLOCK)" to "WITH (NOLOCK)" worked for me as well! Amazing, how did you come up with this!? – user 88 91 Jun 01 '21 at 11:45
11

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.

Stefan Steiger
  • 78,642
  • 66
  • 377
  • 442
5

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
Eralper
  • 6,461
  • 2
  • 21
  • 27
4

Also make sure that RPC OUT is set to TRUE in linked server options. This option also needed when you try to execute stored procedure on linked server. Otherwise you will get following error.

Server 'servername' is not configured for RPC

enter image description here

Shahdat
  • 5,343
  • 4
  • 37
  • 37
2

See this answer:

Use OPENQUERY

Replace SP call with SELECT ... FROM fn() query and it should work.

Community
  • 1
  • 1
Mr. TA
  • 5,230
  • 1
  • 28
  • 35
2

This is the example of calling a remote SQL user defined function returning a table as output in SQL Server 2014. It is working for me.

Declare @Param1 Varchar(10)
Declare @SqlText nvarchar(4000)
Set  @Param1 = 'xxx'
Set @SqlText = 'SELECT * FROM Database.dbo.ufn_Function (''' + @Param1 + ''')'
EXEC LinkedServer.Database.dbo.sp_executesql @SqlText
Rajesh
  • 21
  • 1