Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 7, 2003

Linked Servers on MS SQL Part 2

By Don Schlichting


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.


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:

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:

FROM sysservers

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

MS SQL Archives

Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM