How to connect to oracle from SQL Server

This is probably ridiculously easy, but is frustrating the hell out of me. I want to connect to a number of oracle dbs from sql server to reteive the data and apply to a set sql server tables........

Please advise?

Many Thanks
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

You can use DTS or linked servers.
oogoogliesAuthor Commented:
ok explain how i would do it with each if that is ok???? I have never used SQL server before.....
Guy Hengel [angelIII / a3]Billing EngineerCommented:
see this tutorial:
in page 4, you will see details about oracle
Aneesh RetnakaranDatabase AdministratorCommented:
From BOL

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
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Mktg',
   @srvproduct = 'Oracle',
   @provider = 'MSDAORA',
   @datasrc = 'MyServer'
-- OR to use no named parameters:
USE master
EXEC sp_addlinkedserver
   'LONDON Mktg',

Once you created the linked server, you can fetch the data from the linked server using

SELECT * from LinkedServerName.daabaseName.dbOwnerName.TableName
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:

1)  Determine your version of Oracle.

If you are using Oracle 7.x through Oracle 8.1, you can use the MSDAORA OleDB driver provided by Microsoft.

If you are using Oracle 9, you have to use Oracle's driver.  I've not had to use the Oracle 9 driver, so I'm not going to list all of the instructions here, but you can find them at Oracle's site:

2)  Make sure you have installed the Oracle client software on your SQL 2000 box.  To use MSDAORA, you have to make sure it is a fairly recent client version.  In BOL, it states:

The Microsoft OLE DB Provider for Oracle requires Oracle Client Software Support File version or later, and SQL*Net version

3)  Set up your SQL*NET alias on your SQL2000 box.  If you aren't familiar with Oracle, ask your Oracle DBA.  If s/he won't talk to you, use the Oracle Configuration assistant.  I use Oracle8, so in my program tree, it shows up under Programs/Oracle - Oracle (the second oracle is the name of the "Home" you chose when you installed the client) / Network Administration / Net8 Configuration Assistant.

I can't stress highly enough how much less stress you'll encounter if you have a friendly Oracle DBA help you with this step.

Once you have that running, you want to go to Local Net Service Name configuration then to Add.

This can be a bit tricky.  Your service name is the name of the Instance of the DB you want to connect to.  After that, you'll likely choose TCP as the communication protocol, accept the default port (1521) and enter the name of the server your Oracle DB is sitting on:  eg. or whatever it's dns'd to in your company.

You'll then get a chance to test your connection.  It will fail.  You then want to change the connection information using the little button at the bottom to whatever your DBA wants you to use as your username and password.  It always defaults to scott/tiger which never works.  If it does work, take a long hard look at your dba.  Anyway, once you change it, the connection should work and let you know it.

Finally, it lets you choose your Net Service Name which you'll use below.  This is basically an alias for the OracleDb you just linked to.

I don't profess to be an Oracle DBA of great merit, but this is how I do it.

4)  Update your registry for your version of Oracle
Scared yet?  Well, Microsoft makes this fairly easy for the versions it supports and believe me, it won't work without it.

Oracle client     Windows 2000
7.x =   mtxoci7x_win2k.reg
8.0 =   mtxoci80x_win2k.reg
8.1 =   mtxoci81x_win2k.reg

These are all listed in the Books Online entry under linked servers, connecting to Oracle.

You can browse to them at:
C:\Program Files\Common Files\System Files\OLE DB
If you right click the proper file, it will allow you to merge the files into your registry.

5)  Add the linked server through query analyzer.

You can do this through enterprise manager, but I find it confusing.

sp_addlinkedserver @server='', @srvproduct='', @provider='', @datasrc=''

You don't have to use the named parameters as long as you do this in order.  For the values, use:

@server='Bob' or whatever you want to use to name your linked server locally
@datasrc='SuperDB'  (this is the name you used in the last step of your TNS name creation, also known as your Net Service Name)

6)  sp_addlinkedsrvlogin 'Bob', false, 'Joe', 'OrclUsr', 'OrclPwd'

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
    [ , [ @useself = ] 'useself' ]
    [ , [ @locallogin = ] 'locallogin' ]
    [ , [ @rmtuser = ] 'rmtuser' ]
    [ , [ @rmtpassword = ] 'rmtpassword' ]

@rmtsrvname is the Name you chose for the @server above.
@useself = 'false'
@locallogin is whatever local SqlServer login name you wish to allow to connect.
@rmtuser is the Oracle Username in the linked database.
@rmtpassword is the Oracle password for that remote user.

7)  Try a select query from the linked server as follows:

SELECT top 10 * from BOB..<tableowner>.<tablename>

If you were connecting to a table named LIST owned by GENERAL, you would use  BOB..GENERAL.LIST

Oracle is case sensitive, so make sure you match the caps in the last two values or you will get back a nasty error, or it just won't find it.

After this, you can fine-tune your linked server through Enterprise manager, adding new users, adjusting passwords and such.

Final note:

There is one more possible solution for drivers.  It is quite expensive, but much better than Oracle's or Microsoft's drivers combined.  DataDirect is an excellent source of drivers for most databases.  You can check out their products at their website:

Hope this helped.  If you are using Oracle 9i, sorry about the verbose description.  If not, this should be about what you'll need to get going, though the exact verbage on the screens may vary a bit depending upon your version of the Oracle software.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.