Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194

    Exclamation Unanswered: Linked Server Problem

    Folks i've two SQL SERVER, SERVER1 and SERVER2. I am updating a table at SERVER1 by joining a table with SERVER2. The query works fine, but update fails.

    UPDATE a
    set col1=b.col2
    FROM mytable a JOIN SERVER2.mydb.dbo.mytable b ON a.id=b.id
    WHERE a.date=getdate()


    I get the following error:
    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
    [OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].


    Plz advise!

  2. #2
    Join Date
    Dec 2003
    Posts
    22
    You need to start the "Distributed Transaction Coordinator" service on both servers.

    Roby2222

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    It's already running on both of the servers.

  4. #4
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Check whether any firewall rule is obstructing the DTC access.

    To work around this behavior, install network DTC access on both servers:
    1. Click Start, and then click Control Panel.
    2. Click Add or Remove Programs, and then click Add/Remove Windows Components.
    3. In the Components box, click Application Server, and then click Details.
    4. Click to select the Enable network DTC access check box, and then click OK.
    5. Click Next, and then follow the instructions that appear on the screen to complete the installation process.
    6. Stop and then restart the Distributed Transaction Coordinator service.
    7. Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  5. #5
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Thanx for the guidance, Satya.

    I would let ya know after i restart the machines(on production).


    Howdy!

Posting Permissions

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