Results 1 to 8 of 8

Thread: Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error

  1. #1
    Join Date
    Jan 2007
    Posts
    36

    Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error

    My link server was working just fine until friday evening.
    It stopped worked over the week end.
    with and error Error 7399: OLE DB provider 'MSDAORA' reported an error. OLE DB error .

    ---my oracle 10g client is working just fine
    --TNS names looks fine
    ---i recreated the link but i am still getting the same error.

    I need your help because a lot of jobs are using that link on Monday it is going to be crazzzy.

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Reboot sql server if possible or try odbc provider for linked server.

  3. #3
    Join Date
    Jan 2007
    Posts
    36
    rmaio,
    is to stop sql server the same as to reboot?
    What i did i stopt sql server and restarted, and then i used ODBC Connection but i keep getting the same error.

    ---I went to ODBC and setup oracle connection .

    Any new ideas

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    Stopping sql is different from rebooting server since it can't recover os or network issues.

  5. #5
    Join Date
    Jan 2007
    Posts
    36
    Thanks, i am gonna have to wait until tmwrw to restart the server.

  6. #6
    Join Date
    Jan 2007
    Posts
    36
    rmaio,
    We restarted SQL server and we still had the same problem,the problem was on Linux server which runs oracle, so we stoped all oracle services and when we restarted the server BINGO, i am back in business.

    Thanks man

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,931
    Use the Microsoft driver for Oracle, do not use Oracle's driver. This has been a problem with Linked server for a long time.

  8. #8
    Join Date
    Mar 2011
    Posts
    1

    Exclamation Linked server from SQL Server 2005 to Oracle 11i

    When adding a linked server, use a SQL script to gain more control..

    BEFORE creating a linked server from SQL to Oracle, you HAVE to install the Oracle client (I installed the 1 GB package, second option when running the Oracle installation).

    After installing the Oracle client you HAVE to reboot the server (restarting the SQL server service will not do it).

    When this is done, I recommend you create a test connection before creating the linked server. There are several ways of doing this, but here is a simple way that always work for me:

    1) Right click on your desktop to create a new .txt file. Rename the file extention from ".txt" to ".udl", and answer "Yes" when asked if you want to rename the file
    2) On the "Provider" tab select "Oracle Provider for OLE DB". (in fact.. in most cases the Microsoft OLE DB Provider for Oracle" will work, but this will fail in Linked Server!)
    3) Click on the "Next" button
    4) Enter this into the "Data source" field: <IP>:<Port>/<SID> Example: 140.10.10.20:1526/mydb
    5) Enter a user name and pwd in the boxes further down, and click on "Test connection".

    If this works, you know the provider, username, pwd etc. works.

    Then.. when you have done the above, add a linked server.

    Some forum posts (here and other places), tells you to use the MSDAORA provider. I found it to be the other way around. When I used the MSDAORA provider, it failed. When using the OraOLEDB.Oracle provider it worked!

    Script for creating the linked server (you need to enter values between the <..>):

    exec sp_addlinkedserver
    @server= '<Linked Server Name>',
    @provider='OraOLEDB.Oracle',
    @srvproduct='Oracle',
    @datasrc='<IP>:<Port NB! Rembember Listner on remote Oracle>/<SID>'

    Example:

    exec sp_addlinkedserver
    @server= 'ORALinkedServer',
    @provider='OraOLEDB.Oracle',
    @srvproduct='Oracle',
    @datasrc='140.10.10.20:1526/mydb'

    When this is done, you need to configure the permission on the linked server. This can be done by stored procedure, or by selecting properties on the new linked server, security, and select using remote user and enter user name and password.

    Good luck!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •