how to get hsodbc working?

From: <tpcolson_at_gmail.com>
Date: 17 Mar 2005 16:30:50 -0800
Message-ID: <1111105850.158571.132720@g14g2000cwa.googlegroups.com>

Hi all. I am trying to set up Oracle 9i to talk to various access databases (why...subject of some research. We want to see if it can be done then write about it)

I set up a system DSN to a simple ASSESS database names asmis, with a table "asmis" and called the dsn "TEST". THe access DB and Oracle 9i are all the same machine. I keep getting the same error, which metalink tells me is "You have the SID name wrong in the tsname.ora..." when I clearly have the SID correct.

any ideas?

I have set the following:

tsnames.ora:

TEST =
  (DESCRIPTION =
    (ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = x.x.x.x)(PORT = 1521))
    )
    (CONNECT_DATA =
       (SID = TEST)
    )
    (HS=OK)
  )

listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
      )


      (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = hydrodb)(PORT = 1521))
      )

)
)

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = C:\oracle\ora92)
(PROGRAM = extproc)

    )

     (SID_DESC =


      (SID_NAME = TEST

      (ORACLE_HOME = C:\oracle\ora92)

      (PROGRAM = hsodbc)

    )
    (SID_DESC =
(GLOBAL_DBNAME = OEMREP)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = OEMREP)

    )
    (SID_DESC =
(GLOBAL_DBNAME = manager)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = manager)

    )
    (SID_DESC =
(GLOBAL_DBNAME = stream)
(ORACLE_HOME = C:\oracle\ora92)
(SID_NAME = stream)

    )
  )

initTEST.ora:

HS_FDS_CONNECT_INFO = TEST
HS_FDS_TRACE_LEVEL = 4
HS_FDS_TRACE_FILE_NAME = c:\temp\AccessTraceLog.trc

odbc.ini:

[ODBC 32 bit Data Sources]

MS Access Database=Microsoft Access Driver (*.mdb) (32 bit)
Excel Files=Microsoft Excel Driver (*.xls) (32 bit)
dBASE Files=Microsoft dBase Driver (*.dbf) (32 bit)

TEST=Microsoft Access Driver (*.mdb) (32 bit) [MS Access Database]
Driver32=C:\WINDOWS\System32\odbcjt32.dll [Excel Files]
Driver32=C:\WINDOWS\System32\odbcjt32.dll [dBASE Files]
Driver32=C:\WINDOWS\System32\odbcjt32.dll [TEST]
Driver32=C:\WINDOWS\System32\odbcjt32.dll

then I restart the listner service. TNSPING of TEST returns:

C:\oracle\ora92\network\trace>tnsping TEST

TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 17-MAR
05 19:23:01

Used parameter files:
C:\oracle\ora92\network\admin\sqlnet.ora

Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =

(PROTOCOL = T
(HOST = 152.1.32.122)(PORT = 1521))) (CONNECT_DATA = (SID = TEST))
(HS=OK))

OK (20 msec)

then I create a public database link

then I try

select * from ASMIS_at_TEST;

which returns
Oraa-28545: error diagnosed by net8 when connecting to an agent NCRO: Failed to make RSLV connection
ORA-02063: Preceding two lines from TEST

trcasst listener.trc returns:

Error found. Error Stack follows:

              id:9
  Operation code:68
      NS Error 1:12537
      NS Error 2:12560
NT Generic Error:507
  Protocol Error:109
        OS Error:0

NS & NT Errors Translation
12537, 00000 "TNS:connection closed"
// *Cause: "End of file" condition has been reached; partner has disconnected.

// *Action: None needed; this is an information message. /
12560, 00000 "TNS:protocol adapter error" // *Cause: A generic protocol adapter error occurred. // *Action: Check addresses used for proper protocol specification. Before
// reporting this error, look at the error stack and check for lower level
// transport errors.For further details, turn on tracing and reexecute the
// operation. Turn off tracing when the operation is complete. /
00507, 00000 "Connection closed"
// *Cause: Normal "end of file" condition has been reached; partner has
// disconnected.
// *Action: None needed; this is an information message. /

///////////////////////////////////////////////////////////////

///////////////////////////////////////////////////////////////

and listener.log returns:

17-MAR-2005 19:16:11 * (CONNECT_DATA=(SID=TEST)) * (ADDRESS=(PROTOCOL=tcp)(HOST=152.1.32.122)(PORT=1353)) * establish * TEST * 12505
TNS-12505: TNS:listener could not resolve SID given in connect descriptor

17-MAR-2005 19:16:36 * service_update * manager * 0
17-MAR-2005 19:16:36 * trc_level * 0
17-MAR-2005 19:16:42 * service_update * OEMREP * 0
17-MAR-2005 19:16:42 * trc_level * 0
17-MAR-2005 19:16:51 * service_register * stream * 0

Trace information written to C:\oracle\ora92\network\trace\listener.trc 17-MAR-2005 19:16:59 * trace * 0
17-MAR-2005 19:17:11 * (CONNECT_DATA=(SID=TEST)) * (ADDRESS=(PROTOCOL=tcp)(HOST=152.1.32.122)(PORT=1355)) * establish * TEST * 12505
TNS-12505: TNS:listener could not resolve SID given in connect descriptor

17-MAR-2005 19:18:15 * service_update * manager * 0
17-MAR-2005 19:18:21 * service_update * OEMREP * 0
17-MAR-2005 19:21:00 * service_update * manager * 0
17-MAR-2005 19:23:01 * ping * 0
17-MAR-2005 19:23:51 * service_update * OEMREP * 0
17-MAR-2005 19:24:15 * (CONNECT_DATA=(SID=TEST)) *

(ADDRESS=(PROTOCOL=tcp)(HOST=152.1.32.122)(PORT=1357)) * establish * TEST * 12505
TNS-12505: TNS:listener could not resolve SID given in connect descriptor
17-MAR-2005 19:24:51 * service_update * manager * 0 17-MAR-2005 19:24:57 * service_update * OEMREP * 0 Received on Thu Mar 17 2005 - 18:30:50 CST