SQL Compare - can I ignore Linked server requirement?

Compares and synchronizes SQL Server databases, backups and scripts.

Moderators: JonathanWatts, Chris Auckland, David Atkinson, eddie davis, Anu Deshpande, Michelle Taylor, alice.easey, james.billings, chengvoon.tong

SQL Compare - can I ignore Linked server requirement?

Postby shiftbit » Wed Mar 06, 2013 9:15 pm

There is one stored procedure that requires a linked server and the deployment will fail.

I get an error similar to this:

[7391] The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "server name here...." was unable to begin a distributed transaction.
OLE DB provider "SQLNCLI" for linked server "server name here..." returned message "The transaction manager has disabled its support for remote/network transactions.".

Is there a setting to ignore this check, and just apply the changes?
shiftbit
 
Posts: 9
Joined: Wed Mar 06, 2013 12:37 am

Postby shiftbit » Wed Mar 06, 2013 9:54 pm

I think I found the option I needed - "Dont use transactions", this let me get past the one problem stored procedure thats using a linked server, but if its not configured on my machine, then the change deployment fails.

In this casae, Im trying ti update my local instance with the changes in the DB on the server, and to take/override what I have with everything from that instance.
shiftbit
 
Posts: 9
Joined: Wed Mar 06, 2013 12:37 am

Postby Brian Donahue » Mon Mar 11, 2013 12:29 pm

Thanks for updating us. SQL Compare will not recreate linked servers, as they are server-level objects and outside of the database container.

Linked server drivers usually have limits on their capability, for instance they may only support certain isolation levels. You can either disable transactions, or the other option is to specify the isolation level. In most cases, READ COMMITTED with do the job and not have to disable transactions entirely.
Brian Donahue
 
Posts: 6670
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Compare 10

Who is online

Users browsing this forum: No registered users and 0 guests