Comments
4 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 -
Jay,
Can you just delete the line that sets the transaction isolation level altogether?
Add comment
Please sign in to leave a comment.
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