Linked Server

1. Adding Linked Server
2.Creating a linked Server Login
3.Execute a T-SQL statement

Some Of the Data Transfer examples using Linkes Server:--

1.) To Transfer Data From SQL Server To MS Access using Linked Server
----------------------------------------------------------------------
SQL Server Table Name: tblTicker
Fields: CompanyID
TickerSymbol
ClassOfShare
Exchange
MS Access Table Name : tblNew

--Adding Linked Server
--Server1 is linked server name
EXEC sp_addlinkedserver
@server='Server1',
@provider = 'Microsoft.jet.oledb.4.0',
@srvproduct='OLEDB provider for jet',
@datasrc='D:ticker.mdb' --path of the mdb

--Getting Permissions
--Here no lginid and no password is assigned
EXEC sp_addlinkedsrvlogin 'Server1','False'
/*You can add linked server in the database you are working but if any problem occurs then execute the above two statements in Master Database*/

--Sending Data From SQL Server table to MS Access table
--Three Dots are for linked srever name.databasename.dbo.tablename
--Select the Database in which table exists
INSERT INTO Server1...New(CompanyID,TickerSymbol,ClassOfShare,Exchange) SELECT * FROM tblTicker

--Using Select Statement to display data
SELECT * FROM Server1...New

2.)To Transfer Data From SQL Server To Excel Sheet
----------------------------------------------------------

--Adding Linked Server
EXEC sp_addlinkedserver
@server='Server2',
@provider='Microsoft.jet.oledb.4.0',
@srvproduct='Jet 4.0',
@datasrc='D:225.xls',
@provstr='Excel 5.0'

--Permissions
--Here no LoginId and Password is assigned
EXEC sp_addlinkedsrvlogin 'Server2','False'

--Sending Data From Sql Server To Excel Sheet(Sheet No 1)
--Keep the Excel Sheet Close
INSERT INTO Server2...sheet1$(CompanyID,TickerSymbol,ClassOfShare,Exchange) SELECT * FROM tblTicker

/*Using Select Statement I have created a range rng in excel sheet and want to display that particular data*/
SELECT * FROM Server2...rng

In place of sheet name you can use cell range also, if you have created.You have to just mention the range name in place of sheet name without $ sign.

3.)To Display Data From A Table On A Remote SQL Server
-------------------------------------------------------------

--Adding Linked Server

EXEC sp_addlinkedserver
@server='Server3', --Name Of The Linked Server
@provider='SQLOLEDB',
@srvproduct='SQL',
@datasrc='sw3' --Name Of the SQL Server on the network
--Permissions
--Here LoginId is sa with no password
EXEC sp_addlinkedsrvlogin 'Server3','False',NULL,'sa',NULL

--Selecting Data From Table latestRatios in the database Stocks using linked server Server3

SELECT * FROM OPENQUERY(Server3,'SELECT * FROM Stocks..latestratios')

--Removing Linked server
sp_dropserver 'Server3', 'droplogins'