linked server

Linked server is used to execute command against OLEDB datasource on different server.

Advantages :

· Remote server access

· Distibuted query ,update,command and transaction on heterogeneous datasource across the enterprice.

· Ability to address diverse datasource similarity

Linked Server components :

Oledb provider and oledb datasource

Oledb provider-is a dll that manages and interact with specific

Linked server details

When a client appln execute a distributed query through a linked server ,sql server break down into command and send it to rowset and req to oledb. The row set exec and open the table from the provider.

Managing linked server Definition

To work in linked server ,register the connection formula and datasource information to sql server .After registration datasource ll always reffer to the logical name provided.

e.g

EXEC

sp_addlinkedserver @server='74.55.278.251',
@srvproduct='',
@provider='SQLNCLI',
@provstr = 'DRIVER={SQL server }; server=databasename;UID=username;PWD=password;'
@datasrc='74.55.278.251'

by using proc we can add and delete the linked server

sp_addlinkedserver – to add the linked server.

Sp_linkedservers –to see the information in the linked serer.

Sp_addlinkedserver and sp_linkedservers to see more information

The tables affected are

For server details:

select * from sys.servers

For linked server Login Details :

select * from sys.linked_logins

sp_dropserver – used to delete the added server and also we can remove the remote server.

By using sql server enterprise Manager

I am not explaining this.

Identifying a Data Source Using a Linked Server Name

After registered a linked server four part name that help for the transaction.

Part Name Desc

Linked_server_name Linked server referencing the the oleDB data source

Catalog Catalog in oledb datasource Containing object

Schema Schema in the catalog contain the object

Object_name Data object in the schema