Sunday, February 26, 2012

drop 1 article from publication and add it back, sql server 2000 s

Recently I have experienced problems with subscriptions getting out of sync.
Some of the published tables are very large and are connected to the
subscriber via t1 connection. To avoid re-initializing the entire publication
I ran sp_dropsubscription followed by sp_droparticle, both specifying the
article that is out of sync. Then I opened the publication properties from
the replication monitor, went to the articles tab, and added the article back
to the publication. When I run the snapshot again a new snapshot is generated
for that article and it subsequently is pushed to the subscriber. Sometimes
the artilces will get out of sync again in a day or two and some articles
stay synchronized without any problems.
The script I run to drop the article is:
use db1
go
declare @.p varchar(128)
declare @.a varchar(128)
declare @.db varchar(128)
set @.p = 'db1_Publication'
set @.a = 'tbl1'
set @.db = 'db1'
exec sp_dropsubscription @.publication = @.p ,@.article = @.a
,@.subscriber = 'rptServer' ,@.destination_db = @.db
exec sp_droparticle @.publication = @.p
,@.article= @.a
,@.force_invalidate_snapshot = 0
I believe this is the correct way to drop 1 article from a publication that
contains many articles and adding it back again to reinitialize, but I
haven't been able to find any examples of this anywhere.
The question I pose to the replication gurus is, does this work? I am unsure
at this point due to the inconsistent results I've been getting.
Thanks,
Jason
Jason,
this is much the same way I do it as well (see
http://www.replicationanswers.com/AddColumn.asp). I would like to
investigate the non-synchronization of these tables though. Do you see any
commands in the distribution database for these tables (sp_browsereplcmds)?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

No comments:

Post a Comment