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.
