In SQL Server it is called a Linked Server whereas in Oracle it's DBLinks (Database Links).
It’s best in some ways when you are trying to run dynamic complex queries across heterogeneous data source. There are many instances that OpenQuery/OpenRowSet and four-part qualifier calling might not work in the complex SQL design. The limitation of linked server will explode when are trying to manipulate data and write complex queries with heterogeneous data sources.
To execute a Stored Procedure:
The three methods to execute the SP on remote server are
- Calling with four part naming convention
- Using OpenQuery and OpenRowSet
- Execute At LinkedServer
This four-part name should be in the form linked_server_name.catalog.schema.object_name.
Pre-requisites
- Make sure RPC and RPC Out parameters are set to TRUE
- MSDTC is enabled to run distributed queries
Important tables
We can get Linked Server basic information by executing the following stored procedure created in the master database or default system.
exec sp_linkedservers
Or
sys.servers
Add a Linked Server
sp_addlinkedserver
Drop a Linked Server
EXEC sp_dropserver 'LinkServerName'
The use of Openquery, Openrowset and four part calling stored procedure might work in simple cases. When you are working with distributed queries with heterogeneous data source the use of EXECUTE … AT LinkedServer works best.
Reference:
https://www.mssqltips.com/sqlservertip/6083/understanding-sql-server-linked-servers/