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

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