Querying Oracle db in Stored Procedures

[travisbrown]

Is it possible to update a table in SS2000 nightly from an Oracle 9 db with a SP. Any hints on how to go about this.

(I would put a question mark after the question, but the qestion mark key does not work on my keyboard.)

 

[lemosley01]

1) Get a new keyboard
2) Check out the topic Linked Servers and sp_addlinkedserver stored procedure in SQL Books online

Have fun and drive fast (just don't get caught)

 

[travisbrown]

Okay, revisiting this one.

Anyone care to help me walk through this? What I need to do is execute a sp daily at a specified time that will update a table in SS2K with data from a simple join query on a table in an Oracle db. I have absolutely no idea how to create the link to the Oracle db and subsequestly query the data.

I suppose because sp_addlinkedserver is on the master table I will have to get the sa to run it; however, I would like to prepare it for her.

Here's what they have in BOL, but I'm not sure of the parameters.

USE master
GO

EXEC sp_addlinkedserver
@server = 'LONDON Mktg', <--- don't know what this is. A new instance of what?
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'MyServer' <---I take it this is the name of the oracle server.
GO

Once this is done, how do I query against this linked data?

Thanks.

 

[Lezza]

It's been a couple of years since I've done this, but I do know I needed to look at a white paper called: Accessing Heterogeneous Data with SQL Server 7.0. You can still get it from the Knowledge Base. This was a real bear to implement. Are you running 7.0 or 2000? BOL in 2000 explains how to link up to an Oracle database in detail...

 

[travisbrown]

I'm using SS2000. I've looked at BOL, but haven't seen detailed enough stuff for me. I will look for that white paper.

Thanks.

 

[Lezza]

Try looking in BOL for this: OLE DB Provider for Oracle
under Accessing and Changing Relational Data. This is the detailed page I was referring to.