XY Problem background info:

I have a pull replication publisher in which I want to add or alter a index and have those changes be applied to the subscriber.

The solution to that problem I would like to do is to generate a new snapshot and re-init the subscription.


My Question:

How do I, from the publisher, mark a pull merge replication publication as having a invalid snapshot such that if I did sp_helpmergepublication the snapshot_ready column would return 0.

Doing exec sp_reinitmergesubscription @upload_first = 'true' will cause the subscriber to re-initialize but it does not mark the snapshot as invalid.

I know I could change a publication or article property then change it back and cause to invalidation to happen that way but I would really like to invalidate as the "primary action" instead of having the invalidation be a side effect of some other action.

I am looking for something similar to transactional replication's sp_reinitsubscription procedure which has a @invalidate_snapshot parameter, but I could not find the equivalent for merge replication.

Is there any way to invalidate a merge replication snapshot only without making some other kind of change that has snapshot invalidation as a side effect?

I looked in to the declaration of sp_changemergepublication and found out the way it invalidates the snapshot is a simple query to update the table sysmergepublications

if (@property like 'ftp%' OR @property in ('sync_mode', 'snapshot_in_defaultfolder',
        'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script','compress_snapshot','keep_partition_changes',
        'use_partition_groups', 'publication_compatibility_level'))
and @snapshot_ready = 1
begin
    if @force_invalidate_snapshot = 0 and @snapshot_ready = 1
    begin
        raiserror(20607, 16, -1)
        goto UNDO
    end
    update dbo.sysmergepublications set snapshot_ready=2 where pubid=@pubid and snapshot_ready=1
    if @@ERROR<>0   GOTO UNDO
end

So if you want to invalidate all subscriptions and snapshots just run these two lines.

update dbo.sysmergepublications set snapshot_ready = 2 where snapshot_ready = 1
exec sp_reinitmergesubscription @upload_first = 'true'

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.