Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69

    Unanswered: How to connect SQL Server to Oracle using HS?

    Hi,

    I am trying to connect SQL Server 2000 to Oracle 10g Release 10.2.0.1.0 Std. using Heterogeneous Services.
    Both servers run on a Windows 2003SP1 Std. platform.

    Does anyone know how to do this in detail?

    Thanks in advance

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Are you incapable or just unwilling to RTFM?
    http://oraclesvca2.oracle.com/docs/c...b14232/toc.htm
    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.

  3. #3
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    hmmm... perhaps incapable.
    Got many years experience on SQL Server, hardly not on Oracle (other than just downloading data in SQL Server).
    I know the doc on the link you advice me, however I did not succeed in get this work other than:

    select "EmployeeID","LastName","FirstName" from employees@mysqlserverdsn.

    (see http://www.databasejournal.com/featu...0893_3442661_1)

    I've reinstalled the server a couple of times now (fortunately from an image) because Oracle became unreachable (no listener etc) after a reboot.
    I'm running out of time now and looking for other ideas.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Oracle9i, like previous versions, supports heterogeneous services to allow data in non-Oracle database to be queried using SQL. This support has been in the form of transparent gateways, which are vendor specific, or generic connectivity which uses ODBC or OLEDB to make the connections. The functionality supported by generic connectivity is typically more limited than that possible when using vendor specific gateways, but it is quick and simple to configure. The steps listed below can be used to connect Oracle to any ODBC compliant database (MS Access, SQL Server etc.) on the local server:
    Make sure a valid username and password are present for the non-Oracle database (MyUser/MyPassword).
    Create an ODBC DataSource for the non-Oracle database (Access1).
    Create a file called: ORACLE_HOME\hs\admin\initACCESS1.ora which contains:
    HS_FDS_CONNECT_INFO = Access1 #ODBC DSN
    HS_FDS_TRACE_LEVEL = OFF
    Add the following entry to the tnsnames.ora file:
    ACCESS1.WORLD =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521) )
    (CONNECT_DATA=(SID=ACCESS1))
    (HS=OK)
    )
    Add the following entry into the listener.ora file:
    (SID_DESC=
    (SID_NAME=ACCESS1)
    (ORACLE_HOME=D:\Oracle\Ora9011)
    (PROGRAM=hsodbc)
    )
    Reload the listener: lsnrctl reload
    Create a database link using:
    CREATE DATABASE LINK access1.world CONNECT TO "MyUser" IDENTIFIED BY "MyPassword" USING 'ACCESS1.WORLD';
    Query the table using:
    SELECT *
    FROM mytable@ACCESS1.WORLD;
    With MS Access the first query is slow due to the Access application being loaded. As expected, subsequent queries do not display a similar lag.

    http://asktom.oracle.com/pls/ask/f?p...18830681837358

    HTH
    Gregg

  5. #5
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi Gbrabham,

    You wrote:

    "The steps listed below can be used to connect Oracle to any ODBC compliant database (MS Access, SQL Server etc.) on the local server"

    Must it be on the same server as Oracle is running on?
    That's the thing I didn't try.

    Rgds,

    Wim

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    No ... it can be on different servers ...

  7. #7
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi Gbrabham,

    Did this (combined several examples):

    1) Create ODBC connection
    Windows Registry Editor Version 5.00

    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI]

    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\MYSQLSER VERDSN]
    "Driver"="C:\\WINDOWS\\system32\\SQLSRV32.dll"
    "Description"="MYSQLSERVERDSN"
    "Server"="tserver03.loc1.hnl"
    "LastUser"="root"

    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources]
    "MYSQLSERVERDSN"="SQL Server"

    [HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC File DSN]
    "DefaultDSNDir"="C:\\Program Files\\Common Files\\ODBC\\Data Sources"


    2) initMYSQLSERVERDSN.ora
    HS_FDS_CONNECT_INFO = MYSQLSERVERDSN
    HS_FDS_TRACE_LEVEL = OFF

    3) Add to listener:
    LISTENERMYSQLSERVERDSN =
    (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=tcp)(HOST=pserver21.loc1.hnl)(P ORT=1522))
    (ADDRESS=(PROTOCOL=ipc)(KEY=PNPKEY)))

    SID_LIST_LISTENERMYSQLSERVERDSN=
    (SID_LIST=
    (SID_DESC=
    (SID_NAME=MYSQLSERVERDSN)
    (ORACLE_HOME = c:\oracle_home)
    (PROGRAM=hsodbc)
    )
    )


    4) Add to tnsnames
    MYSQLSERVERDSN =
    (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=pserver21.loc1.hnl)(P ORT=1522))
    (CONNECT_DATA=(SID=MYSQLSERVERDSN))
    (HS=OK)
    )

    5) Start listener
    lsnrctl start listenermysqlserverdsn

    6) Create dblink
    create public database link mysqlserverdsn
    connect to root identified by root using 'MYSQLSERVERDSN';

    7) Test with query
    select "EmployeeID","LastName","FirstName" from employees@mysqlserverdsn;

    Result:
    It works, however, when rebooting the server, I can't connect anymore :
    ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

    Any idea what to do?

  8. #8
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Start troubleshooting tcpip and sqlnet ...
    Can you ping the server (tcpip) ? using the servername
    Can you tnsping the instance ?

    If you have any problems, can you ping the ip address
    (are you having any problems with the dns ..)

    You might try changing the tnsnames and listener using the ip address
    instead of the name ...

    also, ensure that the space is not really there
    (ADDRESS=(PROTOCOL=tcp)(HOST=pserver21.loc1.hnl)(P ORT=1522))

    see what happens

    HTH
    Gregg

  9. #9
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi Gregg,

    1) I can ping the server using the servername.
    2) I can tnsping both sids (new sid and the database called euro)

    C:\>tnsping mysqlserverdsn
    TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2006 19:50:57
    Copyright (c) 1997, 2005, Oracle. All rights reserved.
    Used parameter files:
    C:\oracle_home\network\admin\sqlnet.ora
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = tcp)(HOST = pserver21.loc1.hnl)(PORT = 1522)) (CONNECT_DATA = (SID = MYSQLSERVERDSN)) (HS = OK))
    OK (30 msec)

    C:\>tnsping euro
    TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 30-JAN-2006 19:50:26
    Copyright (c) 1997, 2005, Oracle. All rights reserved.
    Used parameter files:
    C:\oracle_home\network\admin\sqlnet.ora
    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = pserver21.loc1.hnl)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = euro)))
    OK (30 msec)

    4) Changing the names to IP addresses didn't help as well.

    5) Spaces are inserted by the editor of dbforums, checked.

    6) More details: O/S = Windows 2003 SP1,
    Oracle 10g version 10.2

    I am a week busy now to get this working, and quite desperate...
    We also hired a (no cure, no pay) consultant... no result..

    Regards,

    Wim

  10. #10
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    ..also removed the machine from the domain, no result

    Wim

  11. #11
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    Did you at least checked the settings are still on the registry after rebooting ? Or do you have to create those after you reboot as well ? If so, the user might not have permissions.

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Sounds like a Listener or PATH environment variable problem...
    Double check the listener (lsnrctl) and issue a reload command....
    Check the status ...

    Ensure that the PATH variable is set properly...

    command prompt: path
    HTH
    Gregg

  13. #13
    Join Date
    Jan 2006
    Posts
    2
    Quote Originally Posted by Wim Venema
    Hi,

    I am trying to connect SQL Server 2000 to Oracle 10g Release 10.2.0.1.0 Std. using Heterogeneous Services.
    Both servers run on a Windows 2003SP1 Std. platform.

    Does anyone know how to do this in detail?

    Thanks in advance
    I wrote a little bit up on the free alternative at http://www.orawin.info/services/inde...d=41&Itemid=34

    if that helps.

    Niall Litchfield
    Oracle DBA
    http://www.orawin.info/services/

  14. #14
    Join Date
    Jun 2004
    Location
    Haaksbergen, Netherlands
    Posts
    69
    Hi,

    I got it working now.
    Removing the machine from the domain was the first step but
    JMartinez brought me to the idea of registry restrictions forced by the GPO of the domain (classes). The guy responsible for this does not work here anymore.
    A complete reinstall of the machine and not join it to the domain again was the solution.

    I would like to thank all of you for your great help.

    Still want to know if you can automatically startup Oracle after a reboot in Windows 2003 and how to query: select * from table@dsn instead of putting all the single column names between "" like select "col1", "col2" from table@dsn.

    Again thanks!

    Best regards,

    Wim Venema

  15. #15
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can autostart Oracle from 2003 ... In services, ensure that the Oraclexxxservice (xxx is the instance name) in set for automatic startup.

    in the registry, look under HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE / HOME0 (1) (2) ... look for ORA_xxx_AUTOSTART (xxx is the instance)
    ensure that it is set to TRUE

    What type of error do you get when you issue the command....

    select * from table@dsn

    HTH
    Gregg

Posting Permissions

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