1 2 Previous Next 20 Replies Latest reply: Sep 12, 2012 2:20 AM by Mkirtley-Oracle RSS

    Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.

    629826
      Hi everyone
      I'm on oracle 11g 11.1.0.7 (64bit) OEL 5.2 using unixodbc and freetds to connect to SQL Server 2000 following this doc http://www.dbapool.com/articles/06280701.html

      I'm stuck at the listener stage here is my error when i try to start the listener
      TNS-01150: The address of the specified listener name is incorrect

      Listener failed to start. See the error message(s) above...


      Here are my config files:

      /usr/local/etc/freetds.conf:
      # A typical Microsoft server
      [NYCSQL1]
      host = 999.16.0.99
      port = 1433
      tds version = 8.0

      /etc/odbcinst.ini:
      [TDS]
      Description     = FreeTDS driver
      Driver          = /usr/local/lib/libtdsodbc.so
      Setup     = /usr/local/lib/libtdsodbc.so
      Trace          = Yes
      TraceFile     = /tmp/freetds.log
      FileUsage     = 1

      /etc/odbc.ini:
      [NYCSQL1]
      Description          = NYCSQL1
      Driver          = TDS
      Servername     = NYCSQL1
      Server          = NYCSQL1
      Address          = 999.16.0.99
      Port          = 1433
      Database          = db1
      TDS_Version     = 8.0
      Language          = us_english
      TextSize          =
      Domain          = corp.hfmus.com
      PacketSize          =
      Trace          = Yes
      TraceFile          = /tmp/mstest.log

      initnycsql1.ora:
      # This is a sample agent init file that contains the HS parameters that are
      # needed for an ODBC Agent.
      #
      # HS init parameters
      #
      HS_FDS_CONNECT_INFO = NYCSQL1
      #HS_FDS_TRACE_LEVEL = 4
      #HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc
      HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so

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

      #
      # Environment variables required for the non-Oracle system
      #
      #set <envvar>=<value>



      listener.ora:
      LISTENER =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      )
      )
      SID_LIST_NYCSQL1 =
      (SID_LIST =
      (SID_DESC =
      (SID_NAME = NYCSQL1)
      (ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
      (PROGRAM = hsodbc)
      )
      )

      NYCSQL1 =
      (DESCRIPTION_LIST =
      (DESCRIPTION =
      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
      (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))
      )
      )

      hsodbc has been changed to dg4odbc in 11g and i've tried different variations and get the same error.

      Any help appreciated.
      Thank you

      Edited by: ALEXSDBA on Mar 26, 2010 12:59 PM

      Edited by: ALEXSDBA on Mar 26, 2010 1:13 PM
        • 1. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
          Kgronau-Oracle
          check the number of closing brackets in the address list, you close one more than you open in this line (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))


          Correct address list would be:
          NYCSQL1 =
          <space>(DESCRIPTION_LIST =
          <space><space>(DESCRIPTION =
          <space><space><space>(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1522))
          <space><space><space>(ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY))
          <space><space>)
          <space>)

          In addition I would add ENVS parameter to the SID LIST section and add the LD_LIBRARY_PATH containing the ODBC and Oracle libs like:

          SID_LIST_NYCSQL1 =
          <space>(SID_LIST =
          <space><space>(SID_DESC =
          <space><space><space>(SID_NAME = NYCSQL1)
          <space><space><space>(ORACLE_HOME = /u01/app/oracle/product/11.1.0/db_1)
          <space><space><space>(PROGRAM = dg4odbc)
          <space><space><space>(ENVS=LD_LIBRARY_PATH=/usr/local/lib:/u01/app/oracle/product/11.1.0/db_1/lib)
          <space><space>)
          <space>)


          SIDE NOTE:
          Please make sure you've installed the 64bit release of UnixODBC Driver Manager and FreeTDS ODBC driver as you use DG4ODBC on a 64bit platform and dg4odbc on 64bit platforms is 64bit and thus requires 64bit ODBC driver and driver managers.

          To check if you've installed 32bit or 64bit, you can use 'file':
          file /usr/local/lib/libtdsodbc.so
          file /usr/local/lib/libodbc.so
          file /u01/app/oracle/product/11.1.0/db_1/dg4odbc
          • 2. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
            629826
            thank you so much kgronau

            Here the info you requested:
            To check if you've installed 32bit or 64bit, you can use 'file':
            file /usr/local/lib/libtdsodbc.so =/usr/local/lib/libtdsodbc.so: symbolic link to `libtdsodbc.so.0.0.0'

            file /usr/local/lib/libodbc.so = /usr/local/lib/libodbc.so: ERROR: cannot open `/usr/local/lib/libodbc.so' (No such file or directory)
            it's located in /usr/lib64/libodbc.so
            file /u01/app/oracle/product/11.1.0/db_1/dg4odbc is located in /u01/app/oracle/product/11.1.0/db_1/bin/dg4odbc



            Now it works and i can tnsping nycsql1:
            Used TNSNAMES adapter to resolve the alias
            Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522)) (CONNECT_DATA= (SID=nycsql1)) (HS=OK))
            OK (1 msec)

            The problem is when i create the database link and try to connect to the sql server i get an error.

            Here is my database link:
            CREATE PUBLIC DATABASE LINK "HFMHS"
            CONNECT TO "Admin" IDENTIFIED BY 'blahblah' USING 'hfmhs';

            Here is my tnsnames.ora entry for nycsql1:
            hfmhs=
            (DESCRIPTION=
            (ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1522))
            (CONNECT_DATA= (SID=nycsql1))
            (HS=OK)
            )
            So when i issue select * from dual@hfmhs i get this error:

            ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
            ORA-02063: preceding line from HFMHS
            28500. 00000 - "connection from ORACLE to a non-Oracle system returned this message:"
            *Cause:    The cause is explained in the forwarded message.
            *Action:   See the non-Oracle system's documentation of the forwarded
            message.
            Error at Line: 1 Column: 19

            What should i do?
            • 3. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
              Kgronau-Oracle
              Ok. Now you're one step further.
              This error is a generic error message - please enable gateway tracing by setting:
              HS_FDS_TRACE_LEVEL=DEBUG and post the gtaeway trace into the forum thread.
              • 4. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                629826
                it says:


                Oracle Corporation --- MONDAY MAR 29 2010 11:22:23.451


                Heterogeneous Agent Release
                11.1.0.7.0


                Unable to open init file initNYCSQL1.ora in directory /u01/app/oracle/product/11.1.0/db_1

                But this file is located in:

                /u01/app/oracle/product/11.1.0/db_1/hs/admin
                • 5. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                  629826
                  ok i changed the file name form initnycsql1.ora to initNYCSQL1.ora and now i'm getting this:


                  Oracle Corporation --- MONDAY MAR 29 2010 11:33:17.089


                  Heterogeneous Agent Release
                  11.1.0.7.0




                  Oracle Corporation --- MONDAY MAR 29 2010 11:33:17.089

                  Version 11.1.0.7.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_FDS_TRANSACTION_ISOLATION to default of "READ_COMMITTED"
                  setting HS_NLS_NCHAR to default of "AL32UTF8"
                  setting HS_FDS_TIMESTAMP_AS_DATE to default of "TRUE"
                  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_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 "TRUE"
                  Parameter HS_FDS_QUOTE_IDENTIFIER is not set
                  setting HS_CALL_NAME_ISP to "gtw$:SQLTables;gtw$:SQLColumns;gtw$:SQLPrimaryKeys;gtw$:SQLForeignKeys;gtw$:SQLProcedures;gtw$:SQLStatistics"
                  Exiting hgosdip, rc=0
                  ORACLE_SID is "NYCSQL1"
                  Product-Info:
                  Port Rls/Upd:7/0 PrdStat:0
                  Agent:Oracle Database Gateway for ODBC
                  Facility:hsa
                  Class:ODBC, ClassVsn:11.1.0.7.0_0006, Instance:NYCSQL1
                  Exiting hgogprd, rc=0
                  Entered hgoinit
                  HOCXU_COMP_CSET=1
                  HOCXU_DRV_CSET=178
                  HOCXU_DRV_NCHAR=873
                  HOCXU_DB_CSET=178
                  HOCXU_SEM_VER=110000
                  Entered hgolofn at 2010/03/29-11:33:17
                  HOSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/lib/libodbc.so"
                  Entered hgolofns at 2010/03/29-11:33:17
                  hoaerr:28500
                  Exiting hgolofns at 2010/03/29-11:33:17
                  Failed to load ODBC library symbol: /usr/local/lib/libodbc.so(SQLAllocHandle)
                  Exiting hgolofn, rc=28500 at 2010/03/29-11:33:17
                  Exiting hgoinit, rc=28500 at 2010/03/29-11:33:17 with error ptr FILE:hgoinit.c LINE:294 FUNCTION:hgoinit() ID:Loading ODBC aray of function ptrs
                  Entered hgoexit at 2010/03/29-11:33:17
                  • 6. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                    Kgronau-Oracle
                    there seems a missmatch in your configuration ...
                    Accroding to the trace file it doesn't find the ODBC driver manager:
                    OSGIP for "HS_FDS_SHAREABLE_NAME" returned "/usr/local/lib/libodbc.so"
                    Entered hgolofns at 2010/03/29-11:33:17
                    hoaerr:28500
                    Exiting hgolofns at 2010/03/29-11:33:17
                    Failed to load ODBC library symbol: /usr/local/lib/libodbc.so(SQLAllocHandle)


                    Above you've stated:
                    file /usr/local/lib/libodbc.so = /usr/local/lib/libodbc.so: ERROR: cannot open `/usr/local/lib/libodbc.so' (No such file or directory)
                    it's located in /usr/lib64/libodbc.so


                    But it seems you didn't adopt the HS_FDS_SHAREABLE_NAME parameter to HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so

                    So please correct your settings.
                    • 7. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                      629826
                      KGRONAU

                      THANK YOU SO MUCH FOR ALL YOUR HELP I'm able to connect to SQL Server.

                      And now i see:
                      [FreeTDS][SQL Server]Invalid cursor state when i do a simple select col1,col2 from table@hfmhs.
                      It's something in ODBC.

                      weird part is when i use

                      "isql -v nycsql1 user pass"

                      from the db server and issue

                      "select col1,col2 from table" it works fine.

                      Edited by: ALEXSDBA on Mar 29, 2010 4:19 PM
                      • 8. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                        Kgronau-Oracle
                        Which version of FreeTDS are you using?

                        Could you provide an ODBC trace file?
                        ODBC tracing is commonly enabled by adding in the [ODBC] section of the odbc.ini file these parameters:
                        [ODBC]
                        Trace = yes
                        TraceFile = /tmp/odbctrace.out


                        Depending on the driver manager it might happen you have to add these parameters to the odbcinst.ini file - in this case please make sure to add another set command to the gateway init file:
                        set set ODBCINI=/etc/odbcinst.ini
                        • 9. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                          629826
                          I'm using freetds-0.82.

                          I already have trace enabled but never see anything in /tmp folder.
                          And i did change ODBCINI=/etc/odbc.ini to /etc/odbcinst.ini and still same error.
                          I even left both of them on and same error.
                          • 10. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                            Kgronau-Oracle
                            FreeTDS 0.82 should be fine when it is compiled correctly.
                            Please do not change odbc.ini to odbcinst.ini - it is another additional set parameter - make sure you have BOTH are set and both point to valid files.
                            • 11. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                              629826
                              I have them like this:
                              set ODBCINI=/etc/odbc.ini
                              set ODBCINI=/etc/odbcinst.ini


                              still same error
                              • 12. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                                Kgronau-Oracle
                                sorry, my failure.. I copied the wrong entry above.

                                Correct entry should be:
                                set ODBCINI=/etc/odbc.ini
                                set ODBCINSTINI=/etc/odbcinst.ini
                                • 13. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                                  629826
                                  here is the initNYCSQL1.ORA
                                  # This is a sample agent init file that contains the HS parameters that are
                                  # needed for an ODBC Agent.
                                  #
                                  # HS init parameters
                                  #
                                  HS_FDS_CONNECT_INFO = NYCSQL1
                                  HS_FDS_TRACE_LEVEL = DEBUG
                                  #HS_FDS_TRACE_FILE_NAME = /tmp/freetds.trc
                                  #HS_FDS_SHAREABLE_NAME = /usr/local/lib/libodbc.so
                                  HS_FDS_SHAREABLE_NAME=/usr/lib64/libodbc.so
                                  #
                                  # ODBC specific environment variables
                                  #
                                  set ODBCINI=/etc/odbc.ini
                                  set ODBCINSTINI=/etc/odbcinst.ini
                                  #
                                  # Environment variables required for the non-Oracle system
                                  #
                                  #set <envvar>=<value>


                                  should i change it to lowercase initnycsql1.ora?
                                  should i set envvar=?

                                  Thank for all your help i greatly appreciate it.
                                  • 14. Re: Connect with unixodbc and freetds on 64bit OEL 5.2 to SQL Server 2000.
                                    Kgronau-Oracle
                                    No.
                                    Your configuration is working in general. You are trying to get some data from the SQL Server, but the driver reports an issue. Thus we need an ODBC trace.
                                    If this suggestion doesn't work, please contact your ODBC Driver Manager vendor and try to get the details how to enable tracing for their version.

                                    You can also get a trial version of a commercial ODBC driver from DataDirect just to check if your configuration works with this driver.
                                    1 2 Previous Next