Linked servers can bring your SQL Server and Oracle databases together

Suppose you have a Microsoft .NET-enabled Internet ordering system but your warranty system is a legacy Oracle database application. You don't want to bill your commercial customers when they place orders for warranty replacement parts, so you need realtime answers from your Oracle database. Creating a linked server lets you use SQL Server to query your Oracle database in realtime to find out who your existing customers are.

When your data is distributed across several databases on multiple SQL Servers, linked servers let you run queries distributed across those servers. When all the servers are SQL Servers, configuration is easy, and SQL Server Books Online (BOL) covers everything you need to know. However, if some of your data is on an Oracle database, for example, configuring a linked server brings special challenges. You need to understand that even if you configure an Oracle linked server in SQL Server Enterprise Manager, your SQL Server is a client of your Oracle database. Therefore, you must successfully install and configure Oracle client software on your SQL Server. Oracle provides product support only for Oracle8 and later, so this article assumes that you're running Oracle8 or later. The Oracle Net8 library provides the client software that SQL Server needs.

In Oracle, a schema is the name for the entity SQL Server professionals know as a database. To connect to Oracle, you need to supply the schema name, password, and host string. An Oracle schema is owned by a specific Oracle username, so the schema name is also the username of the user who owns the schema. One Oracle username owns only one schema. You can find out more about the contents of a schema by querying the Oracle data dictionary, as I describe in the sidebar "System Procedures for Obtaining Linked-Server Metadata," page 26. The Oracle host string is also known as a service name or System Identifier (SID). What SQL Server calls an instance, Oracle calls a database. During an Oracle server installation, Oracle Universal Installer (a graphical interface similar to SQL Server's installation program) asks for a SID to use as the name of the Oracle database.

If you install your Oracle instance on a Windows machine and set your SID to Ora817, as in this article's example installation, you'll have a Windows 2000 service called OracleServiceORA817. This service is analogous to the MSSQLSERVER service for SQL Server 2000. For information about the architectural differences between the two products, see the Microsoft article "Migrating Oracle Databases to Microsoft SQL Server 7.0" (http://msdn.microsoft.com/library/techart/oracle2sql.htm).

Setting Up the Oracle Client on SQL Server
A SQL Server instance and its client exchange data through the application-level Tabular Data Stream (TDS) protocol by using a supported network protocol such as TCP/IP or IPX/SPX. Oracle servers and clients use Net8 to exchange data through three components: the Transparent Network Substrate (TNS), an Oracle protocol adapter, and a supported network library. Net8 uses a protocol adapter for translations between TNS and the network library. Net8, a replacement for SQL*Net, uses service names to find servers. The network libraries Net8 supports are TCP/IP, SPX, Named Pipes, Logical Unit Type 6.2 (LU6.2), and Bequeath. TCP/IP and SPX are the network libraries you can use to connect remote clients to Oracle servers. You use LU6.2 for compatibility with the IBM Advanced Program-to-Program Communications (APPC) architecture. Net8 uses the Bequeath network protocol for making connections when an Oracle client is logged in locally to the Oracle server.

For a Net8 client to make a successful connection to an Oracle server, the client must be able to find the service name for the server. Oracle clients can resolve service names by using a local client file called tnsnames.ora (which is analogous to a Windows HOSTS file), the DNS, or an Oracle Names Server. With Oracle9i, Lightweight Directory Access Protocol (LDAP) becomes another service-name resolution option. However, at press time, Microsoft didn't officially support Oracle9i as a linked server.

The Java-based Oracle Universal Installer helps you install Oracle server and client software on both Windows and UNIX systems. On a Windows system, inserting the Oracle installation CD-ROM autostarts Oracle Universal Installer. I recommend selecting a default installation of the Oracle8 client because in addition to installing Net8, the default installs the Net8 Assistant, the Net8 Configuration Assistant, and SQL*Plus and adds these options to your Start menu. SQL*Plus is a client tool comparable to SQL Server's osql.

Troubleshooting
Using SQL Server to validate a linked-server connection to Oracle can give misleading results. Until you execute a linked-server query, you can't be certain whether you configured the link correctly. Keep in mind that you can issue a linked-server query indirectly. In Enterprise Manager, if you select Tables or Views under your linked-server definition, you're indirectly querying the Oracle data dictionary. After I issued such a query, I received the error message in Figure 1. But when I executed Oracle's TNSPING command (similar to a TCP/IP PING, but specifically for testing connectivity to an Oracle database) from a command prompt, the results showed that Oracle client and networking components were installed, as Figure 2 shows.

Additionally, I had successfully used both the Net8 Assistant, as Figure 3 shows, and the Net8 Configuration Assistant to make actual connections to the example SCOTT schema, which is analogous to the SQL Server Pubs database. Using either of those network administration tools is sufficient for validating a database connection, so I knew that the problem lay elsewhere.

   Prev. page   [1] 2 3     next page



You must log on before posting a comment.

If you don't have a username & password, please register now.

Reader Comments

I liked you article. I have been working with SQL Server and Oracle linked in a production environment for 2 years now. You brought up the interesting use of a UDF for selecting from Oracle.

I find that best performance for Inserts is achieved by the construct of :

INSERT INTO OPENQUERY(ORACLEDB, 'Select ... FROM MYTABLE WHERE 1=2') VALUES ( ... )

What do you think ?

Jason Nunn- November 12, 2001

What about wrapping a distributed transaction around multiple inserts into Oracle and an update into SQL? The MSDAORO OLEDB Provider seems to hurl upon initiating a distributed transaction around the whole lot.

Any ideas?

Richard M. Bagdonas- November 28, 2001

excellent

Aleksandar Jovanovic- June 12, 2004

Nice article, but as I am from Germany I have no clue where to find the PATH environment variable described above.

Would be nice to get help, because I think this is where my problems comes from.

Alex- June 17, 2004

Fantastic!!! Truly useful. Saved a lot of time and frustration. the details on change in PATH file did the trick.

Thanks a lot.

Samir Shah- July 02, 2004

I agree with Samir.. The advice on the PATH variable was just what the doctor ordered.. Thanks again!!

garyhampson- September 13, 2004

Article Rating 5 out of 5

Outstanding article, I've linked multiple SQL Servers before but this time I was able to link to an Oracle 10g database -- good job!

Anonymous User- November 17, 2004

Article Rating 5 out of 5

In SQL Server I create view like "select * from..." source is from Oracle Table. When I update oracle table struture (like add a column) SQL Server (via Linked Server) doesn't update schema...of course, I try refresh, but no effect... Somebody have an idea ? :) Thanks

Anonymous User- November 22, 2004

Hi : I am retriving data from a oracle database using sql server linked server.

I found that when I run the query in sql plus (oracle) the query executes within seconds but when I am executing the same query using sql server query analyzer it is taking more than 10 min. What could be the problem.

my query involves inner join on 4 tables with millions of data and distinct and where clause. I get 6 rows with the query I am using.

Anonymous User- January 06, 2005

Anonymous User above, Any query over a network with joins will take ages. If you join two tables, the number of "reads" is very roughly equal to the total number of rows multiplied together I think. Hence all these reads become network reads so it takes ages. Try creating a view in oracle and querying that instead - that should be quicker. Also if you want to get technical check the join order of the query plan.

Anyway my comments - article is good, however I still can't get it to work. Having changed the path variable does anyone know if I have to restart the SQL server? Cheers Will

Anonymous User- January 26, 2005

Very nice article. I established successfully connection to Oracle but I am unable to execute Stored Procedure. Any clue why?

Anonymous User- March 04, 2005

Article Rating 5 out of 5

I have: Oracle 8i and SQL Server 2000. I want to create a link server with a Oracle Database.

First: I try with the OLE DB Provider of Oracle. I think that all the necesary data is ok, because I can see all the tables of the Oracle Database in my SQL Server and I can create views of the tables, but when I do a select of a table with more of 100 register I obtain an error of the controler like: Servidor: mensaje 7399, nivel 16, estado 1, línea 1 El proveedor OLE DB 'OraOLEDB.Oracle' informa de un error. El proveedor no proporcionó información acerca del error.

Second: I try with the OLEDB Provider of Microsoft and I can to create the linked server, but whe I try to create a views of any table of the Oracle Data Base I obtain an error like:

Servidor: mensaje 7399, nivel 16, estado 1, línea 1 El proveedor OLE DB 'MSDAORA' informa de un error. El proveedor no proporcionó información acerca del error.

I execute the register script for my windows 2000 correctly but I obtain this error too.

What is the problem with the two OLE DB Provider? Thanks

dialer_ft- March 30, 2005

Article Rating 5 out of 5

- SQL Server 2000 & Oracle 10g - Oracle10g Provider for OLE DB Version 10.1.0.4.0

Able to create linked server and view all tables. However, query give Server: Msg 7320, Level 16, State 2, Line 1 Could not execute query against OLE DB provider 'OraOLEDB.Oracle'. OLE DB error trace [OLE/DB Provider 'OraOLEDB.Oracle' ICommandText::Execute returned 0x80040155].

Query is select count(DEPT_CODE) from ORADB..ORADBA.DEPARTMENTS

Have changed the PATH. But still the same error.

What other config did I miss out ? Pls help... Txs.

Anonymous User- June 12, 2005

Article Rating 3 out of 5

Answer for the last comment. I had the same prob.This one solved it:

Message 3 Error 7302: Could not create an instance of OLE DB provider 'MSDAORA' Make sure that the MSDAORA.dll file is registered correctly. (The MSDAORA.dll file is the Microsoft OLE DB provider for Oracle file.) Use RegSvr32.exe to register Microsoft OLE DB Provider for Oracle. If the registration fails, reinstall Microsoft Data Access Components (MDAC). For more information about MDAC, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn.microsoft.com/data/Default.aspx Note If you use a third-party Oracle provider, and your Oracle provider cannot run outside a SQL Server process, enable it to run in-process by changing the provider options. To change the provider options, use one of the following methods. • Method 1 Locate the following registry key. Then, change the value of the AllowInProcess (DWORD) entry to 1. This registry key is located under the corresponding provider name: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\ProviderName • Method 2 Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box.

Anonymous User- June 21, 2005

Article Rating 5 out of 5

Another comment about last post: i deleted the linked server and add it from begining but this time i did this:

Method 2 Set the Allow InProcess option directly through SQL Server Enterprise Manager when you add a new linked server. Click Provider Options, and then click to select the Allow InProcess check box.

Anonymous User- June 21, 2005

Article Rating 4 out of 5

See More Comments  1   2