Replication Error: Specify all articles when subscribing to a publication using concurrent snapshot processing
November 4, 2020 by Sql Times
Interesting one today:
In our lab environment, while setting up replication on a few nodes, we ran into this interesting error message.
Error:
Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 15
Specify all articles when subscribing to a publication using concurrent snapshot processing.
At first glance, it did not make much sense — as we did not make any changes to the code. But upon further digging, it became obvious the minor change that was made.
@sync_method parameters.
The section where we define the publication, looks like this:
Replication Add Publication
While defining the publication, along with all other parameters, we also mention the way in which the articles & data from Publisher is sent to each Subscriber.
@sync_method defines, which approach to take.
The associated ‘add_subscription’ (which threw the error) looks like this:
Replication Add Subscription
Solution:
When a Publication is defined with @sync_method as ‘concurrent‘, then it creates all articles/tables as one chunk of data (not as individual tables) in native mode. So we need to be sure to define @article parameter in ‘sp_addSubscription‘ with ‘all’ as value (and not individual table names) — since the articles are not available individually.
Replication Add Subscription
Viola !! Now the add subscription works !!
Side note:
If you want to add individual tables/articles in the ‘sp_addsubscription‘, then you want to consider using ‘native‘ for @sync_method.
Replication Add Publication
Replication Add Subscription
Hope this helps,
Leave a comment