Linked Servers on MS SQL Part 2

Introduction

In Part 1 of this series, we reviewed the purpose and reasons for using linked servers, as well as when not to use them. The first linked server created was to an Excel sheet containing data from the Authors table of the SQL Pubs database. We will now move on to other data sources and security.

Access

Northwind , the sample Access database we will be using, can be downloaded at http://www.microsoft.com/downloads/details.aspx?FamilyID=C6661372-8DBE-422B-8676-C632D66C529C&displaylang=en . Save this file into your c:\temp directory and expand it. Convert it to your correct version of Access. For these examples, Access 2000 SR 1 was used. If the file was successfully expanded, the Norhtwind Traders splash will appear when the database is opened.

To create the linked server, open Enterprise Manager and navigate to Security, right click on Linked Servers, and select New Linked Server.

In the Linked Server Properties box, select Microsoft Jet 4.0 OLE DB Provider as the provider name. The product name will be "OLE DB Provider for Jet" and the data source will be "c:\temp\nwind.mdb." Click OK to finish.

A linked server called NWIND should now appear under the Linked Servers heading. Clicking the "Tables" icon will list the table names of the Access database.

To create the link with TSQL, open Query Analyzer, enter and run:

exec sp_addlinkedserver
	@server = 'Nwind2',
	@provider = 'Microsoft.Jet.OLEDB.4.0',
	@srvproduct = 'OLE DB Provider for Jet',
	@datasrc = 'c:\temp\nwind.mdb'

This should return (1 row(s) affected) lines.

To verify that the linked server was successfully created, enter the select:

SELECT *
FROM NWind2...Orders

The select should return all the rows from the Orders table:

To get a quick list of your servers while in the master database, execute:

SELECT *
FROM sysservers

Our two linked servers will appear with a Jet provider name.