This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Aug 28, 2013 11:59 PM by mkirtley-Oracle RSS

connection with ms sql 2005

user9275313 Journeyer
Currently Being Moderated

Dear all,

 

I have two scenario to make connection with sql server 2005.

1)sqlserver and database 11g both on windows xp 32 bit  .

Is oracle Gateway best option in such scenario?

2)Sqlserver on windows xp and 11g database on solaris 64 bit.

Please provide step by step documents for this.

 

Thanks


  • 1. Re: connection with ms sql 2005
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

      You can use an Oracle Database Gateway for both scenarios.

    There are different combinations you can have depending how you want to setup your systems -

     

    1. sqlserver and database 11g both on windows xp 32 bit

    You can install the gateway either as part of the existing RDBMS install or in a separate ORACLE_HOME.  It is usually better to install the gateway standalone in a separate OH as this makes maintenenace easier.

     

    2. Sqlserver on windows xp and 11g database on solaris 64 bit.

    You can either -

    a) install the gateway on the Solaris 64-bit machine to connect to the SQL*Server on Wiondows. Again you can install standalone or as part of the existing RDBMS

    b) from the RDBMS on Solaris 64-bit you can access a gateway on the Windows machine. If this is the same Windows machine as in scenario 1 then you can use that gateway which is already installed.

     

    There are 2 gateways you can use to connect to SQL*Server -

    - Database Gateway for SQL*Server - DG4MSQL

    - Database Gateway for ODBC - DG4ODBC

     

    There are differences in the licensing and functionality of these 2 gateways which are discussed in these notes -

     

    Functional Differences Between DG4ODBC and Specific Database Gateways (Doc ID 252364.1)

    Gateway and Generic Connectivity Licensing Considerations (Doc ID 232482.1) 

     

    The following notes have further details on setting up the gateways -

    How to Setup DG4MSQL (Database Gateway for MS SQL Server) on Windows 32bit post install (Doc ID 466267.1)

    How to Configure DG4MSQL (Oracle Database Gateway for MS SQL Server) 64bit Unix OS (Linux, Solaris, AIX,HP-UX) post install (Doc ID 562509.1)

     

    How to Setup DG4ODBC (Oracle Database Gateway for ODBC) on Windows 32bit (Doc ID 466225.1)

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

     

    The following note has further details on gateways including links to the documentation -

    Master Note for Oracle Gateway Products (Doc ID 1083703.1)

     

     

    These are all available on My Oracle Support.

    Regards,

    Mike

  • 2. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    Dear Mike,

     

    Thank you for your detail reply.

     

    I have configured Gateway to follow the scenario 1 in my question, I have installed Gate on the same oracle home, Every thing seems to be

    configured well and given default sql server database master during installation. I am facing error as following.

     

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

    [Generic Connectivity Using ODBC][H006] The init parameter

    <HS_FDS_CONNECT_INFO> is not set. Please set it in init<orasid>.ora file.

    ORA-02063: preceding 2 lines from DG4

     

    Waiting your kind reply.

     

    Thanks

  • 3. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    Latest update of my changing are as follows.

     

    I just change the tnsname hostname from localhost with ip address, It start to give other error

     

    ora-28545 error diagnosed by net8 when connecting to an agent sql server

     

     

    Thanks

  • 4. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    Hi Mike,

     

    Thanks I have sort out my issue.

     

    Now I am going towards my actual scenario and you suggest me following.

     

    b) from the RDBMS on Solaris 64-bit you can access a gateway on the Windows machine. If this is the same Windows machine as in scenario 1 then you can use that gateway which is already installed.

     

    I would be very thankful if you give the step by step detail in the above mentioned scenario.

     

    Suppose I have sqlserver and gateway on windows 32 bit machine and 11g database on solaris 64 bit machine.

    How can I configure them?

     

    Thanks

  • 5. Re: connection with ms sql 2005
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

       For your proposed setup you would do the following -

     

    1. Configure the gateway on Windows to access SQL*Server

    2. On the Solaris 64-bit create a tnsnames.ora entry that references the gateway listener on Windows 32-bit, for example -

     

    dg4msql  =
        (DESCRIPTION=
            (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname of the Oracle Gateway Server>)(PORT=1511))
            (CONNECT_DATA=(SID=dg4msql))
            (HS=OK)
         )


    - change the PORT and HOST as necessary.


    3. In SQLPLUS on Solaris create a database link that uses this tnsnames.ora entry -


    create database link dg4msql_link connect to "user_name" identified by "password" using 'dg4msql' ;


    4. Test from sqlplus -


    select * from dual@dg4msql_link ;


    This is discussed in the setup notes referred to earlier.


    Regards,

    Mike

  • 6. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    Hello Mike,

     

    Thanks for your reply. I follow the same step you have mentioned only added tnsnames entry in solaris tnsnames.ora file.

    So no linstener changes anywhere needs. I did all step and facing common error as follows.

     

    "ora-28545 error diagnosed by net8 when connecting to an agent sql server".

     

    Kindly let me know how to sort out it.

     

    Thanks

  • 7. Re: connection with ms sql 2005
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

      The ora-28545 is a configuration error so to follow up can you post the following -

    - gateway listener.ora on Windows

    - tnsnames.ora entry on Solaris

    - the create database link statement from Solaris

     

    Regards,

    Mike

  • 8. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    LISTENER_GW =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.253)(PORT = 1525))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
        )
      )

    ADR_BASE_LISTENER_GW = C:\product1\11.2.0\tg_1

     

     

    Tnsnames.ora

     

    LISTENER =
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1522))


    ORACLR_CONNECTION_DATA =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
        )
        (CONNECT_DATA =
          (SID = CLRExtProc)
          (PRESENTATION = RO)
        )
      )
    dg4msql  =
      (DESCRIPTION=
        (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.253)(PORT=1525))
        (CONNECT_DATA=(SID=dg4msql))
        (HS=OK)
      )
    PRD =
      (DESCRIPTION =
        (ADDRESS =
          (PROTOCOL = TCP)
          (HOST = 10.0.0.2)
          (PORT = 1522)
        )
        (CONNECT_DATA =
          (SID = PRD)
        )
      )
    TEST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.169)(PORT = 1521))
        (CONNECT_DATA =
          (SERVER = DEDICATED)
          (SERVICE_NAME = test)
        )
      )

     

    DBLINK COMMAND

     

     

    CREATE DATABASE LINK sqlser

             CONNECT TO "sa" IDENTIFIED BY "sa1234" USING 'dg4msql';

     

     

    Thanks

  • 9. Re: connection with ms sql 2005
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

      In listenener.ora you have the description list but you don't have the 'sid_list' which details the instances available.

    You should have an entry similar to -

     

    SID_LIST_LISTENER_GW =

      (SID_LIST =

        (SID_DESC =

          (SID_NAME = dg4msql)

          (ORACLE_HOME=C:\product\11.2.0\tg_3)

          (PROGRAM=dg4msql)

        )

      )

     

    and stop and start the gateway listener.
    If you then issue -

     

    lsnrctl status listener_gw

    or

    lsnrctl services listener_gw

     

    you should see the dg4msql sid as available.

     

    Regards,

    Mike

  • 10. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    Hi Mike,

     

    It works fine but I fall now in other issue, when I access same database through client I am getting following error.

     

     

    ORA-12154: TNS:could not resolve the connect identifier specified - Oracle Error

     

    Thanks

  • 11. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    Hi Mike,

     

    When I applied same scenario on solaris database and try to run query I am getting this error.

     

    ORA-12154: TNS:could not resolve the connect identifier specified

     

    Please help me.

     

    Thanks

  • 12. Re: connection with ms sql 2005
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

      The ORA-12154 means that the gateway entry cannot be found in the tnsnames.ora file or the tnsnames.ora file is not being accessed..
    Can you do the following -

     

    1. Check that there is a tnsnames.ora entry for the gateway.

    2. Post the entry here.

    3. What are the contents of sqlnet.ora ?

    4. Does the user or client that gets the ORA-12154 have access to the tnsnames.ora file containing the gateway entry ?

    5. On Solaris what is returned by -

    echo $TNS_ADMIN


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


    TROUBLESHOOTING GUIDE: ORA-12154 & TNS-12154 TNS:could not resolve service name (Doc ID 114085.1)


    You should also make sure that the tnsnames.ora entry has at least one blank at the beginning of each line except the first -


    dg4msql  =

    <blank> (DESCRIPTION=

    <blank>   (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.253)(PORT=1525))

    <blank>   (CONNECT_DATA=(SID=dg4msql))

    <blank>   (HS=OK)

    <blank>  )

     

    Can you post the latest gateway listener.ora ?

     

    Regards,

    Mike

  • 13. Re: connection with ms sql 2005
    user9275313 Journeyer
    Currently Being Moderated

    In above issue I was missing correct tns name file.

     

    I am trying to configure sql server 2008 on window 7 32 bit with same oracle gateway software , I am getting following error.

     

    ERROR at line 1:

    ORA-28500: connection from ORACLE to a non-Oracle system returned this message:

    [Oracle][ODBC SQL Server Driver][DBMSLPCN]SQL Server does not exist or access

    denied. {08001,NativeErr = 17}[Oracle][ODBC SQL Server

    Driver][DBMSLPCN]ConnectionOpen (Connect()). {01000,NativeErr =

    64}[Oracle][ODBC SQL Server Driver]Invalid connection string attribute {01S00}

    ORA-02063: preceding 2 lines from OLDSILO

     

    Gateway Listener post is following.

     

    LISTENER_GW =
      (DESCRIPTION_LIST =
        (DESCRIPTION =
          (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.220)(PORT = 1525))
          (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1525))
        )
      )
    SID_LIST_LISTENER_GW=
      (SID_LIST=
          (SID_DESC=
             (SID_NAME=dg4msql)
             (ORACLE_HOME=C:\product\11.2.0\tg_1)
             (PROGRAM=dg4msql)
          )
      )

    ADR_BASE_LISTENER_GW = C:\product\11.2.0\tg_1

     

    Kindly let me know where is the issue.

     

    Thanks

  • 14. Re: connection with ms sql 2005
    mkirtley-Oracle Expert
    Currently Being Moderated

    Hi,

      The critical error is this -

     

    SQL Server does not exist or accessdenied. {08001,NativeErr = 17}

     

    which is coming from SQL*Server. The gateway is making the connection but thne hitting a problem on SQL*Server itself.

    When other customers have had this error the solution was to change the SQL*Server configuration to be port based instead of named instances.

    Could you check with your SQL*Server DBAs ?

     

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

     

    DG4MSQL Gives Error - SQL Server Does Not Exist or Access Denied - With NativeErr 17 and 67 (Doc ID 1482030.1)


    Regards,

    Mike

1 2 Previous Next

Legend

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