Drivers available for creating linked servers

  • Hi,

    My sql server is running the following version: Microsoft SQL Server 2005 - 9.00.1399.06 (X64)  

    I am trying to add some oracle linked servers and I only see the 2 drivers installed:

    1. SQL Native Clent 2. SQL server

    MDAC version in the registry shows 2.82.8220

    I am trying to figure out whether there is something wrong with the installation or I need to install the drivers separately.

    Could someone please check the control panel-->Adminitrative tools-->Data Sources(ODBC)-->Add and tell me the list of drivers?

    Any help on this will be greatly appreciated.

    -Nikki

     

     

     

  • Nikki,

    I have a lot on the list because we have things to be installed on the server separetely. We did have to install Oracle client (or connectivity tools) for us to be able to create a linked server.

    Regards,Yelena Varsha

  • Was this ever resolved?  We are missing ODBC drivers (including ORACLE) on our 64 bit SQL Server 2005 environments.  Server operations group installed the Oracle client, but the drivers are still missing.  Maybe they missed something?

  • I always used to use MSDAORA to connect to the Oracle servers, but there isn't one for x64.

     

    So you need Oracle 10g client for 64 bit, then a couple of patches from metalink (5203839 and 5752544 for platform MS Windows Server 2003 (AMD64 and EM64T))

     

    Before installing 5752544, you need to download, from

    http://www.oracle.com/technology/software/tech/windows/odpnet/64-bit/index.html

    Oracle10g Release 2 ODAC (64-bit) 10.2.0.2 for Windows x64

    Download the File

    64-bit ODAC 10.2.0.2 Beta for Windows x64

    It is apparently the production version even though it says Beta.

    When you create your linked servers you need to set collation compatible to TRUE otherwise you'll get this error

    CANNOT FETCH ROW USING BOOKMARK FROM OLE DB PROVIDER ORAOLEDB.ORACLE LINKED SERVER 

    One last thing - since it's the 10g client you will no longer be able to connect to versions of Oracle before 9i

     

    Hope this is useful

    IainT

  • I cannot find the two patches listed however the link now points to a 10.2.0.3 rather 10.2.0.2 so maybe the patches are no longer necessary. However, I am having a little trouble actually using the driver to build a linked database.

    I have a 64 bit SQL Server 2005 SP2 on a Windows 2003 x64. The developer said that he needed to use OLE to talk to Oracle so I went to Oracle and downloaded the 64 bit OraOLDDB driver as recommended in a MS note.

    I can connect to Oracle via tnsping and using SQLPlus but I cannot create a linked db successfully.

    The linked database errors off with >> Cannot initialize data source for OLE provider "OraOLEDB.Oracle" for Lined Server "X" (Microsoft SQL Server error: 7399) << (manually typed)

    Here is the official OS, SQL Server, and Oracle client information:

    The OS is Windows Server 2003 Standard x64 edition Service Pack 2 (NT 5.2 build 3790)

    The SQL Server 2005 9.0.3042 (x64) [from

    Microsoft SQL Server Management Studio 9.00.3042.00

    Microsoft Analysis Services Client Tools 2005.090.3042.00

    Microsoft Data Access Components (MDAC) 2000.086.3959.00

    (srv03_sp2_rtm.070216-1710)

    Microsoft MSXML 2.6 3.0 6.0

    Microsoft Internet Explorer 6.0.3790.3959

    Microsoft .NET Framework 2.0.50727.42

    Operating System 5.2.3790

    The OraOLEDB.Oracle driver shows that it is version 10.2.0.3

    Oracle "tnsping sid " works

    SQLPlus (ver 9.2) works

    The driver showed as a provider in SQL Server 2005 as soon as the install was done and I then updated the registry for 3 entries to point to the driver. But I notice that nothting shows in the ODBC management screen for the driver, should it? The only 2 drivers that show are from MS and one is for 32 bit SQL Server and the other is the newer MS SQL Server driver.

    I have serveral linked databases to Oracle that I have built but all of them are 1- on SQL Server 2000 and 2- are 32 bit. If anyone knows of any additional patches (Windows or Oracle) necessary to resolve this I would appreciate the information. Also there are potential firewall issues though the fact that SQLPus can be used seems to rule this out, but if there are any ports associated with OLE I would like to check on them specifically.

    MSDTC is installed and appears configured correctly.

    -- Mark D Powell --

  • YOu try reinstalling the MDAC component and then try you should be seeing all the drivers for linked servers with the MDAC version newly installed.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • I have a 64 bit SQL Server 2005 SP2 on a Windows 2003 x64. The developer said that he needed to use OLE to talk to Oracle so I went to Oracle and downloaded the 64 bit OraOLDDB driver as recommended in a MS note.

    Microsoft does not make a 64 MDAC componennt for Oracle and they won't be making one. You will need to download and install the ODAC (Oracle Data Access Component) from the Oracle site as IanT indicated (with the link).

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

  • Mg, it is the recommended download which is not working. Oracle has updated the download (.3 now instead of .2 in the note)

    What I need is a list of settings to verify at the OS level, registry (added or changed 3 entries under MSDTC) etc... that I can verify.

    Also any information on how to tell that the firewall which I have no access to isn't the problem.

    Suggestion on how to find the actual problem also welcomed.

    What I really need is for someone who set up and uses the 64 but OraOLEDB to tell me that what I should should work or provide an also you need this.

    -- Mark D Powell --

  • OK - I did this on 3 64 bit servers and they all work.

    1 - install the 10g client (.3 version is what I installed)

    2 - install the ODAC package (this is not part of the client)

    3 - validate the TNSNAMES.ORA and SQLNET.ORA - be sure the registry entry points to where the files actually are (since you can connect with SQLPLus, I guess they are)

    4 - stop/start SQL

    5 - the Linked Servers/Providers should now have "OraOLEDB.Oracle" in the list.

    6 - create linked servers to Oracle

    a. the Provider drop down should now include "Oracle Provider for OLE DB"

    I don't know if the client is really needed since I can't find a registry entry for TNS_ADMIN in the 10g keys but rather than take chance, I've installed it since I don't have any place to test my theory.

    Links are working for me. If you have firewall issue, I can't offer any advice.

    MG

    "There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
    Tony Hoare

    "If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.

Viewing 9 posts - 1 through 8 (of 8 total)

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