7 Replies Latest reply: Apr 9, 2013 4:00 PM by 1000810 RSS

    Problem establish Oracle Database Gateway for SQL Server

    1000810
      I have configured dblink between Oracle and sqlserver using "Oracle Database Gateway for SQL Server" method.

      I am using dg4odbc and freetds, following Doc ID 561033.1.

      I have my isql, tsql and osql working properly.
      But getting below error when query any table/column from Oracle sql*plus to the sql server database.

      select Address_Type from Address@ITSMSP;

      ORA-28511: lost RPC connection to heterogeneous remote agent using SID=ORA-28511: lost RPC
      connection to heterogeneous remote agent using
      SID=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))(CONNECT_DATA=(SID=ITSM-Prod)))
      ORA-02063: preceding line from ITSMSP


      As per below trace seems after establish the connection it's failing.
      I am getting this error with every table and/or column selection.
      I already prepare the DBlink user id and password with double quote and link with single quote.
      One thing puzzle me when I am closing my session it's asking for commit or rollback.
      Although except select I am not doing anything.
      I already tried by setting autocommit on, but still getting same error.


      We have establish oracle database gateway for sql server before. this time few things are different like:
      1. The server name(of sql server database) contain a "-" hyphen.
      2. Sql server database name also contain a "-" hyphen.
      2. sql server database in 64-bit windows OS, prior we used 32-bit.

      Please let me know if anyone establish Oracle database gateway using dg4odbc and freetds to sql server, where sql server database reside in 64-bit windows machine.


      Below is the trace snapshot: remove server name with ****

      Oracle Corporation --- TUESDAY APR 02 2013 21:15:38.096

      Heterogeneous Agent Release
      11.2.0.3.0

      Oracle Corporation --- TUESDAY APR 02 2013 21:15:38.096

      Version 11.2.0.3.0

      Entered hgogprd
      HOSGIP for "HS_FDS_TRACE_LEVEL" returned "4"
      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"
      setting HS_FDS_FETCH_ROWS to default of "100"
      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
      setting HS_FDS_QUOTE_IDENTIFIER to default of "TRUE"
      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 "****-****"
      Product-Info:
      Port Rls/Upd:3/0 PrdStat:0
      Agent:Oracle Database Gateway for ODBC
      Facility:hsa
      Class:ODBC, ClassVsn:11.2.0.3.0_0011, Instance:****-****
      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
      LANG=en_US.UTF-8
      HOCXU_SEM_VER=112000
      Entered hgolofn at 2013/04/02-21:15:38
      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLAllocHandle
      symbol_peflctx=0x2ca08300
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLBindCol
      symbol_peflctx=0x2ca08390
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLBindParameter
      symbol_peflctx=0x2ca08b50
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLCancel
      symbol_peflctx=0x2ca09dc0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLDescribeParam
      symbol_peflctx=0x2ca11890
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLDisconnect
      symbol_peflctx=0x2ca11d30
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLEndTran
      symbol_peflctx=0x2ca14580
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLExecute
      symbol_peflctx=0x2ca15a20
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLFetch
      symbol_peflctx=0x2ca16220
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLFreeHandle
      symbol_peflctx=0x2ca17c40
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLFreeStmt
      symbol_peflctx=0x2ca17c60
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetData
      symbol_peflctx=0x2ca19370
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetEnvAttr
      symbol_peflctx=0x2ca1c4f0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetFunctions
      symbol_peflctx=0x2ca1c890
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLMoreResults
      symbol_peflctx=0x2ca1e330
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLNumResultCols
      symbol_peflctx=0x2ca1ef80
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLParamData
      symbol_peflctx=0x2ca1f280
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLPutData
      symbol_peflctx=0x2ca210b0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLRowCount
      symbol_peflctx=0x2ca21480
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLSetEnvAttr
      symbol_peflctx=0x2ca22fc0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLSetDescRec
      symbol_peflctx=0x2ca22e10
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLColAttribute
      symbol_peflctx=0x2ca0a3e0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLColumns
      symbol_peflctx=0x2ca0bb80
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLConnect
      symbol_peflctx=0x2ca0f4f0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLDescribeCol
      symbol_peflctx=0x2ca11150
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLDriverConnect
      symbol_peflctx=0x2ca12890
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLExecDirect
      symbol_peflctx=0x2ca154b0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLForeignKeys
      symbol_peflctx=0x2ca16b40
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetConnectAttr
      symbol_peflctx=0x2ca17fb0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetDescField
      symbol_peflctx=0x2ca199d0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetDescRec
      symbol_peflctx=0x2ca19e90
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetDiagField
      symbol_peflctx=0x2ca1b060
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetDiagRec
      symbol_peflctx=0x2ca1bc90
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetInfo
      symbol_peflctx=0x2ca1cad0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetStmtAttr
      symbol_peflctx=0x2ca1d4d0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLGetTypeInfo
      symbol_peflctx=0x2ca1df90
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLPrepare
      symbol_peflctx=0x2ca1f9b0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLPrimaryKeys
      symbol_peflctx=0x2ca1fec0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLProcedureColumns
      symbol_peflctx=0x2ca20470
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLProcedures
      symbol_peflctx=0x2ca20b00
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLSetConnectAttr
      symbol_peflctx=0x2ca21770
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLSetStmtAttr
      symbol_peflctx=0x2ca24320
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLSetDescField
      symbol_peflctx=0x2ca22b30
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLStatistics
      symbol_peflctx=0x2ca258c0
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Entered hgolofns at 2013/04/02-21:15:38
      libname=/usr/lib64/libodbc.so, funcname=SQLTables
      symbol_peflctx=0x2ca26550
      hoaerr:0
      Exiting hgolofns at 2013/04/02-21:15:38
      Exiting hgolofn, rc=0 at 2013/04/02-21:15:38
      HOSGIP for "HS_OPEN_CURSORS" returned "50"
      HOSGIP for "HS_FDS_FETCH_ROWS" returned "100"
      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 2013/04/02-21:15:38
      Entered hgolgon at 2013/04/02-21:15:38
      reco:0, name:********, tflag:0
      Entered hgosuec at 2013/04/02-21:15:38
      Exiting hgosuec, rc=0 at 2013/04/02-21:15:38
      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 ******** as default value for "HS_FDS_DEFAULT_OWNER"
      HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
      Entered hgocont at 2013/04/02-21:15:38
      HS_FDS_CONNECT_INFO = "****-****"
      RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
      Entered hgogenconstr at 2013/04/02-21:15:38
      dsn:*****-****, name:********
      optn:
      Entered hgocip at 2013/04/02-21:15:38
      dsn:****-****
      Exiting hgocip, rc=0 at 2013/04/02-21:15:38
      ##>Connect Parameters (len=39)<##
      ## DSN=****-****;
      #! UID=********;
      #! PWD=*
      Exiting hgogenconstr, rc=0 at 2013/04/02-21:15:38
      Entered hgolosf at 2013/04/02-21:15:38
      ODBC Function-Available-Array 0xFFFE 0x00FF 0xFF00 0xFB7F 0x03FF 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0x0000 0x0000 0x0000 0x0000
      0x0000 0x0000 0xFE00 0x3F5F
      Exiting hgolosf, rc=0 at 2013/04/02-21:15:38
      DriverName:libtdsodbc.so, DriverVer:0.91
      DBMS Name:Microsoft SQL Server, DBMS Version:95.10.0255
      Exiting hgocont, rc=0 at 2013/04/02-21:15:38
      SQLGetInfo returns Y for SQL_CATALOG_NAME
      SQLGetInfo returns 128 for SQL_MAX_CATALOG_NAME_LEN
      Exiting hgolgon, rc=0 at 2013/04/02-21:15:38
      Entered hgoulcp at 2013/04/02-21:15:38
      Entered hgowlst at 2013/04/02-21:15:38
      Exiting hgowlst, rc=0 at 2013/04/02-21:15:38
      SQLGetInfo returns 0x1f for SQL_OWNER_USAGE
      TXN Capable:2, Isolation Option:0xf
      SQLGetInfo returns 128 for SQL_MAX_SCHEMA_NAME_LEN
      SQLGetInfo returns 128 for SQL_MAX_TABLE_NAME_LEN
      SQLGetInfo returns 134 for SQL_MAX_PROCEDURE_NAME_LEN
      HOSGIP returned value of "TRUE" for HS_FDS_QUOTE_IDENTIFIER
      SQLGetInfo returns " (0x22) for SQL_IDENTIFIER_QUOTE_CHAR
      2 instance capabilities will be uploaded
      capno:1989, context:0x00000000, add-info: 0
      capno:1992, context:0x0001ffff, add-info: 0
      Exiting hgoulcp, rc=0 at 2013/04/02-21:15:38
      Entered hgouldt at 2013/04/02-21:15:38
      NO instance DD translations were uploaded
      Exiting hgouldt, rc=0 at 2013/04/02-21:15:38
      Entered hgobegn at 2013/04/02-21:15:38
      tflag:0 , initial:1
      hoi:0x4093b4c8, ttid (len 38) is ...
      00: 54415058 2E435245 49474854 4F4E2E45 [TAPX]
      10: 44552E33 61383332 3362322E 312E3233 [3a8323b2.1.23]
      20: 2E323037 3631 [.20761]
      tbid (len 35) is ...
      00: 54415058 2E435245 49474854 4F4E2E45 [TAPX]
      10: 44555B31 2E32332E 32303736 315D5B31 [[1.23.20761][1]
      20: 2E345D [.4]]
      Exiting hgobegn, rc=0 at 2013/04/02-21:15:38
      Entered hgodtab at 2013/04/02-21:15:38
      count:1
      table: Address
      Allocate hoada[0] @ 0x14eedc70
      Free hoada[0] @ 0x14eedc70
      SQL text from hgodtab, id=0, len=23 ...
      00: 73656C65 6374202A 2066726F 6D202241 [select * from "A]
      10: 64647265 737322 [ddress"]
      Entered hgodscr_process_sellist_description at 2013/04/02-21:15:38


      Many Thanks for your help,
      Pal

      Edited by: 997807 on Apr 3, 2013 7:26 AM
        • 1. Re: Problem establish Oracle Database Gateway for SQL Server
          Kgronau-Oracle
          In general the connection using FreeTDS works and connects also to a 64bit SQl Server although it isn't the best choice.
          On which platform did you install Dg4ODBC and what ODBC Driver Manager version are you using (odbcinst -j)?
          Could you please post your odbc.ini as well as gateway init file?

          Try adding to the gateway init file the parameter: HS_FDS_SQLLEN_INTERPRETATION=32 and then test again the select. Does it now work or still fail?

          - Klaus
          • 2. Re: Problem establish Oracle Database Gateway for SQL Server
            1000810
            Thanks Klaus for taking time on this.
            I installed the ODBC in Linux.

            odbcinst -j output below and freetds version is 0.91.

            unixODBC 2.2.11
            DRIVERS............: /etc/odbcinst.ini
            SYSTEM DATA SOURCES: /etc/odbc.ini
            USER DATA SOURCES..: /home/oracle/.odbc.ini


            odbc.ini file:

            # ===================================================================
            # - Arrange Data Sources by type, then alpha
            #
            [ODBC Data Sources]
            #
            # --- Sqlserver 2008 here
            #
            TESTDB=Sqlserver 2008 connection
            #
            # ===================================================================
            # - All packets in alpha order
            #
            [ITSM-Prod]
            Driver = TDS
            Description = ITSM database
            Trace = Yes
            TraceFile = /tmp/hs_itsm.log
            Database = ITSM-Prod
            Server = ****-**
            Address = *.*.*.*
            Port = 1433
            QuotedId = YES
            AnsiNPW = YES

            Gateway init file:initITSM-Prod.ora

            # HS init parameters
            #
            HS_FDS_CONNECT_INFO = ITSM-Prod
            # debug #
            HS_FDS_TRACE_LEVEL = 4
            #HS_FDS_TRACE_LEVEL = OFF
            HS_FDS_TRACE_FILE_NAME = $ORACLE_HOME/hs/admin/trace.log
            #HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so.1.0.0
            HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so
            HS_FDS_SUPPORT_STATISTICS = FALSE
            #HS_LANGUAGE = american_america.al32utf8
            HS_LANGUAGE = american_america.we8iso8859p1
            HS_FDS_SQLLEN_INTERPRETATION=32
            #HS_FDS_DELAYED_OPEN=FALSE
            #
            ODBCINI = /etc/odbc.ini
            ODBCINST = /etc/odbcinst.ini
            #


            tried with HS_FDS_SQLLEN_INTERPRETATION=32, but still getting same error.
            by any chance am I missing any bug or compatibility issue between freetds v0.91 and unixODBC 2.2.11.
            prior we used freetds 0.63, this will be first time with v0.91.

            Also any idea if hyphen in server name and database name can be an issue.

            Many thanks,
            Pal
            • 3. Re: Problem establish Oracle Database Gateway for SQL Server
              Mkirtley-Oracle
              Hi,
              Thanks for the information. The UnixODBC 2.2 driver manager is quite old and there is now a later 2.3.1 version available from here -

              http://www.unixodbc.org/

              by clicking on the 'Download' tab.
              Could you download and install that version then use in the setup instead of the 2.2 version ?

              Regards,
              Mike
              • 4. Re: Problem establish Oracle Database Gateway for SQL Server
                Mkirtley-Oracle
                Hi - I forgot to add that having a hyphen in the server or database will not cause any problems.

                Regards,
                Mike
                • 5. Re: Problem establish Oracle Database Gateway for SQL Server
                  1000810
                  Thanks Mike.
                  As we don't have control over odbc driver in production, so installing new version of odbc will require extra permissions.
                  is there any reason to avoid unixODBC 2.2.11?
                  that version is working in other environments.
                  Thanks,
                  Susmita
                  • 6. Re: Problem establish Oracle Database Gateway for SQL Server
                    Kgronau-Oracle
                    Hi Susmita,
                    yes there is - unixODBC 2.2.11 had only a very poor ODBC level 3 64bit implementation. Depending on how the driver manager was installed/compiled it used on 64bit systems the 32bit ODBC level 3 standard. Starting with unixODBC 2.3 the compilation of the driver manager was changed and it is now properly supporting the 64bit ODBC level 3 standard.

                    - Klaus
                    • 7. Re: Problem establish Oracle Database Gateway for SQL Server
                      1000810
                      Billions thanks to Mike and Klaus.
                      It's started working perfectly with unixODBC 2.3.1.

                      Thanks,
                      Susmita Pal