2

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?

1 Answer 1

4

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);';
2
  • Can I use Synonyms with say OpenQuery? So rather than searching for every query that calls a TVF on the linked server I create synonyms for each tvf
    – K09
    Oct 6, 2015 at 8:02
  • e.g. 'SELECT * FROM OPENQUERY(linkedServerName, ''SELECT @someValue = dbo.[SomeUDF](' + CAST(@id etc) + ')'')'
    – K09
    Oct 6, 2015 at 8:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.