1 2 Previous Next 18 Replies Latest reply: Feb 10, 2012 6:50 AM by Kgronau-Oracle RSS

    Again Oracle to MSSQL with Unixodbc and freetds

    916508
      I dont have a clue why my config dont work, so i need you help:

      I connect from SLES 64 bit to a MSSQL 2008R2 Server my system odbc is calles MSSQL and works wit isql -v mssql <user> <pw>.

      I can see an error in my /hs/log like this
      Oracle Corporation --- THURSDAY FEB 09 2012 15:45:07.202


      Heterogeneous Agent Release
      11.2.0.1.0


      HS Agent diagnosed error on initial communication,
      probable cause is an error in network administration
      Network error 2: NCR-00002: NCR: Invalid usage
      HS Gateway: NULL connection context at exit


      Here my config files:
      initdg4odbc.ora

      # This is a sample agent init file that contains the HS parameters that are
      # needed for the Database Gateway for ODBC

      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = mssql
      HS_FDS_TRACE_LEVEL = 4

      ##HS_FDS_SHAREABLE_NAME=/usr/local/lib/libtdsodbc.so

      #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

      #
      # ODBC specific environment variables
      #
      ##set ODBCINI=<full path name of the odbc initilization file>


      #
      # Environment variables required for the non-Oracle system
      #
      ##set <envvar>=<value>
      ##set LIBPATH=/usr/local/lib:

      sqlnet.ora:
      # sqlnet.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora
      # Generated by Oracle configuration tools.
      SQLNET.AUTHENTICATION_SERVICES= (NONE)
      NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

      ADR_BASE = /opt/oracle

      listener.ora

      # listener.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/listener.ora
      # Generated by Oracle configuration tools.

      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.internal.netlogix.de)(PORT = 1521))
      )
      )
      SID_LIST_LISTENER=(SID_DESC=(SID_NAME=orcl)(PROGRAM=dg4odbc))
      ADR_BASE_LISTENER = /opt/oracle


      tnsnames.ora

      # tnsnames.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/tnsnames.ora
      # Generated by Oracle configuration tools.

      ORCL =
      (DESCRIPTION =(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.internal.netlogix.de)(PORT = 1521)))(CONNECT_DATA= (SID=orcl))(HS=OK))



      tnsping orcl resolves like this:

      tnsping orcl

      TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 09-FEB-2012 15:54:40

      Copyright (c) 1997, 2009, Oracle. All rights reserved.

      Used parameter files:
      /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora


      Used TNSNAMES adapter to resolve the alias
      Attempting to contact (DESCRIPTION =(ADDRESS_LIST=(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.internal.netlogix.de)(PORT = 1521)))(CONNECT_DATA= (SID=orcl))(HS=OK))
      OK (0 msec)




      Hope anyone can find my typo. im no oracle guru.

      Tank you
        • 1. Re: Again Oracle to MSSQL with Unixodbc and freetds
          Mkirtley-Oracle
          Hi,
          There are several problems with your configuration -

          1. In listener.ora and tnsnamess.ora you have (SID_NAME=orcl) and (SID=orcl) but you have named the Dg4ODBC config file as initdg4odbc.ora.
          You need to rename this to initorcl.ora or change the sid in the listener and tnsnames to dg4odbc.

          2. In the listener.ora you need to set the ORACLE_HOME variable and it is also advisable to have an ENV setting to give the location of the various library directories. In your case this would be -

          SID_LIST_LISTENER=
          (SID_DESC=
          (SID_NAME=orcl)
          (ORACLE_HOME=/opt/oracle/product/11gR1/db)
          (ENV="LD_LIBRARY_PATH=/usr/local/lib:/opt/oracle/product/11gR1/db/lib")
          (PROGRAM=dg4odbc)
          )

          Change this and stop and start the listener.
          The spaces at the beginning of each line may be lost in this post, so make sure there is at least one space at the beginning of every line, except the first.

          3. Your initdg4odbc.ora file isn't correct either. You have -

          This is a sample agent init file that contains the HS parameters that are
          # needed for the Database Gateway for ODBC

          #
          # HS init parameters
          #
          HS_FDS_CONNECT_INFO = mssql
          HS_FDS_TRACE_LEVEL = 4

          ##HS_FDS_SHAREABLE_NAME=/usr/local/lib/libtdsodbc.so

          #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

          #
          # ODBC specific environment variables
          #
          ##set ODBCINI=<full path name of the odbc initilization file>

          #
          # Environment variables required for the non-Oracle system
          #
          ##set <envvar>=<value>
          ##set LIBPATH=/usr/local/lib:

          - you need to -

          - uncomment the HS_FDS_SHAREABLE_NAME - this must be specified.
          - uncomment "set ODBCINI" and specify the location of the odbc.ini file.

          If you have access to My Oracle Support have a look at this note -

          How to Configure DG4ODBC on 64bit Unix OS (Linux, Solaris, AIX, HP-UX) to Connect to Non-Oracle Databases Post Install (Doc ID 561033.1)

          for more detail on the configuration.

          Regards,
          Mike
          • 2. Re: Again Oracle to MSSQL with Unixodbc and freetds
            916508
            Hi Mike,

            i changed it due to solve my error.
            Thanks for Reply.
            I Changed the following before create the database link: CREATE DATABASE LINK mssql CONNECT TO "useridonsql" IDENTIFIED BY "passwordofsqluser" using 'orcl';
            select * from "person"."address"@mssql

            ERROR at line 1:
            ORA-28546: connection initialization failed, probable Net8 admin error
            ORA-28511: lost RPC connection to heterogeneous remote agent using
            SID=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=oracle.internal.netl
            ogix.de)(PORT=1521)))(CONNECT_DATA=(SID=orcl)))
            ORA-02063: preceding 2 lines from MSSQL



            Updated config files:
            initorcl.ora

            # This is a sample agent init file that contains the HS parameters that are
            # needed for the Database Gateway for ODBC

            #
            # HS init parameters
            #
            HS_FDS_CONNECT_INFO = mssql
            HS_FDS_TRACE_LEVEL = 4
            ##HS_FDS_RECOVERY_ACCOUNT=*****
            ##HS_FDS_RECOVERY_PWD=*****
            HS_FDS_SHAREABLE_NAME=/usr/local/lib/libtdsodbc.so

            #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

            #
            # ODBC specific environment variables
            #
            set ODBCINI=/etc/unixODBC/odbc.ini


            #
            # Environment variables required for the non-Oracle system
            #
            ##set <envvar>=<value>
            ##set LIBPATH=/usr/local/lib:

            listner.ora

            # listener.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/listener.ora
            # Generated by Oracle configuration tools.

            LISTENER =
            (DESCRIPTION_LIST =
            (DESCRIPTION =
            (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
            (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.internal.netlogix.de)(PORT = 1521))
            )
            )
            SID_LIST_LISTENER=
            (SID_DESC=
            (SID_NAME=orcl)
            (ORCL_HOME=/opt/oracle/product/11gR1/db)
            (ENV="LD_LIBRARY_PATH=/usr/local/lib:/opt/oracle/product/11gR1/db/lib")
            (PROGRAM=dg4odbc)
            )
            ADR_BASE_LISTENER = /opt/oracle

            tnsnames.ora

            # tnsnames.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/tnsnames.ora
            # Generated by Oracle configuration tools.

            ORCL =
            (DESCRIPTION =
            (ADDRESS_LIST=
            (ADDRESS =
            (PROTOCOL = TCP)
            (HOST = oracle.internal.netlogix.de)
            (PORT = 1521)))
            (CONNECT_DATA= (
            SID=orcl))
            (HS=OK))


            With all spaces. Error in HS/Log ist gone.

            odbc.ini
            oracle@oracle:/etc/unixODBC> cat odbc.ini
            [mssql]
            Description = libtds
            Driver = libtds
            Servername =
            Server =
            Address = 192.168.21.29
            Port = 1433
            Database =
            TDS_Version = 7.2
            Language = us_english
            TextSize =
            PacketSize =
            ClientCharset =
            DumpFile =
            DumpFileAppend = Yes
            DebugFlags =
            Encryption = off

            i try to read the document you posted but its hard to do everything right.

            Maybe you see another error or have an troubleshooting tipp.

            thx

            Edited by: 913505 on 09.02.2012 07:59
            /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora


            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP) (HOST = oracle.internal.netlogix.de) (PORT = 1521))) (CONNECT_DATA= ( SID=orcl)) (HS=OK))
            OK (0 msec)
            • 3. Re: Again Oracle to MSSQL with Unixodbc and freetds
              Mkirtley-Oracle
              Hi,
              The configuration looks a little better not but there are still a few things that do not look correct.

              1. In initorcl.ora you have -

              HS_FDS_SHAREABLE_NAME=/usr/local/lib/libtdsodbc.so

              Is the file libtdsodbc.so a driver manager ? From where did you download it ? HS_FDS_SHAREABLE_NAME should point to a driver manager unless the ODBC driver you are using also handle the driver manager functions. I don't think the FreeTDS driver does that, so confirm libtdsodbc.so is a driver manager.

              2. In odbc.ini you have -


              Driver = libtds

              but this should be the full path name to the driver. For example, on ou test systems this is -

              Driver = /home/odbc64/FreeTDS/0.82/lib/libtdsodbc.so

              but it depends where you have installed the ODBC driver.
              You also need to specify the following values -

              Server
              Database

              if you still have problems then set the gateway trace level as follows in initorcl.ora -

              HS_FDS_TRACE_LEVEL=debug

              run a select from a new SQLPLUS session, end the session then post the trace file, or at least the portion that shows definite errors.
              the trace file will be in /opt/oracle/product/11gR1/db/hs/log.

              Regards,
              Mike
              • 4. Re: Again Oracle to MSSQL with Unixodbc and freetds
                Mkirtley-Oracle
                Hi,
                Looking at our setup then libtdsodbc.so looks to be the actual FreeTDS driver, so change the HS_FDS_SHAREABLE_NAME to point to a driver manager, for example from UnixODBC and see what happens after making all the necessary changes.
                the UnicODBC driver manager is usually calaled libodbc.so.

                Regards,
                Mike
                • 5. Re: Again Oracle to MSSQL with Unixodbc and freetds
                  916508
                  Hi mike thx for quick reply the hole evening,

                  i did some changes and still only recive the following error:

                  oracle@oracle:~/product/11gR1/db/hs/log> cat orcl_agt_13689.trc


                  Oracle Corporation --- THURSDAY FEB 09 2012 17:49:43.444


                  Heterogeneous Agent Release
                  11.2.0.1.0


                  HS Agent diagnosed error on initial communication,
                  probable cause is an error in network administration
                  Network error 2: NCR-00002: NCR: Invalid usage
                  HS Gateway: NULL connection context at exit



                  Here my changes:

                  oracle@oracle:/etc/unixODBC> cat odbc.ini
                  [mssql]
                  Description = libtds
                  Driver = /usr/local/lib/libtdsodbc.so
                  Servername =
                  Server = 192.168.21.29
                  Address = 192.168.21.29
                  Port = 1433
                  Database = AdventureWorks
                  TDS_Version = 7.2
                  Language = us_english
                  TextSize =
                  PacketSize =
                  ClientCharset =
                  DumpFile =
                  DumpFileAppend = Yes
                  DebugFlags =
                  Encryption = off

                  (odbc ini was created by odbc manager from unixodbc (ODBCConfig))


                  oracle@oracle:~/product/11gR1/db/network/admin> cat listener.ora
                  # listener.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/listener.ora
                  # Generated by Oracle configuration tools.

                  LISTENER =
                  ..(DESCRIPTION_LIST =
                  ....(DESCRIPTION =
                  .....(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                  .....(ADDRESS = (PROTOCOL = TCP)(HOST = oracle.internal.netlogix.de)(PORT = 1521))
                  ....)
                  ..)
                  SID_LIST_LISTENER=
                  .(SID_DESC=
                  ..(SID_NAME=orcl)
                  ..(ORCL_HOME=/opt/oracle/product/11gR1/db)
                  ..(ENV="LD_LIBRARY_PATH=/usr/local/lib:/opt/oracle/product/11gR1/db/lib")
                  ..(PROGRAM=dg4odbc)
                  .)
                  ADR_BASE_LISTENER = /opt/oracle

                  dots are spaces maybe its just a typo in some of the files.

                  oracle@oracle:~/product/11gR1/db/network/admin> cat tnsnames.ora
                  # tnsnames.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/tnsnames.ora
                  # Generated by Oracle configuration tools.

                  ORCL =
                  .(DESCRIPTION =
                  .(ADDRESS_LIST=
                  ..(ADDRESS =
                  ....(PROTOCOL = TCP)
                  ....(HOST = oracle.internal.netlogix.de)
                  ....(PORT = 1521)))
                  ....(CONNECT_DATA=
                  .......(SID=orcl))
                  .......(HS=OK))

                  oracle@oracle:~/product/11gR1/db/network/admin> tnsping orcl

                  TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 09-FEB-2012 18:00:11

                  Copyright (c) 1997, 2009, Oracle. All rights reserved.

                  Used parameter files:
                  /opt/oracle/product/11gR1/db/network/admin/sqlnet.ora


                  Used TNSNAMES adapter to resolve the alias
                  Attempting to contact (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP) (HOST = oracle.internal.netlogix.de) (PORT = 1521))) (CONNECT_DATA= (SID=orcl)) (HS=OK))
                  OK (10 msec)

                  oracle@oracle:~/product/11gR1/db/hs/admin> cat initorcl.ora
                  # This is a sample agent init file that contains the HS parameters that are
                  # needed for the Database Gateway for ODBC

                  #
                  # HS init parameters
                  #
                  HS_FDS_CONNECT_INFO = mssql
                  HS_FDS_TRACE_LEVEL = debug
                  ##HS_FDS_RECOVERY_ACCOUNT=
                  ##HS_FDS_RECOVERY_PWD=
                  HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

                  #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

                  #
                  # ODBC specific environment variables
                  #
                  set ODBCINI=/etc/unixODBC/odbc.ini


                  #
                  # Environment variables required for the non-Oracle system
                  #
                  ##set <envvar>=<value>
                  ##set LIBPATH=/usr/local/lib:



                  oracle@oracle:~/product/11gR1/db/network/admin> ls /usr/lib64/ |grep odbc
                  libodbccr.la
                  libodbccr.so
                  libodbccr.so.1
                  libodbccr.so.1.0.0
                  libodbcinst.la
                  libodbcinstQ.la
                  libodbcinstQ.so
                  libodbcinstQ.so.1
                  libodbcinstQ.so.1.0.0
                  libodbcinst.so
                  libodbcinst.so.1
                  libodbcinst.so.1.0.0
                  libodbc.la
                  libodbc.so <--- i point to this odbc manager
                  libodbc.so.1
                  libodbc.so.1.0.0
                  libtdsodbc.so <- just a link


                  Kind regards
                  • 6. Re: Again Oracle to MSSQL with Unixodbc and freetds
                    Kgronau-Oracle
                    Looks like your're missing SID_LIST in your listener.ora file (and you need to specify ORACLE_HOME correctly):
                    The correct SID saction looks like:

                    SID_LIST_LISTENER=
                    . (SID_LIST
                    ..(SID_DESC=
                    ...(SID_NAME=orcl)
                    ...(ORACLE_HOME=/opt/oracle/product/11gR1/db)
                    ...(ENV="LD_LIBRARY_PATH=/usr/local/lib:/opt/oracle/product/11gR1/db/lib")
                    ...(PROGRAM=dg4odbc)
                    ..)
                    .)

                    Now stop and start the gateway listener: lsnrctl stop and lsnrctl start => check out the status summaryas it should list at least the ORCL service in status unknown with 1 service handler.

                    After stopping and starting the listener, please try again.
                    • 7. Re: Again Oracle to MSSQL with Unixodbc and freetds
                      916508
                      Hi and thanks in advance for solve the next typo... man im glad to report that the error has changed.

                      now i recieve a trclike this:

                      Oracle Corporation --- FRIDAY FEB 10 2012 09:37:49.155


                      Heterogeneous Agent Release
                      11.2.0.1.0




                      Oracle Corporation --- FRIDAY FEB 10 2012 09:37:49.155

                      Version 11.2.0.1.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"
                      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_CHARACTER_SEMANTICS 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"
                      setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
                      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 "orcl"
                      Product-Info:
                      Port Rls/Upd:1/0 PrdStat:0
                      Agent:Oracle Database Gateway for ODBC
                      Facility:hsa
                      Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:orcl
                      Exiting hgogprd, rc=0
                      Entered hgoinit
                      HOCXU_COMP_CSET=1
                      HOCXU_DRV_CSET=873
                      HOCXU_DRV_NCHAR=873
                      HOCXU_DB_CSET=873
                      HOCXU_SEM_VER=112000
                      Entered hgolofn at 2012/02/10-09:37:49
                      HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/lib/libtdsodbc.so.0.0.0"
                      Entered hgolofns at 2012/02/10-09:37:49
                      symbol_peflctx=0x5009ea30
                      hoaerr:0
                      Exiting hgolofns at 2012/02/10-09:37:49
                      Entered hgolofns at 2012/02/10-09:37:49
                      symbol_peflctx=0x500999f0
                      hoaerr:0
                      Exiting hgolofns at 2012/02/10-09:37:49
                      Entered hgolofns at 2012/02/10-09:37:49
                      symbol_peflctx=0x500998a0
                      hoaerr:0
                      Exiting hgolofns at 2012/02/10-09:37:49
                      Entered hgolofns at 2012/02/10-09:37:49
                      symbol_peflctx=0x5009b0e0
                      hoaerr:0
                      Exiting hgolofns at 2012/02/10-09:37:49
                      Entered hgolofns at 2012/02/10-09:37:49
                      hoaerr:28500
                      Exiting hgolofns at 2012/02/10-09:37:49
                      Failed to load ODBC library symbol: /usr/local/lib/libtdsodbc.so.0.0.0(SQLDescribeParam) <----- dont know whats wrong here
                      Exiting hgolofn, rc=28500 at 2012/02/10-09:37:49
                      Exiting hgoinit, rc=28500 with error ptr FILE:hgoinit.c LINE:337 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs
                      Entered hgoexit
                      HS Gateway: NULL connection context at exit
                      Exiting hgoexit, rc=0 with error ptr FILE:hgoexit.c LINE:108 FUNCTION:hgoexit() ID:Connection context





                      my updated config is:
                      oracle@oracle:~/product/11gR1/db/hs/admin> cat initorcl.ora
                      # This is a sample agent init file that contains the HS parameters that are
                      # needed for the Database Gateway for ODBC

                      #
                      # HS init parameters
                      #
                      HS_FDS_CONNECT_INFO = mssql
                      HS_FDS_TRACE_LEVEL = debug
                      #HS_FDS_RECOVERY_ACCOUNT=*****
                      #HS_FDS_RECOVERY_PWD=*****
                      HS_FDS_SHAREABLE_NAME=/usr/local/lib/libtdsodbc.so.0.0.0

                      #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

                      #
                      # ODBC specific environment variables
                      #
                      set ODBCINI=/etc/unixODBC/odbc.ini


                      #
                      # Environment variables required for the non-Oracle system
                      #
                      ##set <envvar>=<value>
                      ##set LIBPATH=/usr/local/lib:



                      oracle@oracle:~/product/11gR1/db/network/admin> cat listener.ora
                      # listener.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/listener.ora
                      # Generated by Oracle configuration tools.

                      LISTENER =
                      (DESCRIPTION_LIST =
                      (DESCRIPTION =
                      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
                      (ADDRESS = (PROTOCOL = TCP)(HOST = oracle.internal.netlogix.de)(PORT = 1521))
                      )
                      )
                      SID_LIST_LISTENER=
                      (SID_LIST=
                      (SID_DESC=
                      (SID_NAME=orcl)
                      (ORACLE_HOME=/opt/oracle/product/11gR1/db)
                      (ENV="LD_LIBRARY_PATH=/usr/local/lib:/opt/oracle/product/11gR1/db/lib:/usr/lib64")
                      (PROGRAM=dg4odbc)
                      )
                      )
                      ADR_BASE_LISTENER = /opt/oracle

                      oracle@oracle:~/product/11gR1/db/network/admin> cat tnsnames.ora
                      # tnsnames.ora Network Configuration File: /opt/oracle/product/11gR1/db/network/admin/tnsnames.ora
                      # Generated by Oracle configuration tools.

                      ORCL =
                      (DESCRIPTION =
                      (ADDRESS_LIST=
                      (ADDRESS =
                      (PROTOCOL = TCP)
                      (HOST = oracle.internal.netlogix.de)
                      (PORT = 1521)))
                      (CONNECT_DATA=
                      (SID=orcl))
                      (HS=OK))


                      oracle@oracle:/etc/unixODBC> cat odbc.ini
                      [mssql]
                      Description = libtds
                      Driver = /usr/local/lib/libtdsodbc.so.0.0.0
                      Servername =
                      Server = 192.168.21.29
                      Address = 192.168.21.29
                      Port = 1433
                      Database = AdventureWorks
                      TDS_Version = 7.2
                      Language = us_english
                      TextSize =
                      PacketSize =
                      ClientCharset =
                      DumpFile =
                      DumpFileAppend = Yes
                      DebugFlags =
                      Encryption = off


                      Thank you for your great help hope we can finish this soon :)
                      • 8. Re: Again Oracle to MSSQL with Unixodbc and freetds
                        Kgronau-Oracle
                        There's an issue with the driver manager - the gatewa ytrace reports:
                        HS_FDS_SHAREABLE_NAME" returned "/usr/local/lib/libtdsodbc.so.0.0.0

                        But the config file you posted earlier had:

                        Edited by: kgronau on Feb 10, 2012 9:47 AM

                        HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so


                        => the libodbc library is commonly the ODBC driver manager whereas libtdsodbc is the ODBC driver itself.


                        So please set the parameter in your gateway init file back to:
                        HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
                        • 9. Re: Again Oracle to MSSQL with Unixodbc and freetds
                          916508
                          okay i recieve this
                          setting HS_FDS_CHARACTER_SEMANTICS 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"
                          setting HS_FDS_SUPPORT_STATISTICS to default of "FALSE"
                          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 "orcl"
                          Product-Info:
                          Port Rls/Upd:1/0 PrdStat:0
                          Agent:Oracle Database Gateway for ODBC
                          Facility:hsa
                          Class:ODBC, ClassVsn:11.2.0.1.0_0008, Instance:orcl
                          Exiting hgogprd, rc=0
                          Entered hgoinit
                          HOCXU_COMP_CSET=1
                          HOCXU_DRV_CSET=873
                          HOCXU_DRV_NCHAR=873
                          HOCXU_DB_CSET=873
                          HOCXU_SEM_VER=112000
                          Entered hgolofn at 2012/02/10-10:54:50
                          HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/lib64/libodbc.so"
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbccf38f0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbccf3a80
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbccf4340
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbccf5790
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbccfd6c0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbccfdb80
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd00500
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd01a50
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd022e0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd03d70
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd03d80
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd055d0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd08980
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd08d70
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd0aa40
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd0b730
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd0ba50
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd0da30
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd0de40
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd0fb60
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd0f9b0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd150d0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd16a00
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd17040
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd18550
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd18e90
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1abb0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1b150
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1b9c0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1ce90
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1d3a0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1e530
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1f0c0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd1faa0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd20690
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd20c10
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd21570
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd21ac0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd22080
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd226f0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd22ca0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd244f0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd240f0
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd25960
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Entered hgolofns at 2012/02/10-10:54:50
                          symbol_peflctx=0xbcd26560
                          hoaerr:0
                          Exiting hgolofns at 2012/02/10-10:54:50
                          Exiting hgolofn, rc=0 at 2012/02/10-10:54:50
                          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 2012/02/10-10:54:50
                          Entered hgolgon at 2012/02/10-10:54:50
                          reco:0, name:oracle, tflag:0
                          Entered hgosuec at 2012/02/10-10:54:50
                          Exiting hgosuec, rc=0 at 2012/02/10-10:54:50
                          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_CHARACTER_SEMANTICS" returned "FALSE"
                          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 oracle as default value for "HS_FDS_DEFAULT_OWNER"
                          HOSGIP for "HS_SQL_HANDLE_STMT_REUSE" returned "FALSE"
                          Entered hgocont at 2012/02/10-10:54:50
                          HS_FDS_CONNECT_INFO = "mssql"
                          RC=-1 from HOSGIP for "HS_FDS_CONNECT_STRING"
                          Entered hgogenconstr at 2012/02/10-10:54:50
                          dsn:mssql, name:oracle
                          optn:
                          Entered hgocip at 2012/02/10-10:54:50
                          dsn:mssql
                          Exiting hgocip, rc=0 at 2012/02/10-10:54:50
                          Exiting hgogenconstr, rc=0 at 2012/02/10-10:54:50
                          Entered hgopoer at 2012/02/10-10:54:50
                          hgopoer, line 233: got native error 0 and sqlstate I; message follows...
                          [
                          Exiting hgopoer, rc=0 at 2012/02/10-10:54:50
                          hgocont, line 2752: calling SqlDriverConnect got sqlstate I
                          Exiting hgocont, rc=28500 at 2012/02/10-10:54:50 with error ptr FILE:hgocont.c LINE:2772 FUNCTION:hgocont() ID:Something other than invalid authorization
                          Exiting hgolgon, rc=28500 at 2012/02/10-10:54:50 with error ptr FILE:hgolgon.c LINE:781 FUNCTION:hgolgon() ID:Calling hgocont
                          Entered hgoexit at 2012/02/10-10:54:50
                          Exiting hgoexit, rc=0


                          extproc.ora initorcl.ora listener.ora.sample tnsnames.ora.sample
                          oracle@oracle:~/product/11gR1/db/hs/admin> cat initorcl.ora
                          # This is a sample agent init file that contains the HS parameters that are
                          # needed for the Database Gateway for ODBC

                          #
                          # HS init parameters
                          #
                          HS_FDS_CONNECT_INFO = mssql
                          HS_FDS_TRACE_LEVEL = debug
                          #HS_FDS_RECOVERY_ACCOUNT=oracle
                          #HS_FDS_RECOVERY_PWD=novell
                          HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

                          ###HS_FDS_SHAREABLE_NAME=/usr/local/lib/libtdsodbc.so.0.0.0
                          #HS_FDS_SHAREABLE_NAME = <full path name of odbc driver manager or driver>

                          #
                          # ODBC specific environment variables
                          #
                          set ODBCINI=/etc/unixODBC/odbc.ini


                          #
                          # Environment variables required for the non-Oracle system
                          #
                          ##set <envvar>=<value>
                          ##set LIBPATH=/usr/local/lib:



                          oracle@oracle:/etc/unixODBC> cat odbc.ini
                          [mssql]
                          Description = libtds
                          Driver = /usr/local/lib/libtdsodbc.so.0.0.0
                          Servername =
                          Server = 192.168.21.29
                          Address = 192.168.21.29
                          Port = 1433
                          Database =AdventureWorks
                          TDS_Version = 7.2
                          Language = us_english
                          TextSize =
                          PacketSize =
                          ClientCharset =
                          DumpFile =
                          DumpFileAppend = Yes
                          DebugFlags =
                          Encryption = off


                          I am not shure if the lib is correct ...
                          • 10. Re: Again Oracle to MSSQL with Unixodbc and freetds
                            Kgronau-Oracle
                            what's the ODBC driver manager version (odbcinst --version)?
                            Older UnixODBC driver managers (<2.3) had some issues when the ODBC driver tried to use unicode connect strings and HS_LANGUAGE=american_america.we8iso8859p1 had to be added to the gateway init file.
                            • 11. Re: Again Oracle to MSSQL with Unixodbc and freetds
                              916508
                              Man i think you are close to my success ;)
                              now i fetched 100 Rows but a conversation error occours.

                              SQL> select * from "person"."address"@mssql;
                              ERROR:
                              ORA-28528: Heterogeneous Services datatype conversion error
                              ORA-02063: preceding line from MSSQL



                              no rows selected


                              trc: at the end
                              SQLFetch: row: 99, column 1, bflsz: 4, bflar: 0
                              SQLFetch: row: 99, column 1, bflsz: 4, bflar: 0, (bfl: 4, mbl: 4)
                              SQLFetch: row: 99, column 2, bflsz: 121, bflar: 0
                              SQLFetch: row: 99, column 2, bflsz: 121, bflar: 0, (bfl: 0, mbl: 120)
                              SQLFetch: row: 99, column 3, bflsz: 121, bflar: 0
                              SQLFetch: row: 99, column 3, bflsz: 121, bflar: 0, (bfl: 0, mbl: 120)
                              SQLFetch: row: 99, column 4, bflsz: 61, bflar: 0
                              SQLFetch: row: 99, column 4, bflsz: 61, bflar: 0, (bfl: 0, mbl: 60)
                              SQLFetch: row: 99, column 5, bflsz: 4, bflar: 0
                              SQLFetch: row: 99, column 5, bflsz: 4, bflar: 0, (bfl: 4, mbl: 4)
                              SQLFetch: row: 99, column 6, bflsz: 31, bflar: 0
                              SQLFetch: row: 99, column 6, bflsz: 31, bflar: 0, (bfl: 0, mbl: 30)
                              SQLFetch: row: 99, column 7, bflsz: 37, bflar: 0
                              SQLFetch: row: 99, column 7, bflsz: 37, bflar: 0, (bfl: 0, mbl: 36)
                              SQLFetch: row: 99, column 8, bflsz: 16, bflar: 0
                              SQLFetch: row: 99, column 8, bflsz: 16, bflar: 0, (bfl: 0, mbl: 16)
                              SQLFetch: row: 100, column 1, bflsz: 4, bflar: 0
                              SQLFetch: row: 100, column 1, bflsz: 4, bflar: 0, (bfl: 4, mbl: 4)
                              SQLFetch: row: 100, column 2, bflsz: 121, bflar: 0
                              SQLFetch: row: 100, column 2, bflsz: 121, bflar: 0, (bfl: 0, mbl: 120)
                              SQLFetch: row: 100, column 3, bflsz: 121, bflar: 0
                              SQLFetch: row: 100, column 3, bflsz: 121, bflar: 0, (bfl: 0, mbl: 120)
                              SQLFetch: row: 100, column 4, bflsz: 61, bflar: 0
                              SQLFetch: row: 100, column 4, bflsz: 61, bflar: 0, (bfl: 0, mbl: 60)
                              SQLFetch: row: 100, column 5, bflsz: 4, bflar: 0
                              SQLFetch: row: 100, column 5, bflsz: 4, bflar: 0, (bfl: 4, mbl: 4)
                              SQLFetch: row: 100, column 6, bflsz: 31, bflar: 0
                              SQLFetch: row: 100, column 6, bflsz: 31, bflar: 0, (bfl: 0, mbl: 30)
                              SQLFetch: row: 100, column 7, bflsz: 37, bflar: 0
                              SQLFetch: row: 100, column 7, bflsz: 37, bflar: 0, (bfl: 0, mbl: 36)
                              SQLFetch: row: 100, column 8, bflsz: 16, bflar: 0
                              SQLFetch: row: 100, column 8, bflsz: 16, bflar: 0, (bfl: 0, mbl: 16)
                              100 rows fetched



                              My MSSQL Codepage is LAtin 1 General, so it should be something like
                              HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252
                              • 12. Re: Again Oracle to MSSQL with Unixodbc and freetds
                                916508
                                I tried a Sting value:

                                Select "City" from "Person"."Address"@mssql;

                                and it works!!

                                MAn great job, but have you a hint for the Codepage problem?

                                Kind Regards and thanks alot.

                                Edited by: 913505 on 10.02.2012 02:45
                                Codepage of the Database: Latin1_General_CI_AS

                                USE [AdventureWorks]
                                GO

                                /****** Object: Table [Person].[Address] Script Date: 02/10/2012 11:46:23 ******/
                                SET ANSI_NULLS ON
                                GO

                                SET QUOTED_IDENTIFIER ON
                                GO

                                CREATE TABLE [Person].[Address](
                                     [AddressID] [int] IDENTITY(1,1) NOT NULL,
                                     [AddressLine1] [nvarchar](60) NOT NULL,
                                     [AddressLine2] [nvarchar](60) NULL,
                                     [City] [nvarchar](30) NOT NULL,
                                     [StateProvinceID] [int] NOT NULL,
                                     [PostalCode] [nvarchar](15) NOT NULL,
                                     [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
                                     [ModifiedDate] [datetime] NOT NULL,
                                CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
                                (
                                     [AddressID] ASC
                                )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                ) ON [PRIMARY]
                                • 13. Re: Again Oracle to MSSQL with Unixodbc and freetds
                                  Kgronau-Oracle
                                  For MS SQL Server using Latin-1 you can use HS_LANGUAGE=AMERICAN_AMERICA.WE8MSWIN1252.

                                  Regarding the data type conversation error it might have to do with the bulk fetching of DG4ODBC. Give it a try by adding: HS_FDS_FETCH_ROWS=1to the gateway init file - make sure you open a new SQL*Plus session afterwards.
                                  If it continues to fail, it looks like a data type conflict. What is the source table definition (just paste the create table statement of your SQL Server table)

                                  Edited by: kgronau on Feb 10, 2012 11:53 AM
                                  One thing I forgot to ask: What's your FreeTDS ODBC driver version?
                                  • 14. Re: Again Oracle to MSSQL with Unixodbc and freetds
                                    916508
                                    my freetds version is freetds-0.91.

                                    im not shure but if i select every column on its own it works so i dont think its a conversation error.
                                    but if i do a select * from "person"."address"@mssql; i get this error.

                                    can you tell me how i can select more than one column?

                                    my table is created as following:

                                    USE [AdventureWorks]
                                    GO

                                    /****** Object: Table [Person].[Address] Script Date: 02/10/2012 11:46:23 ******/
                                    SET ANSI_NULLS ON
                                    GO

                                    SET QUOTED_IDENTIFIER ON
                                    GO

                                    CREATE TABLE [Person].[Address](
                                         [AddressID] [int] IDENTITY(1,1) NOT NULL,
                                         [AddressLine1] [nvarchar](60) NOT NULL,
                                         [AddressLine2] [nvarchar](60) NULL,
                                         [City] [nvarchar](30) NOT NULL,
                                         [StateProvinceID] [int] NOT NULL,
                                         [PostalCode] [nvarchar](15) NOT NULL,
                                         [rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL,
                                         [ModifiedDate] [datetime] NOT NULL,
                                    CONSTRAINT [PK_Address_AddressID] PRIMARY KEY CLUSTERED
                                    (
                                         [AddressID] ASC
                                    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
                                    ) ON [PRIMARY]
                                    1 2 Previous Next