Create Linked Server From Query in Sql Server

EXEC sp_addlinkedserver
@server='MY_TEMP_linkServer',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='MYDATASOURCE\SQLEXPRESS2005'

EXEC sp_addlinkedsrvlogin @rmtsrvname = 'MY_TEMP_linkServer'
, @useself = 'false'
, @locallogin = 'sa'
, @rmtuser = 'sa'
, @rmtpassword = 'admin123'


Create Linked Server From Query:-

For Link the Two or More Server You need to follow this two steps

A) First Need to create/assign/add server name(Which you can use in program) in linked server list
B) Then set your user id and password to same linked list.

Note :- i) Server Name could be anything which you want to associate in Query.
ii) Need to assign Provider
iii) User names are must be permission to view the table/database.

Syntax :-

EXEC SP_ADDLINKEDSERVER @server=N'SOMESERVER', @srvproduct=N'', @provider=N'SQLOLEDB', @datasrc=N'IP/HOST of server'
GO
EXEC SP_ADDLINKEDSRVLOGIN 'SOMESERVER', 'false', 'remoteuser', 'remoteuser', 'remotepassword'
GO

For Example for Sql Server :-

A)
EXEC sp_addlinkedserver
@server='my_temp_inventory',
@srvproduct='',
@provider='SQLNCLI',
@datasrc='INDIGO49\SQLEXPRESS'

B)
EXEC sp_addlinkedsrvlogin 'my_temp_inventory',
'true', 'my_db_username', 'my_db_username', 'murli'

Another Example :-

EXEC sp_addlinkedserver @server='my_temp_inventory',@srvproduct='',@provider='SQLNCLI',@datasrc='mutli89\SQLEXPRESS2008'
EXEC sp_addlinkedsrvlogin 'my_temp_inventory','true', 'sa', 'sa', 'admin123'

sp_linkedservers --SHOW ALL linked server list

Select * from LINKEDSERVERNAME.DATABASENAME.OWNER.TABLENAME --Execute records

SELECT * FROM OPENQUERY(LINKED_SERVER, ' DROP TABLE DB.dbo.TABLE SELECT NULL') -- drop table

Best Example :-
http://technet.microsoft.com/en-us/library/ms190479.aspx

http://networking.ringofsaturn.com/SQL/linkedservers.php