Oracle Database Discussions

Hi All

I'm trying to understand how to configure a DB Link to MS SQL Server, I've learned a lot of links but still cannot see where I'm wrong.

I tested various kinds of configuration:

Oracle Database 11g  11.2.0.3.0 on AIX   --->   Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (64-bit) on Windows Server 2008R2 SP1  --->  MS SQL server 2008 R2 x64 SP1 on Windows Server 2008R2 SP1

Oracle Database 11g  11.2.0.3.0   on AIX --->   Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2  --->  MS SQL server 2008 R2 x64 SP1 on Windows Server 2008R2 SP1

Oracle Database 11g  11.2.0.3.0   on AIX --->   Oracle Database Gateway 11g Release 2 (11.2.0.1.0) for MS SQL (32-bit) on Windows Server 2003R2 x32 SP2  +  MS SQL server 2005 x32 SP4 on the same Windows Server.

I followed instructions like

Configuring Oracle Database Gateway for ODBC

http://www.youtube.com/watch?v=rEZOiZrc708

etc.

But nothing works (((

Help me please understand what is wrong?

Let me show the latest configuration where the Oracle Database Gateway  and the MS SQL server 2005 x32 SP4  are installed on the same Windows Server 2003R2 x32 SP2.

First I created a system DSN as it's described, for example, here

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\dg4msql]
"Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
"Description"="dg4msql"
"Server"="MSVO-TG4MSQL"
"LastUser"="RECOVER"

then I configured .ora config files

here are  my config files:

initdg4msql.ora - on Oracle Database Gateway

# This is a customized agent init file that contains the HS parameters
# that are needed for the Database Gateway for Microsoft SQL Server

#
# HS init parameters
#
HS_FDS_CONNECT_INFO=[10.48.104.18]//AdventureWorks
HS_FDS_TRACE_LEVEL=OFF
HS_FDS_RECOVERY_ACCOUNT=RECOVER
HS_FDS_RECOVERY_PWD=RECOVER
HS_FDS_RESULTSET_SUPPORT=TRUE

listener.ora  - on Oracle Database Gateway

# listener.ora Network Configuration File: F:\oracle\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER=
   (SID_LIST=
      (SID_DESC= 
         (SID_NAME=dg4msql)
         (ORACLE_HOME=F:\oracle)
         (PROGRAM=dg4msql)
      )
   )


LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.48.104.18)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = F:\oracle

tnsnames.ora - on Oracle Database Gateway

# tnsnames.ora Network Configuration File: F:\oracle\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

dg4msql = 
 (DESCRIPTION=
  (ADDRESS = (PROTOCOL = TCP)(HOST=10.48.104.18)(PORT=1521))
  (CONNECT_DATA = (SID = dg4msql))
  (HS=OK)
 )



EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

Create DB Link on Oracle Database server

Create database link BTEST.WORLD connect to recover identified by “nnnn” using ‘(DESCRIPTION =
        (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))
        )
        (CONNECT_DATA =
        (SID = tg4msql))
        (HS = OK))’

tnsnames.ora - on Oracle Database server

1. SQLLAB.world=

(DESCRIPTION =

        (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP )(HOST = msvo-tg4msql)(PORT = 1521))

        )

        (CONNECT_DATA =

        (SID = tg4msql))

        (HS = OK))