Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2011
    Posts
    5

    Unanswered: Oracle Gateway to MS Sql server returns error when querying a table

    Hi,

    Our requirement is to query MS SQL Server database tables from EBS DB 11.2.0.3. We have configured DataDirect MS SQL Server Wire protocol ODBC driver on our AIX server to connect to MS SQL Server 2012. When querying a table from sqlplus we are facing the below error

    SQL> select count(*) from "tab"@DBLINK;
    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [DataDirect][ODBC SQL Server Wire Protocol driver][Microsoft SQL
    Server]Incorrect syntax near 'TAB'. {HY000,NativeErr = 102}
    ORA-02063: preceding 2 lines from DBLINK

    ENV :


    OS : AIS 6.1 TL 2

    R12.1.3 on 11.2.0.3



    cd $ORACLE_HOME/hs/admin

    $ cat initEnterprise_Access.ora

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO =mssqlserver
    HS_FDS_TRACE_LEVEL = off
    HS_FDS_SHAREABLE_NAME = /opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/odbc.so
    #
    # ODBC specific environment variables
    #
    set ODBCINI=/opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini
    #
    # Environment variables required for the non-Oracle system
    #
    #set <envvar>=<value>

    $




    cat /opt/Progress/DataDirect/Connect64_for_ODBC_71/odbc.ini
    [ODBC Data Sources]
    SQL Server Legacy Wire Protocol=DataDirect 7.1 SQL Server Legacy Wire Protocol

    [ODBC]
    IANAAppCodePage=4
    InstallDir=/opt/Progress/DataDirect/Connect64_for_ODBC_71
    Trace=0
    TraceFile=odbctrace.out
    TraceDll=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddtrc27.so

    [mssqlserver]
    Driver=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib/ddsqls27.so
    HostName=10.0.9.10
    Database=Enterprise_Access
    PortNumber=1433
    EnableQuotedIdentifers=1
    $


    listener :

    SID_LIST_SQLDEV =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME=Enterprise_Access)
    (ORACLE_HOME=/orahome/oracle/DEV/db/tech_st/11.2.0)
    (ENV="LIBPATH=/opt/Progress/DataDirect/Connect64_for_ODBC_71/lib:/orahome/oracle/DEV/db/tech_st/11.2.0/lib")
    (PROGRAM=dg4odbc)
    )
    )

    SQLDEV =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ebsdev) (PORT = 1530))
    )
    )
    )

    DEV =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ebsdev)(PORT = 1525))
    )
    )

    SID_LIST_DEV =
    (SID_LIST =
    (SID_DESC =
    (ORACLE_HOME= /orahome/oracle/DEV/db/tech_st/11.2.0)
    (SID_NAME = DEV)
    )
    )



    tns entry :


    SQLDEV =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=ebsdev)(PORT=1530))
    (CONNECT_DATA=(SID=Enterprise_Access))
    (HS=OK)
    )


    Please help.


    Regards,
    Charan

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    no entry labeled "DBLINK" exists within tnsnames.ora file
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •