Error on distributed transaction

JayDJayD Posts: 6
edited October 17, 2006 1:29PM in SQL Compare Previous Versions
About 2 hours into the process of synchronizing two databases, 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].

The stored procedure that is the object of the ALTER script that generates the error does include some joins and updates across linked servers, but I would assume that the distributed transaction error is a result of the transaction processing that SQL Compare is using, since I can cut out the identical ALTER script and run it in SQL Query Analyzer without a problem. If I run the full script generated by SQL Compare in Query Analyzer, it fails with the same error as I get from the immediate synchronization from SQL Compare.

Any advice?

Thanks,

Jay

Comments

  • Hello Jay,

    This happens because the transaction isolation model in SQL Compare's script dictates TRANSACTION ISOLATION LEVEL SERIALIZABLE, which is not supported by some of the database drivers that linked servers use (most notably MS-ACCESS). The solution is to save the SQL Script, locate the line above that I've capitalized, and changed this to

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    This should allow the script to run.
  • Brian,

    I tried that but it didn't solve the problem. I also tried it with

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    with the same result.

    The linked server in this case is another SQL Server.

    Jay
  • Brian - Still awaiting a reply to my previous post in this thread.

    Thanks,

    Jay
  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    Jay,

    Can you just delete the line that sets the transaction isolation level altogether?
Sign In or Register to comment.