Error on distributed transaction

JayD

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

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.

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

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

Thanks,

Jay

Brian Donahue

Jay,

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