SQL Server Performance

Unable to begin a distributed transaction using Linked Server to an Oracle DB

Discussion in 'SQL Server 2005 General Developer Questions' started by walidha, Feb 9, 2009.

  1. walidha New Member

    I am using SQL 2005 on Server 2003.
    I created a linked server to an Oracle DB 8.x.
    I can select/update/delete from all tables in Oracle using the linked server with no problem.
    However when I use "Begin Transaction" or "Begin Distributed Transaction" before a delete/insert/update I receive the following error:
    Msg 7391, Level 16, State 2, Line 2
    The operation could not be performed because OLE DB provider "MSDAORA" for linked server "cmsp21" was unable to begin a distributed transaction.
    Could anyone please help ?
  2. rohit2900 Member

  3. walidha New Member

    The MSDTC service, on the Server 2003 where SQL2005 runs, is started and configured.
    Oracle server runs on Windows 2000 server. I am not sure if I need to configure something special on it. However I made sure that the MSDTC service is installed and running on this server.
    I followed the steps on the Microsoft site through the link you sent me and still getting the same error when starting a distributed transaction.
    I can do Update/Insert/Delete/Select from SQL2005 to Oracle through the linked server. However when I use "BEGIN DISTRIBUTED TRANSACTION" before a DML statement I get that error.
    Please help!
    Thanks for your time.
  4. satya Moderator

    Are you passing 4 part query or using OPENQUERY within your statement to Oracle provider?
  5. walidha New Member

    Yes I am using the 4 parts. However I am not using OPENQUERY statements.
    Like I said earlier, I can run select statements from Oracle using the 4 parts linked server with no issues.
    example: select * from [cmsp21]..cms30.Classification
    the cmsp21 is the linked server. This statement runs perfectly.
    Also I am able to do delete, update, and insert into Oracle using the 4 parts Linked sevrer. But I need to add protection to my DML statements by using the Begin Distributed Transaction, and this is when I receive the error. What is it that I am doing wrong?
    Thanks for your time.
  6. Elisabeth Redei New Member

    Isn't there a "Force any transaction" privilege that needs to be set in your Oracle database? Could that be it?
  7. satya Moderator

    The first thing you search on error 7391 on the web this KBA http://support.microsoft.com/kb/306212 is referred, it highlights that:
    If you receive the error 7391 from a process such as replication or Data Transformation Services (DTS), you may also receive the error message when the code contains a BEGIN DISTRIBUTED TRAN statement.

    Make sure that you test the code that has the BEGIN DISTRIBUTED TRAN statement in Query Analyzer.
    Start Query Analyzer, and then run this statement by logging on to the server with the same account as the SQL Server and the SQL Server Agent startup account. This will help to narrow your troubleshooting focus.

    Also go through the common resolutions that are mentioned in that article.

Share This Page