Issue: My server “SVR1” runs a SQL Server with a transactional publication. On the second server “SVR2” I had a subscription.
Now the SVR2 does not exist anymore and I want to delete the replication on SVR1. (in the SQL Management Studio, the subscription is still visible)
But if I want to delete the SVR2-subscription on the SVR1, I get the error message 20032 “Could not delete the subscription at Subscriber ‘SVR2’ in database ‘ICSDB’.”How can I delete this subscription (and afterwards also the publication)
I’m using SQL Server 2008 R2 on Windows Server 2008R2
Right click on the publication; go to properties then to subscriptions. Click Delete. This is the EM way. For the QA way, generate the delete statement from EM.
For future use, I was having the same problems described above – but I also had database snapshots on the database that I was trying to remove replication from but that was not mentioned anywhere in the error messages.
Once I removed my database snapshots, the following script cleans things up well:
Example came from here: http://msdn.microsoft.com/en-us/library/ms188411.aspx
DECLARE @distributionDB AS sysname;
DECLARE @publisher AS sysname;
DECLARE @publicationDB as sysname;
SET @distributionDB = N’distribution’;
SET @publisher = N’S-ESN-SQL1\SQL1′;
SET @publicationDB = N’AdventureWorks2008R2′;
— Disable the publication database.
EXEC sp_removedbreplication @publicationDB;
— Remove the registration of the local Publisher at the Distributor.
EXEC sp_dropdistpublisher @publisher;
— Delete the distribution database.
EXEC sp_dropdistributiondb @distributionDB;
— Remove the local server as a Distributor.