Sunday, May 6, 2012

Add Linked server using Command


A linked server configuration allows Microsoft® SQL Server™ to execute commands against OLE DB data sources on different servers. Linked servers offer these advantages:

  • Remote server access.
  • The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise.
  • The ability to address diverse data sources similarly

sp_addlinkedserver
Creates a linked server. A linked server allows for access to distributed, heterogeneous queries against OLE DB data sources. After a linked server is created by using sp_addlinkedserver, distributed queries can be run against this server. If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

Command:
EXEC sp_addlinkedserver @server= SERVER NAME

eg, your server name is myDBserver then 
EXEC sp_addlinkedserver @server = 'myDBserver'


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

Command:
EXEC sp_addlinkedsrvlogin  @rmtsrvname , 'TRUE' | 'FALSE' | NULL@locallogin@rmtuser
@rmtpassword 

eg,
EXEC sp_addlinkedsrvlogin 'myDBserver, 'false', NULL, 'sa', 'password'