Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    Bangalore
    Posts
    28

    Unanswered: distributed transaction

    Hi All,
    I am trying to use distributed transaction (using linked Server).
    But getting the folloing error..

    Some one please help...


    following is the error...

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

    [OLE/DB provider returned message: [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction context in use by another session.]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' ITransactionJoin::JoinTransaction returned 0x8004d00a].

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    The first part of your error can occur when your SQL server and your linked server data source reside on the same machine. Is this the case?

    The other thing that I would check is wether the MSDTC has been started in your servers services.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Apr 2012
    Posts
    4
    Hi Thrasymachus,
    Is there a solution for this error? I am getting this error while using OPENQUERY to execute an SP. Please comment.

  4. #4
    Join Date
    Apr 2012
    Posts
    4
    Hello cheriyan,
    Did u get the fix for this issue? Please let me know if so. Thanks!

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well cheriyan had this problem 8 years ago, so I hope so. Are your ports open?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Apr 2012
    Posts
    4
    Hi Thrasymachus,
    Actually thanks for your reply. Actually i get this issue when i try to debug an SP having OPENQUERY in it. However i get a different error without debugging.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Well, what have tried or checked so far? Do you have some code we can look at? Otherwise we are playing blind chess (I always wondered how blindie did that), and have no starting point to help you fix your issue. You need to describe in detail what you are doing and what you have tried so far. Have you even googled the error message or is someone here supposed to do that for you?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Apr 2012
    Posts
    4
    Hi Thrasymachus,
    Ok, Here is what I did.
    I have piece of code in a Stored Procedure A which uses OPENQUERY to fetch an XML from another Stored Procedure B.
    Here is the sample code:

    CREATE PROCEDURE sp_A
    AS
    BEGIN
    SELECT @HeaderXML = 'EXEC sp_B
    @A = ' + @A
    + ',@B = ' + CONVERT(VARCHAR,@B)

    EXEC('
    INSERT #REFREQXml
    SELECT *
    FROM OPENQUERY(LOCALSERVER,'''+ @HeaderXML + ''' )
    ')
    END


    I tried debugging and got the error that is mentioned here. I suspect the reason would be that the LINKEDSERVER and the SQL Server are in the same machine as you mentioned.
    I did google but ended up without any luck.
    Please let me know in case you have some kind of solution for this.

Posting Permissions

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