Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2002
    Location
    BH - MG - BRAZIL
    Posts
    13

    Post Unanswered: CREATE DATABASE LINK ORACLE to SQLServer

    Hi!!!

    I have tried to create a link with Oracle 8.0.5 to SQL-Server 2000 but I could not. Is there some special parameter that I need to set?

    Thank you by attention.

    Bye.

  2. #2
    Join Date
    Aug 2002
    Posts
    1
    Hi
    Greetings. Can u tell me what procedure u followed to create the database link so that we can tell u where u r doing mistake...

  3. #3
    Join Date
    Aug 2002
    Location
    BH - MG - BRAZIL
    Posts
    13
    Hi!!!

    mudhalvan,

    there is a file attached. It has my steps.

    Bye.
    Attached Files Attached Files

  4. #4
    Join Date
    Jul 2002
    Location
    Brazil
    Posts
    7

    Arrow CREATE DATABASE LINK ORACLE to SQLServer

    Hi Leo,

    How are the ODBC Driver that you was used from this connection?
    How are the O.S. that Oracle was instaled?
    I currently work with this database link.

    Regards,

    --------------------------------------------------------------
    Délson Castro Gonçalves
    Coordenador de Informática
    Royal & SunAlliance Seguros
    Tel : 55 21 2223-7711
    E-mail: delson.castro@br.royalsun.com
    http://www.royalsun.com.br
    --------------------------------------------------------------

    Originally posted by LEO ARRUDA
    Hi!!!

    mudhalvan,

    there is a file attached. It has my steps.

    Bye.

  5. #5
    Join Date
    Jul 2002
    Location
    Brazil
    Posts
    7

    Thumbs up

    Hi LEO,

    This guide bellow describe how to setup a database link between a Microsoft SQL Server and the Oracle Database (for Windows NT/2000) using Oracle Heterogeneous Services. Let me if you need to guide from OS AIX.

    1) Create the DSN system source in Microsoft ODBC Administrator (e.g. "sqlmis").

    2) Adjust the configuration file of the gateway, located in ORACLE_HOME/HS/ADMIN. The name depends on the SID you use for the Heterogeneous Service. We has used the same name sugested by ORACLE guide (hsodbc). In this case the configuration file is <inithsodbc.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 = sqlmis |<- ODBC Data Source Name configured in step 1
    HS_FDS_TRACE_LEVEL = 0 |<- trace levels are from 0 to 4
    HS_OPEN_CURSORS = 300 |<- this parameter has solved the ORACLE error.

    3) Configure tnsnames.ora in ORACLE_HOME\NETWORK\ADMIN. Add the following lines to the file:

    hsodbc =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <your HOSTNAME>) (PORT = 1521))
    )
    (CONNECT_DATA = (SID = hsodbc))
    (HS=OK)
    )

    4) Now configure listener.ora in ORACLE_HOME\NETWORK\ADMIN. Add the following lines to the file in SID_List and restart the listener afterwards:

    (SID_DESC =
    (SID_NAME = hsodbc)
    (ORACLE_HOME = D:\oracle\ora81) |<- your ORACLE_HOME
    (PROGRAM = hsodbc )
    )

    5) Create a database link:

    In this sample : <create database link hsodbc connect to "user" identified by "password" using 'hsodbc'>

    Please note that "user" and "password" are the user granted in SQL Server.

    Regards.

    Délson Castro.



  6. #6
    Join Date
    Apr 2002
    Location
    California, USA
    Posts
    482

    Cool

    Check the AlligatorDBC .

    You could use two different RDBMS sources in your queries at the same time.


    Hope that helps,

    clio_usa
    OCP - DBA

    .

  7. #7
    Join Date
    Nov 2003
    Posts
    1

    Thumbs down TNS:could not resolve service name-- while creating DB link for remote sql server

    Hi,

    While creating dblink for remote sql server I added the follwing changes in

    singapore ------> local machine with oracle server
    dataserver ----->is the remote machine for sql server
    testdsn ----->DSN name for remote sql server database

    TNSNAMES.ora file as
    ---------------------------------------
    hsodbc =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = dataserver ) (PORT = 1433))
    )
    (CONNECT_DATA = (SID = hsodbc))
    (HS=OK)
    )


    changes in SID LIST in listener.ora in
    ----------------
    (SID_DESC =
    (SID_NAME = hsodbc)
    (ORACLE_HOME = c:\ora92)
    (PROGRAM = hsodbc )
    )

    inithsodbc.ora file as
    ------------------------
    HS_FDS_CONNECT_INFO = testdsn
    HS_FDS_TRACE_LEVEL = 0
    HS_OPEN_CURSORS = 300

    --------------------------------------------------------------

    The database link is created using the command

    1)create database link mylink using 'hsodbc';
    sql plus output : database link created

    2)select 1 from dual@mylink;
    sql plus output : TNS: couldnot resolve service name.

    I am using XP. Please help me in getting out of this problem.I have attached all the three .ora files. I think I am going wrong with entries in these files.

    Thanks in advance
    Andy
    Attached Files Attached Files

  8. #8
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    The tnsnames.ora file contains the service_name parameter. This value is sent to the listener. Once the listener obtains the service_name it looks for a matching registration in its files. When the database starts, the database will automatically register with the listener. The init.ora file contains the service_names parameters of the names to be registered with the listener when the instance starts.

    So check whether the entries are correct.
    SATHISH .

  9. #9
    Join Date
    Oct 2003
    Posts
    232

    Form 6i and report 6i

    I want to connect from Form 6i and Report 6i to Mssql and create reports


    how do i do it , is it the same way as connecting from oracle


    SEJ
    bigfoots

Posting Permissions

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