What is a Linked Server?


Linked Server is used to enable SQL Server Database engine to execute commands against OLE DB data sources (MS SQL, Oracle, MS Access, MS Excel etc.) outside of the instance of SQL Server.

Linked Server provide the following advantages:

  • It provide access to other data sources (MS SQL, Oracle, MS Access, MS Excel etc.)
  • It provide ability to Insert, Update, Delete data in the OLE DB data sources.

Syntax:

sp_addlinkedserver [ @server= ] 'server' 
     [ , [ @srvproduct= ] 'product_name' ] 
     [ , [ @provider= ] 'provider_name' ]
     [ , [ @datasrc= ] 'data_source' ] 
     [ , [ @location= ] 'location' ] 
     [ , [ @provstr= ] 'provider_string' ] 
     [ , [ @catalog= ] 'catalog' ] 


Example:
Suppose I want to connect MSSQL data source from Sql Server.

sp_addlinkedserver [ @server= ] N'Server Name of MSSQL Database' 
     [ , [ @srvproduct= ] N'' ] 
     [ , [ @provider= ] N'MSDASQL' ]
     [ , [ @datasrc= ] N'' ] 
     [ , [ @location= ] N'' ] 
     [ , [ @provstr= ] N'MSSQL Database Connection string' ] 
     [ , [ @catalog= ] N'MSSQL Database Name' ]