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. SQL Compare uses TRANSACTION ISOLATION LEVEL SERIALIZABLE to protect the schema from interference while the update is performed. For this reason, we will not be changing the isolation level to be compatible with distributed queries.

Workaround

1. Editing a migration script

• Save a migration script to a file using the deployment wizard of SQL Compare after a database comparison by clicking the Save SQL Script button.
• Once the script is saved to disk, locate the line: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE and change this to SET TRANSACTION ISOLATION LEVEL READ COMMITTED. This will allow distributed query DDL to be submitted.
• Run the script in SQL Server Query Analyzer or Management Studio.

2. Disabling transactions in deployment scripts

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