Add SQL stored procedure to replication

Question

Hi All,

I want to know the procedure to be followed for adding a stored procedure to replication.

I have added manually using the replication wizard and navigated to the snapshot agent and selected "View snapshot agent status" and started the agent which returns a message "A snapshot was not generated because no subscriptions needed initialization". Please advise if adding a stored proc doesn't require a new snapshot?

If not, please let me know the correct procedure to be followed.

I really appreciate any help you can provide.

Answer 1

Hi @Kranthi DBA ,

> Please advise if adding a stored proc doesn't require a new snapshot?

After adding the articles to the existing publication, we must re-initialize the subscriptions with a new snapshot. To do that, right click on the publication and select Reinitialize All Subscriptions.

Refer to MS document Add Articles to and Drop Articles from Existing Publications or the third blog Add new article to existing publication for SQL Server Transactional Replication.

If you want to add new article to replication without generating the whole snapshot file. To avoid generating a snapshot for all articles when adding a new article, publication property immediate_sync must be set to 0 and then call sp_addarticle, followed by sp_addsubscription. If it is pull subscription, you must call sp_refreshsubscriptions. Then generate a snapshot and this process will yield to generate snapshot only for the newly added articles. Please reading below blog to get more detail.

How to Add/Drop articles from existing publications in SQL Server

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".