Tuesday 18 October 2011

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
 

1 comment: