在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所不具备的功能.请参阅:

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/zh-CN/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.

我不确定为什么会出现此错误，因为我的用户名存在映射，并且如果我只是用实际表替换表值函数，它将返回正确的结果.不管是否用sp_executesql执行，OPENQUERY语句都会出现问题，正如我所说的，仅在调用表值函数时才会发生.

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屋！