Replication involved in SQl 2000 and SQl 2005

Replication involved in SQl 2000 and SQl 2005
Publication: database in SQL 2000
Subscriber: database in SQL 2005

Case: Need to drop article from existing publication.

Ex: To drop article “Employees” from publication ‘ReplTest’ which is in sql server 2000 publication and database 'ReplTest'

Connect Publication server and you might follow below steps as in sql 2000 removing article from GUI doesn’t give you option to tick off the article and remove it from publication.

Use 'ReplTest'
GO
sp_droparticle ‘ReplTest’, 'Employees',

Server: Msg 14046, Level 16, State 1, Procedure sp_droparticle, Line 161

Could not drop article. A subscription exists on it.

Above error say’s if you want to drop article from publication then you need to first drop the subscription and then remove article from publication.

And dropping subscription and re- adding the same would require you to run fresh snapshot ,This might be huge pain to customer if snapshot size is very large say 150 GB Appox(6 hours). And then again initial synchronization may take (6 hours).

Total 12 hours maintenance.

Here what I found something interesting and would like to share.

If you want to remove article from publication involved with sql 2005 subscription.

Open SQL 2005 SSMS and connect publication SQL 2000 Server and follow below steps

·Right click publication properties àarticle and remove the article. (Here it allows us to remove article from same publication not allowed from Enterprise or Sql 2000 QA)

You might get below screen



The screen above show subscription needs to be reinitialize but when you run the snapshot it would not generate new snapshot which is good , something foolish error from Microsoft saying subscription be reinitialized from new snapshot.
Thus if you don’t want the whole snapshot to be run as per above scenario, always drop article from SQl 2005 SSMS the same way shown above.

Also if you need to add article just add the same way as you remove from publication and run the snapshot it would run for only one/ number. of added articles.

Please revert and let me know if this article helps you or you have any query.

Thanks,
Ajay Varma,MCTS