I had 2 subscriptions to a publication,
but they were from the same subscriber server and writing to the same subscriber database, just one was bad and one was running fine.
I could not find a way to delete the bad one, so I decided to drop both, and then recreate the subscription.
on the publisher server
use [SupplierDB]
go
exec sp_dropsubscription @publication = N'SupplierDB',
@subscriber = N'REPLON1',
@destination_db = N'SupplierDB',
@article = N'all'
GO
That dropped the good subscription.
Because I could not find a way to drop or even see the bad subscription I decided to drop the publication, but when I try it I get the following error message:
-- Dropping the transactional publication
use [SupplierDB]
exec sp_droppublication @publication = N'SupplierDB'
GO
Msg 14005, Level 16, State 1, Procedure sp_MSdrop_publication, Line 75
Cannot drop the publication because at least one subscription exists
for this publication. Drop all subscriptions to the publication before
attempting to drop the publication. If the problem persists,
replication metadata might be incorrect; consult Books Online for
troubleshooting information.
How can I find and drop this subscription?
This is a problem that I originally had on another question:
NoSync subscriptions will need to be dropped and recreated
Best Answer
You need to look at the actual catalog/metadata table(s) and clean them up. The GUI and stored procedures often don't cleanup the underlying tables properly. So, after you have run the sp_dropsubscription SP run a select from MSsubscriptions and MSsubscriptions to see if there is any data referring to your unwanted subscriptions. See: msdn.microsoft.com/en-us/library/ms179855(v=sql.105).aspx. Delete any row(s) representing subscriptions that shouldn't be there and it should resolve the problem. You may need to set sp_configure 'Allow Updates' = 1 to make changes to the table(s).