Comparing with replication

msigmanmsigman Posts: 11
edited August 26, 2011 4:42AM in SQL Compare Previous Versions
We just purchased the software and noticed that it doesn't seem to work once we turn replication on. It fails immediately with this error: 
Error: Synchronization of 'qadb01\devdb.Monarch' and 'qadb01.Monarch' failed:
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ
isolation levels.

I tried adding the /Option:NoTransaction but then I get this error: 
Error: Synchronization of 'qadb01\devdb.Monarch' and 'qadb01.Monarch' failed:
Cannot alter the table 'ADDRESSES' because it is being published for
replication. Could not drop constraint. See previous errors.

What is the recommended approach to synchronizing two databases that have replication enabled? If it makes a difference, I'm using the command line.
· Share on Twitter

Comments

  • peter.peartpeter.peart Posts: 364
    Hi there,

    Thanks for your post.

    The problem is SQL Compare is attempting to alter a table that is part of replication, and the error is being returned from SQL Server.

    Essentially, if objects that are used in replication are synchronized, errors may occur. For example, SQL Compare cannot drop a table if it is used for replication.

    The only workaround, and I know it's not much of a workaround, is to disable replication before you perform the update. We do have this logged as a feature request, but I don't know when it's likely to be implemented I'm afraid.

    Pete
    Peter Peart
    Red Gate Software Ltd
    +44 (0)870 160 0037 ext. 8569
    1 866 RED GATE ext. 8569
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    Out of interest, what version of SQL Server are you using?

    David Atkinson
    Red Gate Software
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
  • msigmanmsigman Posts: 11
    SQL Server 2008. Is it possible to disable replication with the command line or batch file? I'm using this in an integrated build environment and need an autonomous solution...
    · Share on Twitter
  • David AtkinsonDavid Atkinson Posts: 1,458 Rose Gold 2
    That's a good question. I guess if it can be done in SQL, then sqlcmd.exe could be used?

    I'd recommend you post your question on http://ask.sqlservercentral.com/ as this is frequented by many experts in the field.
    David Atkinson
    Product Manager
    Redgate Software
    · Share on Twitter
Sign In or Register to comment.