Options
BUG: Problem with comparison when a linked server exists.
j.salmon
Posts: 44 Bronze 2
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.
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.
Comments
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!)