Linked server:SQL Server

Linked server:SQL Server 2005 new feature.


A linked server is simply a connection to an Object Linking and Embedding Database (OLEDB) data source.

THE MAIN PURPOSE is to access remote databases from source server instance without login.

Permissions to configure linked server:
SYSADMIN or min setup admin


Syntax:
sp_addlinkedserver server, productname, provider, datasource, location, providerstring, catalog

EXEC sp_addlinkedserver @server = 'EmployeeStats',----linked server name
@provider = 'SQL Server',---instance name
@datasrc = 'Human Resources'--database name

Setting It Up:
1) Go to “Server Objects” of the database server where the linked server will be added and right click on “Linked Servers”.


2) Select “Add New Linked Server”.

3) In the “General” tab, add a name for the new linked server in the “Linked Server” field.


4) Select “Other data source”and for “Provider” select “Microsoft OLE DB for SQL Server”


5) For “Product Name” type in “SQLOLEDB”


6) In the “Data Source” field, enter the IP address of the server to be linked


7) “Catalog” is the name of the database on the linked server and is optional


8) Go to the “Security” tab and select “Be made using this security context”. Type in the remote login and credentials