sql server replication: Dropping an article -- sql server replication

Re: Dropping an article

Gabriel D via SQLMonster.com
7/22/2005 12:00:00 AM

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.

[quoted text, click to view]

Re: Dropping an article

Russell
7/22/2005 2:26:03 AM

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.

[quoted text, click to view]

Re: Dropping an article

Paul Ibison
7/22/2005 9:10:38 AM

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)

Re: Dropping an article

Paul Ibison
7/22/2005 10:57:48 AM

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)

Cannot drop article from publication because its snapshot is run e

pramod
8/9/2005 12:02:46 AM

SUBJECT : Cannot drop article from publication because its snapshot is run
error

Needed urgently. I am getting this error all the time when i want to drop
article from publication . I am using merge replication .

ERROR:
Server: Msg 21338, Level 16, State 1, Procedure sp_dropmergearticle, Line 88
Cannot drop article 'Promotion' from publication 'm10pocketpc' because its
snapshot has been run and this publication could have active subscriptions.

i am using this command to drop article:

exec sp_dropmergearticle @publication = N'm10pocketpc', @article =
N'Promotion'
GO

--
yours truly,
Pramod Renikindi,
creativesoft,
australia

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server replication group.