Execute 'View' residing in a remote server from a stored procedure

Question

How to get the results (pass parameter also) of a view of a remote server from a stored procedure?

The view is in a separate server from the current server where the stored procedures exist.

Thanks.

Answer 1

You could use the Linked Server feature of SQL Server inside your stored procedure:

A linked server configuration allows Microsoft SQL Server to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

• Remote server access

• The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.

• The ability to address diverse data sources similarly.

You would have to write your query inside your stored procedure this way for example:

   SELECT * 
   FROM   MyRemoteServer.MyDB.dbo.MyView
   WHERE  MyViewColumnX = @ParameterY

Answer 2

Create a linked server on your main SQL Server, creating the link to your remote server.

The fact you have a view on the remote server is good, it provides a layer of abstraction to your data. You can specify a login for the linked server that has permission to read that view only. You could also modify the view in the future (add a "where" clause for example to improve performance) with no front end changes required.

I would also add another view on your main server that selects from the view on your remote server: -

create view vwMain as select col1, col2 from RemoteServer.DB.dbo.RemoteView.

That way you can reference vwMain throughout your code (within many stored procedures) without continually referencing the remote server. This way, should your remote server change or you move the data to your main server, it is one simple change to vwMain and all your code continues to work.