Sql-server – Calling TVFs on another Instance

functionslinked-serversql server

We have a database on a SQL Server 2014 instance. We have another on a 2008R2 instance. Both on the same server.

I have created a linked server on the 2008R2 instance to the 2014 one.
However, I am only able to see tables & views.

Is it possible to call stored procedures and functions in the 2014 instance using the linked server?

If not what is my best/simplest solution?

Best Answer

You can call procedures easily:

EXEC LinkedServer.Database.dbo.Procedure;

TVFs will be a different issue:

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

And no, you can't fool SQL Server by creating a synonym so that it doesn't detect a 4-part name; it will just return a different error, e.g.:

Msg 7357, Level 16, State 2
Cannot process the object ""db"."dbo"."synonym"". The OLE DB provider "SQLNCLI11" for linked server "LinkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

To work around this, you can either put the call to the TVF inside a remote procedure and call it like above, or do something like this:

EXEC LinkedServer.Database.sys.sp_executesql N'SELECT ... FROM dbo.TVF();';

If you need more direct interaction (e.g. cross applying the TVF against local tables), you might have to perform that on the remote server using a linked server in the other direction for the table data:

-- instead of
SELECT ... FROM dbo.foo AS t CROSS APPLY LinkedServer.db.dbo.tvf(t.id);

-- you would do:
EXEC LinkedServer.db.sys.sp_executesql N'SELECT ...
  FROM ReverseLinkedServer.db.dbo.foo AS t CROSS APPLY dbo.tvf(t.id);';
Related Question