When you're creating linked servers to non-Microsoft databases, check the PATH environment variable. Note that Oracle incorrectly appears before SQL Server in the PATH variable that Figure 4 shows. To prevent the error message I received, edit PATH as Figure 5 shows, placing SQL Server ahead of any other vendor you're linking to. After I corrected PATH, the linked queries worked correctly.
Configuring SQL Server
Creating a linked Oracle server on SQL Server 2000 imposes an additional configuration requirement: You must manually add a key to the client machine's registry. (In this case, your SQL Server is the client of the Oracle server.) You need to choose the registry keys for the appropriate version of Windows and your Oracle server. You can find the appropriate registry files, which begin with mtx, in the C:\program files\common files\system\ole db folder. Double-click the appropriate registry file to add the necessary key. For more information, see BOL; go to Accessing and Changing Relational Data, Distributed Queries, OLE DB Providers Tested with SQL Server, OLE DB Provider for Oracle, then look under Registry Entries.
You create linked servers by executing a series of two system stored procedures. To begin, you use sp_addlinkedserver to specify your choice of OLE DB provider. The following example specifies Microsoft Data Access for Oracle:
sp_addlinkedserver 'OraDB', 'Oracle',
'MSDAORA', 'Ora817'
Or you can specify Oracle's OLE DB provider:
sp_addlinkedserver 'OraDB', 'Oracle',
'OraOLEDB.Oracle', 'Ora817'
You must pick one or the other of these options. Although Microsoft officially supports and recommends only the Microsoft OLE DB Provider for Oracle, this provider has limitations that I show later in an INSERT example. If you experience problems when you're using Microsoft's provider, try using Oracle's provider before calling Microsoft technical support.
The next step is to use sp_addlinkedsrvlogin to create a login to the linked server. SQL Server is like any other Oracle client in that it must supply a username and password to connect to the Oracle server. You can provide the username and password to sp_addlinkedsrvlogin in several different ways. In the first example that follows, I had already created a SQL Server login of sys with a password of change_on_install to let SQL Server impersonate an Oracle username and password by supplying a SQL Server username and password to Oracle. By default, the Oracle user sys has a password of change_on_install. Because the usernames and passwords for the two database servers match, the SQL Server credentials can impersonate the Oracle credentials. No mapping of SQL Server credentials is required in this case, so I used true as the value of the second parameter, indicating that impersonation of credentials will take place. In the second example, the Win2K user Administrator is mapped to the Oracle user scott with a password of tiger. The last example directs all other SQL Server logins to connect as scott with a password of tiger. The latter two examples pass specific credentials; because they don't use impersonation, the second parameter is false.
sp_addlinkedsrvlogin 'OraDB', true, 'sys'
sp_addlinkedsrvlogin 'OraDB', false,
'W2000AS\Administrator', 'scott', 'tiger'
sp_addlinkedsrvlogin 'OraDB', false, NULL, 'scott',
'tiger'
Figure 6, page 28, shows the security settings on the Linked Server Properties dialog box's Security tab. You can access this dialog box from Enterprise Manager by opening Linked Servers in the Security folder.
Executing an Oracle Query from SQL Server
You can query the SCOTT schema by using Oracle's SQL*Plus or a third-party freeware tool such as Quest Software's TOAD, which Figure 7, page 28, shows. (TOAD is available from http://www.toadsoft.com.) Oracle tools are crude by Microsoft standards: SQL*Plus has functionality similar to the osql command-line tool. TOAD, however, is comparable to SQL Server 2000 Query Analyzer and Object Browser. To start SQL*Plus, choose Start, Programs, Oracle, Application Development, SQL*Plus. In the Log On dialog box that Figure 8, page 28, shows, use tiger as the password. Note that all entries are case insensitive. At the SQL> prompt, enter the following query:
SELECT * FROM dept WHERE deptno = 10;
You'll get the following output:
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
Now, try executing the above SELECT statement as a SQL Server linked-server query from Query Analyzer. Although this query requires a fully qualified four-part name for resolution, the catalog is always left empty because all Oracle metadata resides in one centralized system catalog called SYSTEM.
SELECT * FROM oradb..scott.dept WHERE deptno = 10
Executing the above query returns the following error:
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'oradb' does not contain table '"scott"."dept"'.
The table either does not exist or the current user does not have
permissions on that table.
You get this error because the Oracle data dictionary is case sensitive. Oracle object names are all uppercase by default. The linked-server query must exactly match the case of all referenced Oracle metadata, such as schema and table names. So, the following query works:
SELECT * FROM oradb..SCOTT.DEPT WHERE deptno = 10
To prevent similar errors, you can create a view to give a case-insensitive representation of a linked-server table. You begin by creating a SQL Server view of an Oracle table:
CREATE VIEW dbo.v_oradb_scott_dept
AS
SELECT *
FROM oradb..SCOTT.DEPT
By querying this view, you can see that the case sensitivity of Oracle metadata no longer affects SQL Server queries against the data. This convenience comes at a price, however. SQL Server resolves the query's WHERE clause locally. Because Oracle doesn't see the WHERE clause, it does a full table scan and sends the entire table across the network.
Prev. page
1
[2]
3
next page 
