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)

6 Answers

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);

7

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.

0

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

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

See this answer:

Use OPENQUERY

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

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

0

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.