connecting oracle database from sql server 2000 trigger

Question

connecting oracle database from sql server 2000 trigger

kavita shende

Say Hey Kid

Points: 713
More actions
May 7, 2005 at 2:27 pm

#66266

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

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

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 masterGO-- 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 
Expand a server group, and then expand a server.

Expand Security, right-click Linked Servers, and then click New Linked Server.

Click the General tab, and in the Linked server box, type the name of the server to link.

Under Server type, click a selection. If you select Other data source, you will have to specify provider properties.

--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)