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