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
