0

I'm having trouble dropping a subscription from my replication setup. The subscriptions do not appear (in SSMS, at least) on the subscribers, but do on the publisher. When trying to drop the subscriptions via either SSMS or sp_dropsubscription, I receive he following error message:

Msg 14068, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1353

The subscription status of the object could not be changed.

I dropped all articles from the subscription in SSMS, reran sp_dropsubscription, and still received the same message. However, it appears now the only article that sp_helpsubscription is reporting is a table-valued function that does not exist anymore. A table does however exist with the same name.

I tried renaming the table and re-adding the function, but this didn't work. The function did not show up as checked in the published articles list, and manually dropping it still didn't work, giving me the same error message.

Any ideas? Our publisher is running SQL Server 2008 and our two subscribers are running 2005.

Improve this question

2 Answers 2

Reset to default
1

Just dropped the whole damn distribution and started over. That's all I could figure out.

Improve this answer
1
  • An easier approach - to try first - would be to simply run EXEC sp_removedbreplication 'dbNameHere'; That'll let you keep any publications/topologies going on with other dbs. If you do this, though, just make sure that you manually delete any/all SQL Server Agent Jobs associated with your publication and topology.
    – Michael K Campbell
    Apr 1, 2015 at 19:09
0

I had the same problem. In the publisher database I deleted the missing article from syssubscriptions and sysschemaarticles. Also in the distribution database from MSsubscriptions and MSArticles After doing that I was able to drop and recreate the publication.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .