Wednesday, March 28, 2012

Oracle DB Link to SQL Server using Windows

Found several dozen posts on this but it still took me a while to figure out. Wanted to share my quest here. The following instructions are for Windows. I will try to figure out a Linux install next.


1. Create an ODBC connection (using 32bit ODBC) to your SQL Server instance


2. Create a SQL Server account on the SQL Server database you want to connect to.


3. Add an entry to your TNSNAMES.ORA file for the new SID


dg4odbc  =
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST = localhost)(PORT=1521))
    (CONNECT_DATA=(SID=dg4odbc))
    (HS=OK)
  ) 

4. Modify your listener.ORA file. Add the Following SID DESC to your SID LIST


(SID_DESC=
         (SID_NAME=dg4odbc)
         (ORACLE_HOME=C:oracleappproduct11.2.0dbhome_1)
         (PROGRAM=dg4odbc)

      )


5. Create a initSID.ora file in ORACLE_HOME/hs/admin and set the following parameters:


HS_FDS_CONNECT_INFO = <SQL SERVER DSN NAME>
HS_FDS_TRACE_LEVEL = 0
HS_OPEN_CURSORS = 300

You can use HD_FDS_TRACE_LEVEL = ODBC if while you are troubleshooting. 


6. Create your database link


CREATE PUBLIC DATABASE LINK "Link NAME" CONNECT TO "SQL USER" IDENTIFIED BY VALUES 'SQL USER PASSWORD' USING 'DG4ODBC'

7. Execute remote sql statement


 select * from <sql server table name>@<db link name from step 6>;


Crack open a Celebratory Mt. Dew and bask in the glory of this small victory.

0 comments:

Post a Comment