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 Jan 6, 2004

Linked Severs on MS SQL Part 4, Oracle - Page 2

By Don Schlichting

Create the Linked Server

With the Oracle client created, the linked server can now be created. Start Query Analyzer as SA in master. Execute Add Linked Server stored procedure:

exec sp_addlinkedserver 'OracleTest', 
  'Oracle', 'MSDAORA', 'OracleTest'

The first OracleTest will be the SQL name for the linked server. The second is the name that will be passed to the Oracle client.

Next, we need to create a security mapping.

exec sp_addlinkedsrvlogin 'OracleTest', 
  false, 'sa', 'scott', 'tiger'

Scott is a sample user created when Oracle is first installed. Repeat the proc for additional user mappings. The security examples used in the previous articles apply equally for Oracle linked servers.

Testing the Linked Server

Before we can test the linked server from Query Analyzer, a Schema and table name must be known. In Oracle, tables live in a Schema, not in a database. A Schema is owned by, and named after a user. The Schema and table names can be viewed from inside Enterprise Manager. Navigate to the new linked server, and click the Tables icon.

In this example, the Emp table belongs to the Scott Schema. To select this table from Query Analyzer, run:


The Emp table should be returned.

The same options and performance penalties from the previous articles still apply. Therefore, openQuery is still a good idea.



Linked servers are a convenient way to provide access to various external data sources. The syntax for using them is relatively painless considering the power they offer. The next steps up from linked servers are Distributed Transactions, Two Phase Committing and Partitioned Views. A solid understanding of Linked Servers provides the foundation for all of these advanced topics.

Thanks to all who sent email with questions and suggestions for this series. I did not respond to a couple emails. Unfortunately, those emails became collateral damage during an over zealous spam purge.

» See All Articles by Columnist Don Schlichting

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