Post reply

Linked Server Oracle OLEDB Driver Missing

  • April 27, 2009 at 9:00 am

    #202628

    I have virtually zero knowledge of Oracle. I wanted to setup a linked server from a Windows 2003 Server with x64 SQL 2005 SP3 EE to an Oracle database. However when I go into linked server via management studio, there is no driver for anything Oracle (oledb, odbc, etc). I found a microsoft article about setting up a linked server but its old and references windows 2000 and older oracle clients. Can anyone provide me some insights on how to get this driver to show up?

  • April 27, 2009 at 9:30 am

    #984432

    you have to install the Oracle Client tools on the server, set up the TNSNAMES.ORA and SQLNET.ORA files so you can actually connect with SQLPLUS or other Oracle utilities like Oracle SQLDeveloper from that server..

    and then additionally, If SQL Server instance is 64 bit and not 32 bit on your 64 bit Server , you need some 64 bit drivers that are available from oracle.

    I think it's a non issue if your SQL instance is 32 bit, even if it is on a 64 bit server.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • April 28, 2009 at 10:40 am

    #985309

    Thanks, I will try that and see what I get.

  • May 14, 2009 at 1:25 am

    #994298

    I'm working with SQL Server connecting to Oracle databases also.

    Loweell is correct, you need to install an oracle client on your SQL server machine to provide drivers/libraries as well as configure TNSNAMES.ora file etc to point to your Oracle Database.

    The standard MDAC drivers that come with SQL Server 2005 for Oracle are outdated. They haven't been updated since oracle 8i. They will work, but run poorly.

    To get the correct drivers you should download the Oracle client that is the same version as the oracle database you are connecting to. (It is possible to use a later version but I have been told this can cause problems.)

    If you want to use OLE, you need to download and install the Oracle Data Access componets (ODAC) and work through the oracle client ODAC installation. (It took me a few attempts to get this right, because of the way the Oracle universal installer works, I hadn't installed the OLE parts correctly.)

    When you create your linked server, select the Oracle provider for OLE DB (OraOLEDB) and you'll find it runs much better.

    Good luck 🙂

  • May 15, 2009 at 2:23 pm

    #995678

    If you install the Oracle 11 client 64bit, if you are on a 64 bit server, you may not need to install the the Oracle ODAC

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply