How to set up your database for Distributed queries

SQL Server 2005 provides you with the sp_addlinkedserver procedure. This proc creates a linked server to fire distributed queries against OLE DB data sources.

Let us see how to create a linked server against different data sources

Link to SQL Server 2005

EXEC sp_addlinkedserver
@server='FirstLS',
@srvproduct='SQL Server',
@provider='SQLNCLI',
@datasrc='servername\instance'

where @server is the name of the linked server; @srvproduct is the OLEDB Data Source to add (can be kept blank for SQL Server); @provider is the OLE DB provider that corresponds to the data source; @datasrc is the data source

Link to Access

EXEC sp_addlinkedserver
@server = 'SecondLS',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'Access 2003',
@datasrc = 'C:\Data\MyData.mdb

Link to Excel

EXEC sp_addlinkedserver 'ThirdLS', 'Excel', 'Microsoft.Jet.OLEDB.4.0', 'c:\Data\MySheet.xls', NULL, 'Excel 5.0

Link to Oracle

EXEC sp_addlinkedserver
@server = 'FourthLS',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'Server1'