15 September 2011

Troubleshooting Oracle Link Server Issue

Most of the time, due to different business requirements we do have to work on various RDBMS systems, Oracle and MS SQL Server are the widely used and popular RDBMS. Sometimes we need to import/export data from/to SQL Server and for that we’ve to use Link Server feature of MS SQL Server.

In one of my recent project, a critical application has a job that pulls in the data from an Oracle database, we have a DTS Package and some job scheduled for this task. since couple of days we’ve observed below error message in the job history and while running ad-hoc queries. I am penning down the error message and the solution (rather, temporary workaround) we have found out :

1) The operation could not be performed because the OLE DB provider 'MSDAORA' was unable to begin a distributed transaction.

==> In this case, rebooting MS DTS service  helped us.

2) The maximum number of active transactions that the MS DTC log file can accommodate has been exceeded.  You must increase the size of the MS DTC log file if you wish to initiate more concurrent transactions.

==> In this case, increasing Log file size helped us.

MSDTS_Log

3)Server: Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDAORA" for linked server "OraLnkSvr" reported an error. The provider ran out of memory.

4) Server: Msg 7330, Level 16, State 1, Line 1
Cannot fetch a row from OLE DB provider "MSDAORA" for linked server "OraLnkSvr".
OLE DB provider "MSDAORA" for linked server "OraLnkSvr" returned message "Out of memory.".

==> In case 3 & 4, rebooting SQL Server service helped us.

 

Hope this helps.

 

-- Hemantgiri S. Goswami (http://www.sql-server-citation.com )

2 comments:

  1. Hi Hemant,

    First of all wishing you and your family a very happy Diwali .

    Need help :

    I need to access Oracle tables inside Sql server using linked server . but getting error while fetching data of oracle table inside sql server by using linked server .

    Do you have detail how to handle using link server... or any other work arround to access Oracle table from sql server .

    Regards
    Ajay Pandey

    ReplyDelete
  2. Ajayji,

    This link will help you achieve your goal
    http://support.microsoft.com/kb/280106

    ReplyDelete

I appreciate your time, thanks for posting your comment. I will review and reply to your comment as soon as I can.

Thank you
Hemantgiri