Could not drop article. A subscription exists on it.

Question

I'm trying to drop a publication but cannot get past this error when I run the following command:

exec

 

sp_droparticle @publication = N'PublicationName, @article = N'tblName', @force_invalidate_snapshot = 1

Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75

Could not drop article. A subscription exists on it.

When I expand the publication to see the subscriptions, there are none. How do I drop this?

 

Answer 1

use sp_dropsubscription and pass the parameters you find here:

select publisher=sysservers.srvname, subscriber_db=dest_db, 
Subscriber=syssubscriptions.srvname, article_name=name From syssubscriptions 
join sysarticles on syssubscriptions.artid=sysarticles.artid
join master.dbo.sysservers on syssubscriptions.srvid =sysservers.srvid

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 2

Check on the subscriber server if subscription exists. if so drop it first.

---

Mohd Sufian www.sqlship.wordpress.com Please mark the post as Answered if it helped.

Answer 3

I might be very late to the party. Here is an another workaround:

Step1: Find out the 'article_id' for which it is failing for.

use <distribution>
go
select article_id from msarticles with (nolock) where article = '<article>'

Step2:  Find out the subscriber_id's of that article.

use <distribution>
go

select * from mssubscriptions with (nolock) where article_id = <xxx>

Step3: Delete them from the mssubscriptions table

use <distribution>
go

begin tran
delete from MSsubscriptions with (rowlock) where article_id = <xxx>
commit tran

We should now be good to drop that article.

Answer 4

The solution I had is something stumbled upon by accident.

I got the same error as the OP.  Found out my agent was still trying to do a snapshot.  I stopped the snapshot, waited a few minutes, then was able to drop the article with no problems.

Answer 5

Thanks, I needed that!

Answer 6

Thanks Vijay, you might also want to include publication filters in your where clause else it would drop each article from other publications. Better be safe.