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

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Nov 7, 2003

Linked Servers on MS SQL Part 2

By Don Schlichting

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




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


















Thanks for your registration, follow us on our social networks to keep up-to-date