July 21, 2004 at 7:01 pm
Hi everyone,
I am using SQL Server 2000 and I have set up a replication which is in production. My question is would I be able to take off a table in the existing publication? I open the properties of the publication in SQL Enterprise and go to Articles tab and I could see all tables that have been published (there are ticks next to them) but it would not let me to untick the desired table. However I could add any other unpublished table to the existing publication.
The reason I would like to be able to take a particular table off the publication is becos the developer needs to update that particular table and then we'll put it back into the replication. If there's no way to do it in Enterprise, is there any other workaround?
I'd like to thank in advance to anyone who'd input any thought about this.
July 22, 2004 at 1:17 am
Take a look at the 'sp_changearticle' stored procedure. This should let you remove a table from an existing publication (depending on your replication type etc)
Steven
July 22, 2004 at 6:50 am
To remove an Article(Table) from a publication you can't have subscriptions to that article.
First you need to remove all the subscriptions with sp_dropsubscription @article = 'The table name'.
Once the article doesn't have any subscriptions then you can drop the article from the publication with sp_dropArticle.
July 25, 2004 at 7:22 pm
Thank you guys, particulary Racosta. Your method works. I was able to take a table off (even in Enterprise) after dropping the subscription. It makes sense that if the table is kinda in used one wont be able to modify it. Once again thank you all.
September 21, 2004 at 7:55 am
So, what's the verdict on sp_changearticle?
Is it possible to use it to remove the article without reinitializing the whole subscribtion?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply