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
» 