Create Linked Server in Sql Server 2005

   Tweet

A linked server allows for access database that are placed across different servers.

If the linked server is defined as an instance of SQL Server, remote stored procedures can be executed.

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

Valid provider_name for various databases

SQL Server - SQLNCLI

Oracle - MSDAORA

Access/Jet - Microsoft.Jet.OLEDB.4.0

ODBC data source - MSDASQL

Sample query to create linked server in sql server 2005 for

Database name=dbname

Data Source=servername

User ID=dbuser

password=dbuserpwd

sp_addlinkedserver 'lnkservername','lnkprdname','SQLNCLI','servername,1433',NULL,NULL,NULL

sp_addlinkedsrvlogin 'lnkservername','false',NULL,'dbuser','dbuserpwd'

Query to drop an existing linked server

sp_dropserver 'lnkservername','droplogins'

   Tweet