Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75 Could not drop article. A subscription exists on it.

When we try to delete a Publication, It says "Could not drop article. A subscription exists on it". But, subscriber also deleted! (Still was getting the following Err when I delete the Publication!)

So, I Tried to delete the article first using the script given below!

Use  PublicationDB
Go
sp_droparticle  @publication = N'Publication Name',

@article = N'Article Name',
@force_invalidate_snapshot = 1
Go


But, I could not delete the article! and occured the following Error message!

Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75
Could not drop article. A subscription exists on it.

But, No subscription was not there for the Publication!

So, I could realize that the Subscription not removed properly but may be partially(May be some of the flag not updated internally)!

So, I tried to remove the Subscription using the following way...!

Use  distribution
Go
Delete
MSS From MSsubscriptions MSS JOIN msarticles MSA

On (MSS.article_id = MSA.article_id)
Where MSA.article ='Article Name you trying to Delete'
And MSS.publisher_db ='Publisher DB Name'
Go

I removed the subscription for the particular publication and I tried to remove the article now

Use PublicationDB
Go

sp_droparticle @publication = N'Publication Name',
@article = N'Article Name',
@force_invalidate_snapshot = 1
Go

Wow...! I removed the article successfully from the Publication without any error!

Finally, I could able to remove the publication also without any error! :)

No comments:

Post a Comment