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 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:

SELECT * FROM OracleTest..SCOTT.EMP

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.

SELECT * FROM OPENQUERY(OracleTest, 'SELECT * FROM SCOTT.EMP')

Conclusion

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

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