3 Replies Latest reply: Nov 30, 2012 12:24 PM by 723623 RSS

    Oracle TO MSSQLServer vi ODBC Hangs

    723623
      Hi everyone:

      I'm trying to connect from an Oracle 11.2.0.3 on Solaris 10 (64 bit SPARC) to a MS SQL server 2000 using openlink odbc drivers 6.1 for SQL Server.

      We can do a tnsping to the remore database and it works, but when we try to query any table from SQL Server connections hangs always in the same step.



      Oracle Corporation --- MONDAY NOV 26 2012 11:20:26.750


      Heterogeneous Agent Release
      11.2.0.2.0




      Oracle Corporation --- MONDAY NOV 26 2012 11:20:26.749

      Version 11.2.0.2.0

      Entered hgogprd
      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "DEBUG"
      Entered hgosdip
      setting HS_OPEN_CURSORS to default of 50
      setting HS_FDS_RECOVERY_ACCOUNT to default of "RECOVER"
      setting HS_FDS_RECOVERY_PWD to default value
      setting HS_FDS_TRANSACTION_LOG to default of HS_TRANSACTION_LOG
      setting HS_IDLE_TIMEOUT to default of 0
      setting HS_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
      setting HS_NLS_NCHAR to default of "AL32UTF8"
      setting HS_FDS_TIMESTAMP_MAPPING to default of "DATE"
      setting HS_FDS_DATE_MAPPING to default of "DATE"
      setting HS_RPC_FETCH_REBLOCKING to default of "ON"
      HOSGIP returned value of "1" for HS_FDS_FETCH_ROWS
      setting HS_FDS_RESULTSET_SUPPORT to default of "FALSE"
      setting HS_FDS_RSET_RETURN_ROWCOUNT to default of "FALSE"
      setting HS_FDS_PROC_IS_FUNC to default of "FALSE"
      setting HS_FDS_MAP_NCHAR to default of "TRUE"
      setting HS_NLS_DATE_FORMAT to default of "YYYY-MM-DD HH24:MI:SS"
      setting HS_FDS_REPORT_REAL_AS_DOUBLE to default of "FALSE"
      setting HS_LONG_PIECE_TRANSFER_SIZE to default of "65536"
      setting HS_SQL_HANDLE_STMT_REUSE to default of "FALSE"
      setting HS_FDS_QUERY_DRIVER to default of "TRUE"
      HOSGIP returned value of "FALSE" for HS_FDS_SUPPORT_STATISTICS
      Parameter HS_FDS_QUOTE_IDENTIFIER is not set
      setting HS_KEEP_REMOTE_COLUMN_SIZE to default of "OFF"
      setting HS_FDS_GRAPHIC_TO_MBCS to default of "FALSE"
      setting HS_FDS_MBCS_TO_GRAPHIC to default of "FALSE"
      Default value of 64 assumed for HS_FDS_SQLLEN_INTERPRETATION
      setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics;gtw$:SQLGetInfo"
      setting HS_FDS_DELAYED_OPEN to default of "TRUE"
      setting HS_FDS_WORKAROUNDS to default of "0"
      Exiting hgosdip, rc=0
      ORACLE_SID is "CardHolders"
      Product-Info:
      Port Rls/Upd:2/0 PrdStat:0
      Agent:Oracle Database Gateway for ODBC
      Facility:hsa
      Class:ODBC, ClassVsn:11.2.0.2.0_0008, Instance:CardHolders
      Exiting hgogprd, rc=0
      Entered hgoinit
      HOCXU_COMP_CSET=1
      HOCXU_DRV_CSET=31
      HOCXU_DRV_NCHAR=873
      HOCXU_DB_CSET=873
      HS_LANGUAGE is AMERICAN_AMERICA.WE8ISO8859P1
      rc=2147476520 attempting to get LANG environment variable.
      HOCXU_SEM_VER=112000
      Entered hgolofn at 2012/11/26-11:20:26
      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/apex01/oraapex/ODBC_64/lib/sql_mt_lt.so"
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a170e68
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1724b0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a18db90
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a172520
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a18dce0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17dca0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a191240
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a182600
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a182cc0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a183270
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a183988
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a182d10
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17e190
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a184ec8
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1876d8
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a187928
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a18dc30
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a18dc88
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1887a0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17e130
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17dad8
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a173ba0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a173ef0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a174560
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17dea0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a156d60
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17f070
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a183048
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1760a0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17c2f0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17db78
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a184390
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a184ac0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1873f8
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1906d8
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a191038
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a187dc8
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a188048
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1886c8
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a1883a0
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a176040
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a190738
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a17c368
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a18e280
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Entered hgolofns at 2012/11/26-11:20:26
      symbol_peflctx=0x7a190b28
      hoaerr:0
      Exiting hgolofns at 2012/11/26-11:20:26
      Exiting hgolofn, rc=0 at 2012/11/26-11:20:26
      HOSGIP for "HS_OPEN_CURSORS" returned "50"
      HOSGIP for "HS_FDS_FETCH_ROWS" returned "1"
      HOSGIP for "HS_LONG_PIECE_TRANSFER_SIZE" returned "65536"
      HOSGIP for "HS_NLS_NUMERIC_CHARACTER" returned ".,"
      HOSGIP for "HS_KEEP_REMOTE_COLUMN_SIZE" returned "OFF"
      HOSGIP for "HS_FDS_DELAYED_OPEN" returned "TRUE"
      HOSGIP for "HS_FDS_WORKAROUNDS" returned "0"
      HOSGIP for "HS_FDS_MBCS_TO_GRAPHIC" returned "FALSE"
      HOSGIP for "HS_FDS_GRAPHIC_TO_MBCS" returned "FALSE"
      Invalid value of 64 given for HS_FDS_SQLLEN_INTERPRETATION
      treat_SQLLEN_as_compiled = 1
      Exiting hgoinit, rc=0 at 2012/11/26-11:20:26
      Entered hgolgon at 2012/11/26-11:20:26
      reco:0, name:SA, tflag:0
      Entered hgosuec at 2012/11/26-11:20:26
      Exiting hgosuec, rc=0 at 2012/11/26-11:20:26
      HOSGIP for "HS_FDS_RECOVERY_ACCOUNT" returned "RECOVER"
      HOSGIP for "HS_FDS_TRANSACTION_LOG" returned "HS_TRANSACTION_LOG"
      HOSGIP for "HS_FDS_TIMESTAMP_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_DATE_MAPPING" returned "DATE"
      HOSGIP for "HS_FDS_MAP_NCHAR" returned "TRUE"
      HOSGIP for "HS_FDS_RESULTSET_SUPPORT" returned "FALSE"
      HOSGIP for "HS_FDS_RSET_RETURN_ROWCOUNT" returned "FALSE"
      HOSGIP for "HS_FDS_PROC_IS_FUNC" returned "FALSE"
      HOSGIP for "HS_FDS_REPORT_REAL_AS_DOUBLE" returned "FALSE"
      using SA as default value for "HS_FDS_DEFAULT_OWNER"
      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
      Entered hgocont at 2012/11/26-11:20:26
      HS_FDS_CONNECT_INFO = "CardHolders"
      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
      Entered hgogenconstr at 2012/11/26-11:20:26
      dsn:CardHolders, name:SA
      optn:
      Entered hgocip at 2012/11/26-11:20:26
      dsn:CardHolders
      Exiting hgocip, rc=0 at 2012/11/26-11:20:26
      Exiting hgogenconstr, rc=0 at 2012/11/26-11:20:26


      We configured the odbc.ini, initDB.ora , listener.ora an tnsnmaes.ora as follows.

      [ODBC Data Sources]
      CardHolders = OpenLink SQL Server Lite Driver (multi threaded)

      *** odbc.ini
      [CardHolders]
      Driver = /apex01/oraapex/ODBC_64/lib/sql_mt_lt.so
      ServerType = SQLServer 2000
      Username = SA
      Password = Passw0rd
      Database = CardHolders
      Options = -H 192.168.40.15 -P 1433 -V 9
      FetchBufferSize = 99
      ReadOnly = Yes
      DeferLongFetch =
      JetFix = No
      Description = Sample SQLServer 2000 Lite Connection
      MaxRows =
      NoRowSetSizeLimit = No
      InitialSQL =
      NoAutoCommit = No
      NoLoginBox =
      SqlDbmsName =
      QuotedId = Yes
      AnsiNPW = No

      *** initCardHolders.ora
      HS_FDS_CONNECT_INFO=CardHolders
      HS_FDS_TRACE_LEVEL = DEBUG
      HS_FDS_TRACE_FILE_NAME = CardHolders.trc
      HS_FDS_SHAREABLE_NAME = /apex01/oraapex/ODBC_64/lib/sql_mt_lt.so
      HS_FDS_FETCH_ROWS=1
      HS_LANGUAGE=AMERICAN_AMERICA.WE8ISO8859P1
      HS_FDS_SUPPORT_STATISTICS=FALSE


      *** listener.ora (added)
      (SID_DESC =
      (SID_NAME = CardHolders)
      (ORACLE_HOME= /apex01/oraapex/apexdb/11.2.0.2)
      (PROGRAM = dg4odbc)
      (ENVS=LD_LIBRARY_PATH=/apex01/oraapex/ODBC_64/lib:$ORACLE_HOME/lib)
      )

      *** tnsnames.ora (added)
      CardHolders =
      (DESCRIPTION=
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1530))
      (CONNECT_DATA=(SERVICE_NAME=CardHolders))
      (HS=OK)
      )


      Thanks in advance, any help would be appreciated.
      Regards!!