Scenario:

You have a Publisher that no longer has a subscriber (Youve either used the GUI and removed the subscriber or you ran a sp_subscription_cleanup)

When you run :

-- SP Ran on Publisher
EXEC sp_dropsubscription
@publication= 'reptest', @subscriber = 'all' , @article = 'all'

It completes successfully. but you still see the Publication in the Replication Local Publications.

If you try and delete the publication via the GUI you get the error:

SubscriptionExistsOnItError

Resolution:

The distribution database is still holding details of the publication:

Run:

USE [distribution]
GO
-- Look for any references to the pub you want to remove
select * from mssubscriptions with (nolock )

SubscriptionExistsOnItError2

Now delete those rows.

USE [distribution]
GO
-- Run delete for rows relating to pub
delete from mssubscriptions
where publisher_db = 'repTest'

You can now remove the Publication successfully.

Advertisements