How can we help you today? How can we help you today?

Error on distributed transaction

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
JayD
0

Comments

4 comments

  • Brian Donahue
    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 Donahue
    0
  • JayD
    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
    JayD
    0
  • JayD
    Brian - Still awaiting a reply to my previous post in this thread.

    Thanks,

    Jay
    JayD
    0
  • Brian Donahue
    Jay,

    Can you just delete the line that sets the transaction isolation level altogether?
    Brian Donahue
    0

Add comment

Please sign in to leave a comment.