Using DTS to connect to an Oracle Database

[mit99mh]

I want to extract data from an Oracle database using DTS / Microsoft OLE DB Provider For Oracle - what oracle related software do I need to install on the server hosting the instance of SQL Server?

 

[AlexCuse]

If you have the provider, I don't think you need anything else. Do you have the provider installed already?

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[mit99mh]

Thanks for replying - but I'm sure I need some Oracle-related software on the server - that's what the MS site says but it doesn't say what.

Thanks anyway.

 

[onpnt]

The thinest way you can do this is to download the Oracle InstantClient. You will need to register an account on Oracle (no strings attached) and then match the version of your Oracle installation to the client. e.g. if you have 10.2 then download the 10.2 client.

When you are in the universal installer in the second screen select custom. This will allow you to select only the options you need to install over installing the full client which is not required.

You'll click next a few times and come to a screen that will allow you to actually select the components to install. The only thing you will need to select is the "Oracle Windows Interfaces"

This will give you just about everything you need to successfully use providers froma windows stand point. Note: It is a good idea to select SQP*Plus also for testing. SQL*Plus is not large and it a good admin tool to have on your SQL box in case you have problems.

Go through the installation until it is done and then you will need to configure your TNSNAMES file. This file is what oracle uses to allow your connection to the oracle box. Search the oracle site for how to configure this file. They have very good documentation on what you will need as a minimum in there to get connected.

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[mit99mh]

Thanks for replying - I managed to stumble through and get the provider up and running. The only thing I had to do was to rename a registry setting NLS_LAND - why is this?

Any help appreciated