In part 1 and part 2 of my series on Oracle and SQL Server interoperability, I talked about some subtle differences between Oracle and SQL Server and some of Oracle drivers available. In this article, I will talk about a new tool that comes with Oracle 10g, Oracle Instant Client.

Prior to Oracle 10g, to establish connection to your Oracle databases, you have to install the full Oracle client to get proper Oracle driver loaded on your server or PC. The full client is bloated and pretty big in size and makes quite a few modifications to the registry. In reality, all you need is relevant dlls and some config files to establish a connection to your Oracle server. This is especially true for your server application, because you do not want too many bloated stuff on a production server. The simplest solution is usually the best solution.

Setting up Instant Client is not very complicated. A step-by-step procedure is provided below.

Download and setup the basic and ODBC package

If you try to download packages for Instant Client from Oracle's web site, you will be presented with a few packages to download. You will only need to the Basic Package and the ODBC package.

For Basic Package, you can create a folder on C: called Oracle. You can then extract all files within Basic Package into the C:\Oracle folder. A sub folder called InstantClient may be created. That is all fine. After this step is done, you will have the basic DLL files for Oracle OCI connection.

After Basic Package is downloaded and setup, you can then extract the ODBC Package files into the same folder you created above. After that is done, run ODBC_Install.exe. This is make necessary registry changes and DLL registration on the system.

Setup the environment variables and Oracle network files

The last step is to setup environment variables. Make sure C:\Oracle\InstantClient is added to the PATH variable. In addition, you need to create a new path variable called TNS_ADMIN. The value for TNS_ADMIN is also C:\Oracle\InstantClient. In my experience on an American English system, these are the only 2 environmental variables that I added. If you are working with a non-English system, you may have to setup a language/collation variable.

Depending on the versions of Oracle you are running and authentication methods supported, you may need to have tnsname.ora, sqlnet.ora, or ldap.ora. Get that from your Oracle admin and put those .ora files into C:\Oracle\InstantClient. Please do remember to reboot the box. You should be able to establish connection to your Oracle databases.

Now you are ready to create ODBC DSN for connection. In the driver list, use Microsoft ODBC for Oracle.

Potential Problems

In my experience, if a DTS package is created and scheduled, for whatever reason, it couldn't stop on its own. If you view job status under SQL Server Agent, it is always executing, even after the data transformation is done. I had to create a job to kill it. And this problem only happens on Windows server 2003, not Windows 2000. The test was conducted on Sql Server 2000 with service pack 3a.

To get around this, instead of DTS, I used the T-SQL openquery statement. That seemed to solve the problem.