Post reply

connecting oracle database from sql server 2000 trigger

  • May 7, 2005 at 2:24 pm

    #166908

    Hi,

        I would like to connect to oracle database from sql server 2000 database. Could any body please help me out how to connect to oracle database?. I have to update one table in oracle database based on some values in the sql database table. 

      I was thinking of writing a trigger on the table in sql database which will update the table in the oracle database.But how do I connect to the oracle database from the sql database trigger? Is this possible ?

    Thanks in advance.

    Kavita

     

     

     

     

  • May 9, 2005 at 1:25 am

    #557540

    I think there are two ways you can try:

    First, you can create a DBLink to Oracle DB, then use it to access tables of Oracle DB;

    Second, you can use the T-SQL statements to access tables, for example, Select * from opendatasource('ORAOLEDB.ORACLE','Data Source=oracle92;User ID=scott;Password=scott')..Scott.Test

  • May 9, 2005 at 4:16 am

    #557555

    Hi Kavita,

    On my server I have created an ODBC connection on the server where my SQL database is installed.

    Secondly, I have created a linked server in SQL Enterprise Manager (choose Microsoft OLE DB PRovider for ODBC driver) and specify the oracle's username and password.

    I hope it will help you.

     

    Regards,

    Pascal

  • May 9, 2005 at 6:06 am

    #557561

    I don't believe I'd use a trigger for this because if the Oracle DB or the connection to it is down, then your SQL Server processes will start kicking off errors.

    I would use a trigger to write the info to a staging table and periodically run a job or batch to transfer the data from the staging table to the Oracle database.

    In either case, one way to do this is to setup a "Linked Server" to the Oracle database on the SQL Server.  This info is in Books OnLine but here's a start...

    By script...

    This example creates a linked server named LONDON Mktg that uses the Microsoft OLE DB Provider for Oracle and assumes that the SQL*Net alias for the Oracle database is MyServer.

    USE master

    GO

    -- To use named parameters:

    EXEC sp_addlinkedserver  

    @server = 'LONDON Mktg',  

    @srvproduct = 'Oracle',  

    @provider = 'MSDAORA',  

    @datasrc = 'MyServer'

    

     
    

     
    

    By Enterprise Manager...
    To set up a linked server 
    1. Expand a server group, and then expand a server.
    2. Expand Security, right-click Linked Servers, and then click New Linked Server.
    3. Click the General tab, and in the Linked server box, type the name of the server to link.
    4. Under Server type, click a selection. 
    If you select Other data source, you will have to specify provider properties.
    

     
    Kmyff is correct about using the OPENDATASOURCE SQL command but I don't like to put user names and passwords in code if I can help it.
    --Jeff Moden 
    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    Change is inevitable... Change for the better is not.
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
     
      
    •  
  •   
     
     
     May 11, 2005 at 12:20 pm 
     
      #558196  
          
         
     
    Thanks a lot. That helped me a lot
     
     
     
      
    •  
Viewing 5 posts - 1 through 4 (of 4 total)
 
You must be logged in to reply to this topic. Login to reply