Tuesday, March 23, 2010 2:29 PM
I'm trying to drop a publication but cannot get past this error when I run the following command:
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?
Tuesday, March 23, 2010 6:50 PMModerator
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
Tuesday, March 23, 2010 10:13 PMCheck 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.
Sunday, June 03, 2012 12:55 AM
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.
select article_id from msarticles with (nolock) where article = '<article>'
Step2: Find out the subscriber_id's of that article.
select * from mssubscriptions with (nolock) where article_id = <xxx>
Step3: Delete them from the mssubscriptions table
delete from MSsubscriptions with (rowlock) where article_id = <xxx>
We should now be good to drop that article.
Friday, July 06, 2012 1:10 PM
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.
Thursday, October 18, 2012 5:51 PMThanks, I needed that!
Wednesday, December 19, 2012 5:36 PM
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.