Understanding about LINKED SERVER In SQL

Linked Server in nothing but combining two different Server database together and perform a single query or joining table objects from different database of different server.

For Example:

You have Server-A with Database-A and Server-B with Database-B in different geographical locations. Now you want to make a SQL statement by JOINING Table-A from Server-A / Database-A and Table-B from Server-B/Database-B.  to do this you need to configure linked server configurations.

Configuring Linked Server.

Syntax

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

     [ , [ @provider= ] 'provider_name' ]

     [ , [ @datasrc= ] 'data_source' ]

     [ , [ @location= ] 'location' ]

     [ , [ @provstr= ] 'provider_string' ]

     [ , [ @catalog= ] 'catalog' ]

Suppose You are currently logged in at SERVER-A

USE master

GO

EXEC sp_addlinkedserver  

   @server=N'S1_instance1', -- Linked Server Name

   @srvproduct=N'',

   @provider=N'SQLNCLI',    -- Provider Name

   @datasrc=N'Server-B';    -- SQL Server Instance Name

Provider Details:

SQL Server	Microsoft SQL Server Native Client OLE DB Provider	SQLNCLI
Oracle	Microsoft OLE DB Provider for Oracle	MSDAORA

It can connect any server supporting Microsoft OLEDB Provider.

Linked Server Login

 sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname' 

     [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ] 

     [ , [ @locallogin = ] 'locallogin' ] 

     [ , [ @rmtuser = ] 'rmtuser' ] 

     [ , [ @rmtpassword = ] 'rmtpassword' ] 

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server

EXEC  sp_addlinkedsrvlogin

       @rmtsrvname = 'Server-B',

       @useself = 'TRUE',

       @locallogin = 'Domain\Mary',

       @rmtuser =  'mysqllog',

       @rmtpassword = 'rmtpassword' 

  

To Drop linked Server Login

sp_droplinkedsrvlogin [ @rmtsrvname= ] 'rmtsrvname' , 

                      [ @locallogin= ] 'locallogin'

 Execute SQL

SELECT a.Roll, a.StudentName

  FROM Server-B.Dtabase-B.dbo.Table-B

Posted by Mr. Joydeep Das