Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    80

    Unanswered: Distributed transaction

    Hi all,
    I am trying to merge data of 2 tables on different servers with an insert statement.

    INSERT INTO SERVER1.db.owner.table
    select s2.* from SERVER2.db.owner.table as s2
    LEFT JOIN SERVER1.db.owner.table as s1
    ON s1.key=s2.key
    where s1.key is null

    I got this 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].


    while the select query is giving the result.
    I have done simmilar inserts on some other tables which worked fine

    I have created sp_addlinkedserver.
    and DTC set on both servers.

    any help will be greatly appreciated

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Did you execute this on Server1 or Server2? What OS and sp are on the respective servers?

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Are you doing it within a transaction? You can successfully bypass engagement of MSDTC by preceeding your INSERT with SET IMPLICIT_TRANSACTIONS OFF.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Mar 2004
    Posts
    80
    Thanks for the reply.
    I have tried it on both servers .
    os:w2k and sp3.
    It is not with in transaction.
    error still occurs.

Posting Permissions

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