Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Oracle and SQL Server Interoperability Part 3

By Haidong Ji,

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.

Total article views: 11780 | Views in the last 30 days: 7
 
Related Articles
ARTICLE

Executing a Package from Visual Basic

So you've created a SQL Server package and now you're ready to integrate it into your Visual Basic a...

FORUM

linked server to oracle

create table in oracle over linked server

FORUM

Creating DB link b/w Oracle and SQL server

Creating DB link b/w Oracle and SQL server

FORUM

basic guidelines on creating Indexes

basic guidelines on creating Indexes

FORUM

SQL Server And Oracle

SQL Server And Oracle

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones