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.
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.
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'.
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.