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?

Jard

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

Haydon

The OPENQUERY already causes the function to run locally on the remote server, and it didn't matter that sp_executesql was running on the local server, because the problem was occurring whether I executed OPENQUERY with or without sp_executesql. It actually works fine now; the problem was that the table-valued function linked back to the original server, and I forgot to add the remote user mapping back to the linked server instead of just my username that I would use if I was running a query locally on the remote server against the linked local server. – Jard

Well in any case this syntax seems easier than messing with OPENQUERY. Even though it sounds like you should have a local copy of your table-valued function if half of its job is to link back to the calling server... – Haydon

The reason the query links back is that the remote query has to do a bunch of left joins against a username list on the main server. There's no way to pass this list through to the remote server other than for the remote server to query for it as a linked server. Any other form of the query would be complex or unreliable involving outer joins and coalesced username field values. – Jard

I've had to deal with this situation in a former life. I found it better to mirror a copy of the user metadata to the calling server. This can be feasible and work quite well depending on which technology you choose for replicating the data and how often it changes. – Haydon

Part of the reason I'm linking the servers is to avoid mirroring data. The whole point of linking them is so that a single universal copy of the data can be queried. It's just really unfortunate that SQL Server 2008 doesn't support a simple query against a remote table-valued function. Hopefully SQL Server 2012 will. – Jard

I agree however that the EXEC syntax is clearer and avoids OPENQUERY, although exec seems to require the extra step of storing the results in a temp table since I can't select from or join to the exec statement the way I can to OPENQUERY. The only thing I had to change was the "RPC Out" setting to true on the linked server, otherwise it was giving me an error saying the "Server 'MY_LINKED_SERVER' is not configured for RPC." Thanks! – Jard

Meh, mirroring data isn't necessarily something we all want to avoid. For read workloads this is actually a very effective way to scale, so that all your other servers aren't hammering this one source of data when they could instead be looking at their own, local, cached copy. – Haydon

In some situations, especially for performance, having cached local copies of data makes sense and is important, but in my situation it's inappropriate. Mirroring would add additional complexity, maintenance, and some degree of uncertainty. With my current setup, this query runs only once a month or as-needed and it's running on the main server, pulling some scoring data from another server. It's just not worth the trouble to create a local cache, when the point of linking the servers is to query authoritative data sources directly, without worrying about maintaining local caches. – Jard

Yep, just a suggestion, you don't have to use it but it might be worthwhile to other readers who are bending over backwards using OPENQUERY and linked server queries when they don't need to. – Haydon

Yeah, I think that remote execution of sp_executesql is definitely the way to go, considering I just realized that running it locally with OPENQUERY would still require storing results in a temp table. Executing sp_executesql remotely and avoiding OPENQUERY will basically help you avoid a lot of extra nested quotes and that pesky string-only argument restriction. – Jard

One last point. A problem I encountered with running sp_executesql remotely, is that it tried to promote the query to a distributed transaction. There were problems with that, but I worked around it by disabling the promotion of RPCs to distributed transactions in the linked server settings. – Jard

@AaronBertrand But how to get the results of the query? – Elson

@voroninp Sorry, don't understand the question. – Haydon

@AaronBertrand How to further process results in SQL code which executed 'EXEC ...' (#27528364) Tried exec 'select * into #tempTramle from openquery(...)' but sql complains that #tempTable is unknown. – Elson

@PavelVoronin Sorry, took 7 years to spot this comment. Create your #temp first and then you can say insert #temp EXEC ... – Haydon

Answer 2

In case you can't solve it by calling an EXEC (because you're calling this function from another function or you're trying to do something like INSERT EXEC but you can't nest it, etc.), here is a workarround that solved this problem for me:

You can create a function in your local server that runs the same query as the remote function (asuming you know the implementation of the remote function) since you can access tables and "static-called-functions" (by openquery) with no problems.

In example, if the remote function is something like this:

CREATE FUNCTION dbo.[remote_function] (@param1 varchar(200))
RETURNS TABLE AS RETURN 
( SELECT col1, col2, col3 FROM [remote_db].[dbo].[remote_table] where col1 = @param1)
GO

You can create a function in your local server:

CREATE FUNCTION dbo.[local_function] (@param1 varchar(200))
RETURNS TABLE AS RETURN 
( SELECT col1, col2, col3 FROM [remote_server].[remote_db].[dbo].[remote_table] where col1 = @param1)
GO

And then just query your new function as you want...

SELECT col1, col2, col3 FROM dbo.local_function(@param1);
GO

It should work with no problems.

Ectophyte