asked
Why can't I remove a replication article via T-SQL command sp_droparticle?
I am trying to semi-automate the deployment of database objects to allow the addition or removal of articles from a transactional replication. After studying the replication programming guide on BOL, I built some sprocs to run the necessary commands. This works fine for adding articles to a publication, but as soon as I want to remove articles from the publication using `sp_droparticle`, I am told: Msg 14046, Level 16, State 1, Procedure sp_MSrepl_droparticle, Line 286 Could not drop article. A subscription exists on it. If I remove the article(s) by hand through SSMS everything works fine. If there are no subscriptions, I can run the drop article command (as suggested by the error message). I am flummoxed! Any Ideas? P.S. I don't want to remove the subscription each time I want to remove an article! :)
not worthy of an answer but this is symptomatic of the problems we had with replication. It seems to get confused somewhere and doesnt know if there is/isnt replication in place. Never found a way around it. Changed over to log shipping.
Woops, google/BOL fail for me! It seems I overlooked that you can run `sp_dropsubscription` for an article, specifying which subscribers should stop subscribing to this article.: EXEC sp_dropsubscription 'Publication','ArticleToDrop','Subscriber','SubscriberDB' Once no subscribers are left for the article it can be dropped using `sp_droparticle`. [puts dunce hat on and sits in the quiet corner]
