Mapping Logins
The linked server essentially defines the connection to the Oracle server from the SQL Server system. However, just like with SQL Server, to access any of the databases, you need to authenticate your linked-server connection to the Oracle database server. The sp_addlinkedsrvlogin stored procedure lets you map your SQL Server login to a remote user's account on the linked-server system. The following code shows how to use sp_addlinkedsrvlogin to map the SQL Server login to the sample Scott account on the Oracle server:
exec sp_addlinkedsrvlogin 'TecaOracle',
false, NULL, 'Scott', 'tiger'
The first parameter specifies the name of the linked server system. This name must match the name of the linked server that you created with sp_addlinkedserver. This example uses the value TecaOracle. The second parameter determines the name of the login SQL Server will use when the linked server attempts to connect to the remote system. A value of true indicates that SQL Server will use the current SQL Server login credentials to connect to the linked server. You can use this option only if the logins of the two database servers match. A value of false indicates that you'll supply the remote login credentials as part of the sp_addlinkedsrvlogin procedure. The third parameter specifies the local login that SQL Server will use to make this connection to the remote server. If you use a value in this parameter, a matching SQL Server login must exist. Using the default value of NULL indicates that this remote login will be used for all SQL Server logins for all connections to the linked Oracle server. The fourth and fifth parameters supply login and password values that are valid on the linked server.
In this example, SQL Server will use the Oracle sample username Scott to connect to the Oracle database server. Figure 8 shows the completed Oracle linked server as Enterprise Manager displays it.
You can use three handy T-SQL commands to list information about the linked servers that are available on your system. The EXEC sp_linkedservers command lists the linked servers and their providers; the EXEC sp_helpserver command lists the remote servers, their network names, and their timeout values. And SELECT * FROM sysservers lists the servers that SQL Server can access as OLE DB data sources.
Running Queries Against the Oracle Linked Server
Now that the connection is configured, you're ready to start running SQL Server statements against the linked Oracle server. Queries to linked servers use the following four-part naming syntax: linked_server_name.catalog_name.schema_name.table_name. As Figure 8 shows, the Oracle SCOTT schema has five user tables. To select all the rows from the EMP table, you'd enter the following SQL statement:
SELECT * FROM TecaOracle..SCOTT.EMP
The value TecaOracle identifies the linked server. The Catalog value is always blank because the Oracle database supports only one catalog. SCOTT is the name of the Oracle schema associated with the username Scott that we mapped earlier using the sp_addlinkedsrvlogin stored procedure. And finally, EMP is the name of the table in the schema you want to access. The right pane of Figure 8 shows the values that your linked server queries need to use in each of these name parts.
After you set up the linked server, SQL Server automatically begins to log statistics for the table on the linked server, attempting to optimize the type of queries that will be sent to the linked server. One important point about linked servers is that the SQL Server distributed query processor—not the linked server—is responsible for optimizing the queries that SQL Server sends to the linked server to retrieve data.
SQL Server first queries the linked server to determine the level of SQL dialect it supports, such as the full SQL Server dialect, the SQL-92 dialect, the ODBC core dialect, or the Jet dialect, then attempts to push operations such as joins, unions, sorts, and GROUP BYs to the remote server. However, the distributed query processor doesn't have the same type of information about remote tables as it does about local tables, so the steps it takes to retrieve data aren't always the most optimal.
SQL Server Query Analyzer's ability to display the execution plan is a good way to get some insight into the way SQL Server's distributed query processor will process the remote query. One possible workaround for queries that don't perform as expected is to use the OPENQUERY() function. OPENQUERY() essentially bypasses the distributed query processor and sends the query directly to the remote server for processing.
Overcoming the Great Database Divide
Using heterogeneous databases is a way of life for most corporations. SQL Server's linked-server feature lets you create a nearly seamless connection between your SQL Server system and other OLE DB—compliant databases including Oracle, Access, and IBM DB2. SQL Server's linked-server feature can provide the interoperability bridge that you need to connect the different database islands that exist in your organization. This connection helps you make the most of your investment in SQL Server as well as in Oracle and other OLE DB—compliant databases that SQL Server's linked-server capability supports.