Sql-server – How to manually invalidate a pull merge replication snapshot from the publisher

merge-replicationreplicationsql serversql-server-2008

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?

Best Answer

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'
Related Question