SQL Compare OLEDB command line error

lfreijo

I am trying to sychronize a snapshot with a database. I am running this in a command window on my local machine. I am a sysadmin on the server. I am getting the following error. I changed the folder_location\snapshot_file and server.database to something generic for security reasons.

Error: Synchronization of 'folder_location\snapshot_file' and
'server.database' failed: 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].

I restarted DTC on my local machine and on the server. Does anyone have any ideas?

lfreijo

I should have added the original command line code I was using:

sqlcompare /snapshot1:"folder_location\snapshot_filesnp" /server2:server /database2:database /synchronize /force

Brian Donahue

You can get a distributed transaction error when you run a SQL Compare synchronization against a database that uses linked servers. Because not all drivers support the same transaction isolation levels supported by a native SQL Server driver, the update will fail because SQL Compare uses serializable transactions. The workaround is to save the script rather than run it directly from SQL Compare, then change the line near the top from SERIALIZABLE to something like READ COMMITTED and run the script in SSMS.

The nasty side-effect is that if anyone else is making schema changes to the database at the same time, the way I understand it the database can be left in an inconsistent state.

lfreijo

Thanks Brian! It was something kind of similar, basically some orphaned procs that would no longer compile. I finally ran the same compare using the GUI and it revealed a lot more information than the command line including the actual proc that would not compile. Thanks for your input!