Error

When deploying to a database, either through SQL Compare, SQL Packager or when running a SQL script produced by them, the following error message may be displayed:

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].

This error message is displayed when a SQL Server programmability object such as a stored procedure or function references a linked server or distributed query connecting to another server. The script fails because the transaction isolation level selected to run the SQL Compare script is incompatible with distributed queries. By default, SQL Compare uses TRANSACTION ISOLATION LEVEL SERIALIZABLE to protect the schema from interference while the update is performed. 

Solution

Use a different transaction isolation level

• In Tools–>Application Options, change the Transaction Isolation Level to Read Committed.
• Deploy your changes as usual.  The new transaction isolation level should be correctly set at the top of the script produced by Compare.

Alternative workaround

Do not use a transaction

• Go into the project settings
• Click the options tab
• select 'do not use transactions in deployment scripts'.

This eliminates the issue by not breaking the script up into transactional units.