I just want to mention that there's also a distributor which is on either of these servers or another, and likely in a database named distribution. You know you have your publisher (the source database), and at least one subscriber (the destination database), and that these are two different servers. Also most of the other error messages are just follow ons because you haven't been able to remove all the subscriptions (or at least SQL thinks so). Restoring the database will break replication, so that's normal. I'm not 100% certain that disabling and enabling the replication is what actually fixed the problem, but it is definitely worth trying if the replication gets messed up. I ran the replication set up script, generated a new snapshot and every thing is now working properly. I refreshed the Local Publications node in SSMS and sure enough it had gone. This time it complained that the publication did not exist. This complained that replication was not enabled on the database. So I tried the sp_droppublication command. The sp_dropsubscription command complained that no subscriptions exists. The one change he did make before giving up was to disable the replication. He tried a few things but didn't get very far. I guess this is the equivalent of switching it off and then back on again.Ī workmate had a go at trying to fix it. It appears that disabling and re-enabling replication probably fixed the issue: exec sp_replicationdboption = N'DatabaseName', = N'publish', = N'false'Įxec sp_replicationdboption = N'DatabaseName', = N'publish', = N'true' Fortunately, this isn't a production environment.
ISTUDIO PUBLISHER ADDING DATA TABLE SOFTWARE
I don't know what is going on here and how do I fix it?īTW this is what happens when you give a software developer who knows just enough to be dangerous the keys to the database. I have also restarted SQL Server - didn't help. This seems to have worked - I have now got rid of the articles, but I still get the same 'Cannot drop the publication because at least one subscription exists for this publication' error when I try to delete the publication. So I tried an alternative method of deleting the article, DELETE FROM sysarticles. Ok, so I try starting the Snapshot Agent and I get this internal SQL exception: The SQL command 'sp_MSactivate_auto_sub' had returned fewer rows than expected by the replication agent. Msg 14046, Level 16, State 1, Procedure sp_MSdrop_article, Line 75 Run the Snapshot Agent again to generate a new snapshot. Ok, so I try to drop the articles: EXEC sp_droparticle = 'PublicationName', = N'all'Īnd get this error: Invalidated the existing snapshot of the publication.
A subscription exists on it.Ĭhanged database context to 'DatabaseName'. This gives the following error message: Could not delete publication 'PublicationName'.Ĭould not drop article. Looking on the subscriber server, SSMS > Delete. SELECT * FROM syssubscriptions shows no results. I'm not sure exactly what I did, but now it is in a completely messed up state and I can't fix it.įirst, I try to get rid of the subscription (on the publisher server): EXEC sp_dropsubscription = 'PublicationName', = N'all', = 'SubscriberServerName' Assuming the database restore would break the replication, I tried to delete the replication and re-create it (we have a script to re-create it from scratch).
The database uses replication to publish to a different server.