I want to drop a table from some of my transactional publications. Can I just
drop the article on the publisher and then be able to delete the table at the
subscriber?
Or do I have to drop the article and re-snapshot the subscriber?
Russell,
using the GUI you'd have to drop the subscriptions then drop the article,
but this is one of those cases where doing things in code is a little
different - you can drop the subscription to the individual article then
drop the article itself:
exec sp_dropsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
exec sp_droparticle @.publication = 'tTestFNames'
, @.article = 'tEmployees'
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the info Paul. If I carryout the two commands can I then just
delete the table from the subscribing database. Doese this process work for
both Merge and Transactional Publications.
"Paul Ibison" wrote:
> Russell,
> using the GUI you'd have to drop the subscriptions then drop the article,
> but this is one of those cases where doing things in code is a little
> different - you can drop the subscription to the individual article then
> drop the article itself:
> exec sp_dropsubscription @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> , @.subscriber = 'RSCOMPUTER'
> , @.destination_db = 'testrep'
> exec sp_droparticle @.publication = 'tTestFNames'
> , @.article = 'tEmployees'
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Russell,
yes - you can drop the table after removing the subscriptions to it and
removing it from the publication.
no - it only works for transactional. For merge you'll need to drop the
subscription entirely before being able to drop the article and the table.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I also use the script to drop the article(s) I don't want.
What I do is:
1) Right click on the publication that you want to remove the table from.
2) Generate DELETE script.
3) Paste into Query Analyzer
4) Find and run the sp_dropsubscription and sp_droparticle commands for the
table you want to run.
5) All done.
This technique, in conjunction with the CREATE script can be used if the
schema of a replicated table needs to be changed.
You generate & SAVE both the DELETE & CREATE scripts. Remove the article from
replication, change the table, then add it back in with the relevant portion
of the create script.
Russell wrote:
>I want to drop a table from some of my transactional publications. Can I just
>drop the article on the publisher and then be able to delete the table at the
>subscriber?
>Or do I have to drop the article and re-snapshot the subscriber?
No comments:
Post a Comment