This discussion is archived
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 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points