SQL 不允许远程表值函数调用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16209135/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Remote table-Valued Function Calls are not allowed
提问by anonymous1110
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)
回答by ExceptionLimeCat
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);
回答by Stefan Steiger
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'')
替换DMS_DB.dbo.tfu_V_DMS_Desktop(''de'')'为[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.
在脚注中，问题是 OPENQUERY 不允许变量，因此您不能有变量参数。但是，您可以将所有表和视图作为来自远程服务器的视图进行引用，并且只需在本地以 1:1 的比例创建表值函数。但是，这可能会很慢。
回答by Shahdat
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”
回答by Eralper
Following SQL OpenQuerycommand 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
回答by Rajesh
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 Server 2014 中调用远程 SQL 用户定义函数返回表作为输出的示例。它对我有用。
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
回答by Mr. TA
See this answer:
看到这个答案：
Replace SP call with SELECT ... FROM fn()query and it should work.
用SELECT ... FROM fn()查询替换 SP 调用，它应该可以工作。
