远程表 - 值函数不允许调用

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

我怎样才能使这个工作？我从远程链接服务器运行一个表值函数。我尝试添加没有锁定这4部分命名，但我仍然得到相同的错误。我正在使用mssql-2008

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

6 个解决方案

#1

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

你需要添加WITH（NOLOCK）。不完全确定为什么，但我今天刚遇到这个问题，这解决了我的问题。

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

#2

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

Nolock不适合我。但是，使用OPENQUERY会...

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

用[110.10.10.100]替换DMS_DB.dbo.tfu_V_DMS_Desktop（''de''）'。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.

在脚注上，问题是OPENQUERY不允许变量，因此您不能拥有变量参数。但是，您可以将所有表和视图作为远程服务器的视图引用，并在本地创建表值函数1：1。然而，这可能会很慢。

#3

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.

还要确保在链接服务器选项中将RPC OUT设置为TRUE。当您尝试在链接服务器上执行存储过程时，也需要此选项。否则您将收到以下错误。

Server 'servername' is not configured for RPC

未为RPC配置服务器“servername”

#4

Following SQL OpenQuery command should be working

以下SQL OpenQuery命令应该正常工作

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

用'包围'的参数值被替换为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

#5

See this answer:

看到这个答案：

Use OPENQUERY

使用OPENQUERY

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

用SELECT ... FROM fn（）查询替换SP调用，它应该工作。

#6

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.

这是调用远程SQL用户定义函数的示例，该函数在SQL Server 2014中将表作为输出返回。它对我有用。

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

#1