Sql

MS SQL Linked server

Posted on by amolwable in Sql

What is a Linked Server?

Linked Server is an alias on your local SQL server that points to an external data source. This external data source can be Access, Oracle, Excel or almost any other data system that can be accessed by OLE or ODBC–including other MS SQL servers. An MS SQL linked server is similar to the MS Access feature of creating a “Link Table.”

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.

In below e.g. i have added mssql server as linked server.

EXEC sp_addlinkedserver
@server='MyServer',  --name
@srvproduct='',
@provider='SQLNCLI',
@datasrc='localhostsqlexpress'  -- server name
EXEC sp_addlinkedsrvlogin 'MyServer', false, null, sa, sa  -- last two parameters are the user name and password

for more info visit: http://msdn.microsoft.com/en-us/library/aa213778(SQL.80).aspx

Leave a comment