Specify all articles when subscribing to a publication using concurrent snapshot processing.

Chris Skorlinski
Microsoft SQL Server Escalation Services

I recently encountered this error when setting up Transactional Replication via SQL scripts.

Msg 14100, Level 16, State 1, Procedure sp_MSrepl_addsubscription, Line 533
Specify all articles when subscribing to a publication using concurrent snapshot processing.

Looking at my create Publication script I see the default @sync_method = N'concurrent' being set in sp_addpublication.  Nothing I want to change here.

image

Further in my script I see the call to sp_addsubscription.  From the BOL, this syntax look correct.  I’m running sp_addsubscription once for each article for this subscriber.

image

image

While the script looks correct, I wondered what the script would look like using default setting created by the Replication Wizard.  To test, I recreated the publication using the Wizard, then scripted out the publication.  I noticed Replication Wizard used had only 1 call to sp_addsubscription with option @article = N'all'.

image

Now the error makes sense.  When using Current Snapshot Processing I need to make 1 call to sp_addsubscription  with option and specify ALL articles for this subscriber.