在 SQL Server 中远程调用表值函数的解决方法有更多问题 [英] Workaround for calling table-valued function remotely in SQL Server has even more issues

问题描述

我有一个带有一组参数的查询，需要使用不同的参数多次运行，所以我将它包装在一个表值函数中.

I had a query with a set of parameters that needed to be run multiple times with different parameters, so I wrapped it in a table-valued function.

需要从远程服务器调用该表值函数.不幸的是，调用在链接服务器上失败并出现错误:

That table valued function needed called from a remote server. Unfortunately, the call fails on the linked server with the error:

Msg 4122, Level 16, State 1, Line 29
Remote table-valued function calls are not allowed.

Microsoft 已承认远程调用表值函数"是 SQL Server 2008 中遗漏的功能.请参阅:http://connect.microsoft.com/SQLServer/feedback/details/276758/remote-table-valued-function-calls-不允许

Microsoft has acknowledged that "calling a table-valued function remotely" was a feature left out of SQL Server 2008. See: http://connect.microsoft.com/SQLServer/feedback/details/276758/remote-table-valued-function-calls-are-not-allowed

我发现了一种使用 OPENQUERY 语法的解决方法，它允许查询在远程服务器上本地运行，然后返回结果集.请参阅:http://social.msdn.microsoft.com/Forums/en/transactsql/thread/7a6e4aa1-630b-4ad5-aee5-15139987adbd

I discovered a workaround using the OPENQUERY syntax, which allows the query to run locally on the remote server and then return the result set. See: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/7a6e4aa1-630b-4ad5-aee5-15139987adbd

不幸的是，这个变通方法需要一个变通方法，因为它需要一个字符串作为参数，这意味着你不能使用 OPENQUERY 语法传递一个变量，你甚至不能在其中连接一个字符串，就像你想包含要传递给远程表值函数的变量.该变通方法的变通方法是使用动态 SQL 显式构建 OPENQUERY 查询，确保将正常字符串传递给它.请参阅:http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/0847ad24-0dfe-4ae1-9788-5516c7830f40/

Unfortunately, this workaround needed a workaround, because it requires a string as an argument, meaning you can't pass a variable using the OPENQUERY syntax and you can't even concatenate a string in it, like if you want to include variables that you want to pass to the remote table-valued function. The workaround for the workaround is to explicitly build the OPENQUERY query with dynamic SQL, ensuring a normal string gets passed to it. See: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/0847ad24-0dfe-4ae1-9788-5516c7830f40/

然而，由此产生了另一个问题.即使在确保所有引号和双引号和四引号都正确嵌入以便整个事情可以通过 exec sp_executesql 传递之后，仍然存在问题:

Still, another problem results from this. Even after ensuring all the quotes and double-quotes and quadruple quotes are embedded correctly so the whole thing can be passed through exec sp_executesql there is still a problem:

当查询最终调用表值函数时，出现错误:

When the query ultimately calls the table-valued function, I get the error:

OLE DB provider "SQLNCLI10" for linked server "MY_REMOTE_SERVER_NAME" returned message "Deferred prepare could not be completed.".
Msg 7416, Level 16, State 1, Procedure MyTableValuedFunctionName, Line 22
Access to the remote server is denied because no login-mapping exists.

我不知道为什么我会收到这个错误，因为我的用户名存在映射，如果我只是用一个实际的表替换表值函数，它返回的结果很好.问题出现在OPENQUERY语句上，不管它是否用sp_executesql执行，而且正如我所说，它只在调用表值函数时发生.

I'm not sure why I'm getting this error, because the mapping exists for my username, and if I simply replace the table-valued function with an actual table, it returns the results fine. The problem occurs with the OPENQUERY statement, regardless of whether it's executed with sp_executesql or not, and as I said it only occurs when calling a table-valued function.

任何想法如何解决这个问题?

Any ideas how to solve this?

推荐答案

您是否尝试过这种变体 - 基本上您将调用推送到远程框本地发生的函数:

Have you tried this variation - basically you push the call to the function to happen locally on the remote box:

EXEC REMOTE_SERVER_NAME.db_name..sp_executesql N'SELECT * 
  FROM dbo.MyTableValuedFunctionName();';

这篇关于在 SQL Server 中远程调用表值函数的解决方法有更多问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！