Workaround for calling table-valued function remotely in SQL Server has even more issues

Question

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

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

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/

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.

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?

Answer 1

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