title: Workaround for calling table-valued function remotely in SQL Server has even more issues
body: <p>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.</p>
  
  <p>That table valued function needed called from a remote server.  Unfortunately, the call fails on the linked server with the error:</p>
  
  <pre><code>Msg 4122, Level 16, State 1, Line 29
  Remote table-valued function calls are not allowed.
  </code></pre>
  
  <p>Microsoft has acknowledged that "calling a table-valued function remotely" was a feature left out of SQL Server 2008.  See: <a href="http://connect.microsoft.com/SQLServer/feedback/details/276758/remote-table-valued-function-calls-are-not-allowed" rel="noreferrer">http://connect.microsoft.com/SQLServer/feedback/details/276758/remote-table-valued-function-calls-are-not-allowed</a></p>
  
  <p>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: <a href="http://social.msdn.microsoft.com/Forums/en/transactsql/thread/7a6e4aa1-630b-4ad5-aee5-15139987adbd" rel="noreferrer">http://social.msdn.microsoft.com/Forums/en/transactsql/thread/7a6e4aa1-630b-4ad5-aee5-15139987adbd</a></p>
  
  <p>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: <a href="http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/0847ad24-0dfe-4ae1-9788-5516c7830f40/" rel="noreferrer">http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/0847ad24-0dfe-4ae1-9788-5516c7830f40/</a></p>
  
  <p>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:</p>
  
  <p>When the query ultimately calls the table-valued function, I get the error:</p>
  
  <pre><code>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.
  </code></pre>
  
  <p>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.</p>
  
  <p>Any ideas how to solve this?</p>