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'

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook