How to connect to oracle from SQL Server

Posted on 2006-04-20
Medium Priority
Last Modified: 2012-08-13
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
Question by:oogooglies
LVL 20

Expert Comment

ID: 16497633
You can use DTS or linked servers.

Author Comment

ID: 16497650
ok explain how i would do it with each if that is ok???? I have never used SQL server before.....
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16497661
see this tutorial:
in page 4, you will see details about oracle
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16497725
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
LVL 43

Accepted Solution

Eugene Z earned 500 total points
ID: 16497929

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.   oracleserver.yourcompany.com 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.


Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question