MS DTC

  • tfifield

    SSCrazy Eights

    Points: 9655

    I'm hoping someone here has had the same problem and knows how to fix it. I have a server and a linked server - different machines.

    On machine A, in a stored procedure, I begin a transaction and do some inserts and updates. If successful, I then have to update a record on machine B. I can do any update I want on machine B, unless it's within the context of a transaction on machine A.

    I keep getting the message:

    OLE DB provider "SQLNCLI" for linked server "PatSvr" returned message "No transaction is active.".

    Msg 7391, Level 16, State 2, Line 14

    The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "PatSvr" was unable to begin a distributed transaction.

    I've been on some forums and tried everything suggested there. Both machines have DTC running. I went into component services on both machines for MS DTC and enabled:

    Network DTC Access

    Remote Client

    Enable TIP

    Any suggestions?

    Todd Fifield

  • GSquared

    SSC Guru

    Points: 260824

    I think I've had to enable RPC to get DTC to work, in similar situations. It was a few years ago, so I don't remember all the details, but it was something like that.

    The other option, in some cases, is to move the update on the remote server outside the transaction. Often, that's not an option, in order to keep the databases consistent, but sometimes it can be used.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Adam Angelini

    SSCrazy Eights

    Points: 8538

    we usually add the SQL service accounts to the local groups "distributed COM users" and SQLSERVER2005DTSUSER

    if that doesn't get you there, I have one more thing you can try, let me know.

  • tfifield

    SSCrazy Eights

    Points: 9655

    Thanks guys. I appreciate the input.

    I really can't move the update outside of the transaction - not an option.

    I'll give the suggestions a try.

    Todd Fifield

Viewing 4 posts - 1 through 4 (of 4 total)

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