Adding article to existing publication without generating a full snapshot

Written by Sugeshkumar Rajendran. Posted in Scripts

This SP can be used to add articles to the existing publication without starting snapshot for all the tables.
When Snapshot is started it will create snapshot only for the newly added article to the publication

CREATE PROCEDURE sp_addarticles(@pubname VARCHAR(100),
@article VARCHAR(100),
@subscriber VARCHAR(100),
@sub_db VARCHAR(100),
@dest_article VARCHAR(100) = NULL)
AS
BEGIN
DECLARE @immsync INT
DECLARE @allowanyon INT

SELECT @immsync=immediate_sync, @allowanyon=allow_anyonmous FROM syspublications

sp_addarticle @publication=@pubname
@article=@article
@source_table=@article
@force_invalidate_snapshot=1
GO

IF @allowanyon = 1
BEGIN
EXEC sp_changepublication
@publication = @pubname,
@property = N'allow_anonymous',
@value = 'false'
GO
END

IF @immsync = 1
BEGIN
EXEC sp_changepublication
@publication = @pubname,
@property = N'immediate_sync',
@value = 'false'
GO
END

EXEC sp_addsubscription
@publication = @pubname
@article = @article,
@subscriber = @subscriber
@destination_db = @sub_db
@reserved='Internal'
END
VN:F [1.9.17_1161]
Rating: 0.0/5 (0 votes cast)
VN:F [1.9.17_1161]
Rating: 0 (from 0 votes)

Tags: , , ,

Trackback from your site.

Leave a comment

*

Recent Comments

Amit Bhatt

|

Hi Deepak,

Thanks for such a nice article.

You missed one thing to add in code:
@article = ‘all’,

Hence the script will be like this:
EXEC sp_addsubscription
@publication = ‘mypublication’,
@article = ‘ALL’,
@subscriber = ‘Subscriberservername’,
@destination_db = ‘mydestinationdbname’,
@reserved=’Internal’

Error 18486 | Platformblog

|

[...] SQL-Articles » Troubleshooting Login failed Error 18456This is one of the infamous error message (and number) that most of the DBAs …. 18486. Login failed for user ‘%.*ls’ because the account is currently locked out. [...]

VidhyaSagar

|

Naveen,
I’ll check this out and get back to you.

balakiran

|

Thanks man, Very simple & easy to understand !!!!