1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Configuring Oracle gateway for MS SQL Server

Discussion in 'Other Databases' started by Benjamin, Mar 24, 2014.

  1. Benjamin

    Benjamin Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi everyone,

    I have two Windows XP machines connected as a LAN : machine named oracle and another machine named sqlserver (the host name)

    I have installed the Oracle database software on the first one (11g R2) and the SQL SERVER 2008 R2 software on the second one

    On the SQL Server side I created a database named test with a table named table1 (for testing)

    I installed and configured the Oracle gateway software on the oracle machine and created a database link to access the SQL Server data, but I'm getting the following error :


    ERROR at line 1:
    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
    [Oracle][ODBC SQL Server Driver][DBNETLIB]Invalid connection.
    {08001,NativeErr = 14}[Oracle][ODBC SQL Server
    Driver][DBNETLIB]ConnectionOpen (ParseConnectParams()). {01000,NativeErr =
    14}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}
    ORA-02063: preceding 2 lines from DBLINK



    my initdg4msql file :


    # This is a customized agent init file that contains the HS parameters
    # that are needed for the Database Gateway for Microsoft SQL Server

    #
    # HS init parameters
    #
    HS_FDS_CONNECT_INFO=[SQLSERVER]/MSSQLSERVER/test
    HS_FDS_TRACE_LEVEL=OFF
    HS_FDS_RECOVERY_ACCOUNT=RECOVER
    HS_FDS_RECOVERY_PWD=RECOVER


    my listener.ora file :


    # listener.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = CLRExtProc)
    (ORACLE_HOME = C:\app\Administrator\product\11.2.0\dbhome_1)
    (PROGRAM = extproc)
    (ENVS = "EXTPROC_DLLS=ONLY:C:\app\Administrator\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )
    (SID_DESC =
    (SID_NAME = dg4msql)
    (ORACLE_HOME = C:\product\11.2.0\tg_1)
    (ENVS = LD_LIBRARY_PATH=C:\product\11.2.0\tg_1\dg4msql\driver\lib:C:\product\11.2.0\tg_1\lib)
    (PROGRAM = C:\product\11.2.0\tg_1\bin\dg4msql)
    )
    )

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    )
    )

    ADR_BASE_LISTENER = C:\app\Administrator

    my tnsnames.ora file :


    # tnsnames.ora Network Configuration File: C:\app\Administrator\product\11.2.0\dbhome_1\network\admin\tnsnames.ora
    # Generated by Oracle configuration tools.

    ORACLR_CONNECTION_DATA =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (CONNECT_DATA =
    (SID = CLRExtProc)
    (PRESENTATION = RO)
    )
    )

    ORCL =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oracle)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
    )
    )

    dg4msql =
    (
    DESCRIPTION =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    (CONNECT_DATA = (SID = dg4msql))
    (HS = OK)
    )


    the command I used to create the database link :


    create public database link dblink connect to "sa" identified by "mssqlserver" using 'dg4msql';


    Thanks in advance for any answer or help