How To Add Linked Server Between two Machines in LAN having MSSQL server

 Adding Linked Server is desirable knowledge one can have,it is useful in running Distributed partitioned views. Here ‘Shrinath’ is remote machine in LAN it holds server instance named ‘Shrinath’,it contain a user called ‘testuser’ having password  ‘testpwd’.

--Remove If linked server already added

EXEC sp_dropserver 'SHRINATH', 'droplogins';

--Adding Linked Server

sp_addlinkedserver

  @srvproduct = '',

  @server ='SHRINATH',

  @provider = 'SQLOLEDB',

  @datasrc = 'SHRINATH',

  @provstr = 'SERVER=SHRINATH;uid=testuser;pwd=testpwd

Explination:

Here is how parameters have got values

@srvproduct-for remote sql server it’s value is ‘’ 

for other system like 

            oracle it is ‘Oracle’ & 

for IBM DB2 it is 

            'Microsoft OLE DB

            Provider for DB2' etc.

@server- is remote machin name

@datasrc –is name remote sql server instance name

@provstr -  is a connection string

Remote sqlserver contain database ‘Epic’ 

which in turn have table ‘Product’.

‘Product’ Table has some records.

  We will query remote sql server to test linked server creation 
succeeded or not.

--try running query that pulls data from linked server.

SELECT * FROM OPENQUERY(SHRINATH,'select * from Epic.dbo.Product')

Output will be as follows.