Tuesday, December 7, 2010

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.


No comments:

Post a Comment