How to connect to oracle from SQL Server

Posted on 2006-04-20
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

    You can use DTS or linked servers.

    Author Comment

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

    Expert Comment

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

    Expert Comment

    by:Aneesh Retnakaran
    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 42

    Accepted Solution


    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.


    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    Introduced in Microsoft SQL Server 2005, the Copy Database Wizard ( is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now