Install Oracle ODBC on Solaris(Unix)_Connect Oracle_to_SQL_Server

I’ve searched a lot of software for to connect Oracle and SQL Server databases. There are a lot of software, but I couldn’t use any of them except DataDirect ODBC. Unfortunately, this soft is not free. I couldn’t find any free software that works on unix.

Let’s start installation.

1. Download DataDirect ODBC driver from here.

Choose your appropriate system(64-bit UNIX/Linux, 32-bit UNIX/Linux,…). After you choose it, there will appear Registration region. Fill it with your information and when you see the following section, do as it is shown on the picture:

DataDirect Registration Region

I chose Oracle, SQL Server and Solaris SPARC…Because I am trying to connect Oracle Database to SQL Server Database, note Oracle Database is installed on Solaris SPARC.

2.  Extract Downloaded file:

# gunzip PROGRESS_DATADIRECT_CONNECT_ODBC_6.1.1_SOL_32.tar.Z

# tar -xvf PROGRESS_DATADIRECT_CONNECT_ODBC_6.1.1_SOL_32.tar

3. Go to the extracted file and run the following:

# ./unixmi.ksh
Installation……………………………………………………………………………

Solaris
Is this the current operating system on your machine (Y/N) ? [Y] PRESS ENTER

READ THE LICENSE TERMS(PRESS ENTER to read whole content)

…

EULA - Connect Products (revised 2/21/11)

Enter YES to accept the above agreement : YES

…

In the Key field, enter either EVAL or the Key provided.

Fill it with your registration information. I am writing EVAL because I just want to try it.

Name          :Mariam
Company       :JSC Bank of Georgia
Serial Number :EVAL
Key           :EVAL

…

1.   Drivers for All Supported Databases
2.   Single Driver
Please enter an option [1]: 2

1.   DB2 Wire Protocol
2.   dBASE
3.   Greenplum Wire Protocol
4.   Informix Wire Protocol
5.   MySQL Wire Protocol
6.   Oracle Wire Protocol
7.   PostgreSQL Wire Protocol
8.   SQL Server Wire Protocol
9.   Sybase Wire Protocol
10.  Teradata
11.  Text
12.  Informix (client)
13.  Oracle (client)
14.  SQL Server Legacy Wire Protocol

Please enter driver option: 8

To change this information, enter C. Otherwise, press Enter to continue. : PRESS ENTER
Enter the full path to the temporary install directory.[/tmp]:PRESS ENTER
Enter the full path to the install directory.[/opt/Progress/DataDirect/Connect_for_ODBC_61]: PRESS ENTER
Would you like to install another product (Y/N) ? [Y] N

…

Installation ended successfully.

3. Configure odbc.ini file, located in /opt/Progress/DataDirect/Connect_for_ODBC_61.

There will be written some information when you open this file, but the following entries are enough:

[MS_SQLServer2005]
Driver=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib/ivsqls25.so
Description=SQL Server ODBC driver
Database=DB_NAME
Address=10.0.0.88,1433
Quoteld=No

Note I have changed the following values:

MS_SQLServer2000 – is your desired name, name it as you wish.(Will be used later)
Description – Just a descriptive text, write what you wish.
Database – Name of the SQL Server Database.
Address – Ip address of the server on which SQL Server Database is installed. 1433 is a default port for SQL Server.  Ensure that this port is opened on  firewall(Or you will not  be able to connect SQL Server)

4. Tnsnames.ora

DB_NAME_ALIAS=  
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.11)(PORT=1522))
     (CONNECT_DATA=(sid=DB_NAME))
     (HS=OK)
  )

Where,

DB_NAME_ALIAS – is your desired name.
DB_NAME – Is SQL Server Database name
1522 – Is any free port, If you have multiple listeners on the server(where Oracle is installed) choose any port that is available.
10.0.0.11 – Ip of the server, where Oracle is installed

5. listener.ora

LISTENER_DB_NAME_ALIAS = 
  (DESCRIPTION_LIST=
     (DESCRIPTION =
       (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = tcp)(HOST = 10.0.0.11)(PORT = 1522))
       )
     ) 
  ) 

SID_LIST_LISTENER_DB_NAME_ALIAS=
   (SID_LIST=
     (SID_DESC=
     (SID_NAME=DB_NAME_ALIAS)
     (ORACLE_HOME=/u0/app/oracle/10.2.0/db)
     (PROGRAM= hsodbc)
     (ENVS="LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib:/u0/app/oracle/10.2.0/db/bin")
    )
)

Underlined entry is very important, or you will get  ORA-28500 error.

6.  Go to  the following directory: $ORACLE_HOME/hs/admin

There will be the file called inithsodbc.ora, copy that file and rename it like this: initDB_NAME_ALIAS.ora

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent.

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = MS_SQLServer2005
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = hsMSSqlMari.trc
HS_FDS_SHAREABLE_NAME=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib/libodbc.so
set ODBCINI=/opt/Progress/DataDirect/Connect_for_ODBC_61/odbc.ini
#
# Environment variables required for the non-Oracle system
#

7.  Start the listener.

Before starting listener, ensure that ODBC lib directory is specified in the shared library environment variable:

LD_LIBRARY_PATH=/opt/Progress/DataDirect/Connect_for_ODBC_61/lib:/u0/app/oracle/10.2.0/db/bin

After that we can start listener:

$ lsnrctl start LISTENER_DB_NAME_ALIAS

8. Create database link:

SQL> create database link LINK_NAME     
     connect to “username”     
     identified by “password”     
     using ‘DB_NAME_ALIAS’;

Where,

username– is the user in SQL Server Database.
password – is its password

Do not forget to use quotas(“”).

9. Check if it works

SQL> select * from dual@LINK_NAME;

D
---
X

That’s it Smile

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: