How to create a linked server, what’s it used for?

Problem: How to create a linked server, what’s it used for?

Solution:  Create Linked Servers

Linked Server creates a connection between two SQL Server Instances which can be helpful to use cross server database queries.

A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created, distributed queries can be run against this server, and queries can join tables from more than one data source. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Usage scenarios:

      1.       It will be helpful to extract data or execute a procedure from one server to another server using four part naming convention
2.       In cases where you want to load data from one server to another server.

Syntax:

sp_addlinkedserver [ @server= ] 'server' [ , [ @srvproduct= ] 'product_name' ]  

     [ , [ @provider= ] 'provider_name' ] 

     [ , [ @datasrc= ] 'data_source' ]  

     [ , [ @location= ] 'location' ]  

     [ , [ @provstr= ] 'provider_string' ]  

     [ , [ @catalog= ] 'catalog' ]   

Example:

In this example, I would like to show we can add, test and drop a linked server.

-- Add the linked servers

EXEC sp_addlinkedserver  

   @server='TROPPLTMSAMBU_LinkedServer',

   @srvproduct='',

   @provider='SQLNCLI',

   @datasrc='TRODVDTMSAMBU';-- target servers

-- Test the linked servers

EXEC sp_linkedservers 

-- query servers tables to get the linked servers

select * from sys.servers where is_linked=1

-- Drops the linked server

EXEC sp_dropserver 'TROPPLTMSAMBU_LinkedServer', 'droplogins'; 

-- query from the other server using four part naming convention

SELECT *FROM [TROPPLTMSAMBU_LinkedServer].[Test].[dbo].[TableA]