Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ?

Jeudi, juin 12, 2008
By Thierry GASCARD in Oracle (tgascard@capdata.fr) [9 article(s)]

Cet article est écrit par Thierry GASCARD.

La première solution est d’utiliser la « gateway Oracle pour SQL SERVER  » (15000$ par serveur).

La deuxième gratuite est d’utiliser OLE DB.

Je vais m’intéresser à la deuxième solution, vous en devinez la raison :)

1) créer un fichier UDL (vide puis ajouter l’extension .udl) sous C:\\Documents and Settings\\thierry\\Bureau\\client1.udl, puis double cliquer

-onglet Fournisseur : choisir le fournisseur « Microsoft OLE DB Provider for SQL Server »

s’il n’est pas présent téléchargez MDAC (merci david)
-onglet connexion : nom du serveur SQL (minsk\aquarium), utilisateur/mot de passe, base de données client1
2) sous $ORACLE_HOME\hs\admin
faire une copie du fichier inithsoledb.ora en initclient1.ora
3) configuer initclient1.ora
HS_FDS_CONNECT_INFO = »UDLFILE=C:\\Documents and Settings\\thierry\\Bureau\\client1.udl »
HS_FDS_TRACE_LEVEL = 0
HS_FDS_TRACE_FILE_NAME = client1.trc

attention doubler \ (merci manu..)
4) configuer le fichier tnsnames.ora
CLIENT1 =
(DESCRIPTION=
(ADDRESS=(PROTOCOL=tcp)(HOST=pc-thierry)(PORT=1524))
(CONNECT_DATA=(SID=CLIENT1))
(HS=OK)
)
5) configurer le fichier listener.ora
ajouter aux services de votre listener
(SID_DESC=
(SID_NAME=CLIENT1)
(ORACLE_HOME = D:\app\oracle\10.2.0\db_1)
(PROGRAM=hsolesql)
)
6) créer le database link
CREATE PUBLIC DATABASE LINK « CLIENT1″ USING ‘CLIENT1′;

7) tester la connexion
select * from sysobjects@client1;

remarque : en cas de souci, vous pouvez poser une trace.
mettre le paramètre HS_FDS_TRACE_LEVEL = 4
la trace se trouvera sous D:\app\oracle\10.2.0\db_1\hs\trace

Continuez votre lecture sur le blog :




Cliquer pour partager cet article sur Viadeo
Cliquer sur "CAPTURER" pour sauvegarder cet article dans Evernote Clip to Evernote

Tags:

2 Responses to “Se connecter à SQL Server à travers Oracle, quelle drôle d’idée ?”

  1. abderrazzak

    Oracle provided generic connectivity to non-Oracle databases using Heterogeneous Services. HS uses ODBC to connect to the non-Oracle database. You should have MySQL ODBC driver installed on the system.
    The following examples are based on MySQL version 3.51.8 for Windows and Oracle 9i (9.2.0.1.0) for Windows.
    MuSQL database:
    Database used is TEST database which contains table called PET.

    Setting up ODBC Driver
    Configure an ODBC system data source for the target datastore. Use an ODBC client application or ODBC test to verify the connectivity.
    Setting up the ODBC connection to the MySQL database requires an odbc.ini file.
    My ODBC.INI file has the following entry for [mysql]
    [mysql]
    Driver32=C:\WINDOWS\System32\myodbc3.dll
    SERVER=DEVELOPMENT_06
    PORT=3306
    USER=root
    password=root
    database=test
    option=3
    socket=

    Configure tnsnames.ora and listener.ora
    Following entries are required in tnsnames.ora and listener.ora
    TNSNAMES.ORA (Avialable typically in ORACLE_HOME\NETWORK\ADMIN)
    HSMSQL =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 197.1.1.31)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = msql) <– Should match the SID on listener.ora
    )
    (HS = OK) <– HS clause to use Heterogeneous Services
    )

    LISTENER.ORA (Avialable typically in ORACLE_HOME\NETWORK\ADMIN)
    # LISTENER.ORA Network Configuration File: c:\oracle\ora91\network\admin\listener.ora
    # Generated by Oracle configuration tools.

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC3))
    )
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = Development_06)(PORT = 1521))
    )
    )
    )

    SID_LIST_LISTENER =
    (SID_LIST =
    (SID_DESC =
    (SID_NAME = db01)
    (ORACLE_HOME = c:\oracle\ora91)
    )
    (SID_DESC =
    (PROGRAM = hsodbc) <– HSODBC is the executable
    (ORACLE_HOME = c:\oracle\ora91)
    (SID_NAME = msql) services
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC3)))
    Services Summary…
    Service « db01.shjcustoms.ae » has 1 instance(s).
    Instance « db01″, status READY, has 1 handler(s) for this service…
    Handler(s):
    « DEDICATED » established:1 refused:0 state:ready
    LOCAL SERVER
    Service « msql » has 1 instance(s).
    Instance « msql », status UNKNOWN, has 1 handler(s) for this service…
    Handler(s):
    « DEDICATED » established:2 refused:0
    LOCAL SERVER
    The command completed successfully
    LSNRCTL>

    Create Initialization file:
    create and customize an initialization file for your generic connectivity agent. Oracle supplies a sample initialization file named « inithsodbc.ora » which is stored in the ORACLE_HOME\hs\admin directory.
    To create an initialization file, copy the appropriate sample file and rename the file to initHS_SID.ora. In this example the sid noted in the listener and tnsnames is msql so our new initialization file is called initmsql.ora.

    INITMSQL.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 = mysql
    HS_FDS_TRACE_LEVEL = 0
    #HS_FDS_FILE_NAME=
    #HS_FDS_SHARABLE_NAME = c:\windows\system32\myodbc3.lib

    #
    # Environment variables required for the non-Oracle system
    #
    #set =

    set ODBCINI=C:\WINDOWS\ODBC.INI

    Create Database Link
    Oracle8i and above have made it mandatory that the database link should have the same name as the targer database. This is achieved by setting a parameter named GLOBAL_NAMES in the init.ora file. Hence if you are creating a database link with name other than the target database you can disable this rule with the following statement:
    The following statement will disable global naming for the connected session only!

    SQL> alter session set global_names=false;

    Session altered.

    If you want to disable global naming for all then use:
    SQL> alter system set global_names=false;

    System altered.

    Create a database link to access the target database. Please use the appropriate quotes as noted below.

    SQL> create database link msql connect to « bill » identified by « byte » using ‘hsmsql’;

    Database link created.

    SQL> select * from pet@msql;

    name owner srno
    ——————– —————————— ———-
    Puffball James 1
    PingPong Bill 2
    Tennis Bong 3
    Cricket Tim 4

    4 rows selected.

    The above statements were executed by user SYSTEM. If you want any other user to access MySQL database then create a public database link:
    SQL> create public database link msql connect to « bill » identified by « byte » using ‘hsmsql’ ;

    Database link created.

    SQL> conn scott/tiger@db01
    Connected.
    SQL> select * from pet@msql;

    name owner srno
    ——————– —————————— ———-
    Puffball James 1
    PingPong Bill 2
    Tennis Bong 3
    Cricket Tim 4

    4 rows selected.

    #100
  2. Baba

    Très bon article sauf que tout est CLIENT1 dans le tutoriel on ne sait plus quoi correspond à quoi.

    #21849

Leave a Reply