BUG: Problem with comparison when a linked server exists.

j.salmon

Setup:
Server1 = source database
Server2 = destination database (this database will be updated).
Server2 contains a linked server pointing to server1 for running various queries with.

When running SQL compare from the GUI - the compare runs fine and can be synchronized successfully.

When running the same project, but from a arg file, the process errors off with the following:
Error: Error comparing server1/db1 vs server2/db1 : The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "server1" was unabled to begin a distributed transaction. OLE DB provider "SQLNCLI" for linked server "server1" returned message "The partner transaction manager has disabled its support for remote/network transactions.".

What is SQL compare doing differently when running from an argfile? Why is it even looking if a linked server exists? Please advise on how to get this working again.

j.salmon

I got this handled by using the NoSQLPlumbing option in the argfile. The errors returned by sql compare are a little confusing since it is referencing a linked server on the dest server.

Jon

Brian Donahue

Hi Jon,

Thanks for posting. Your solution is a good one -- I'll get that documented somewhere. The root of the problem is the transaction isolation level specified in SQL Compare's script as described here:

http://www.red-gate.com/support/kb/KB200708000143.htm

Although your workaround is just as effective (no transactions=no transaction isolation level issue!)