Unable to cleanup a replication publication.

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  November 6, 2016 at 11:21 am

  #315225

  Hello Experts,

  I have a publication but the subscription is removed already from the other server.

  I do not need the replication setup. So when I try to clean up (delete) the replication, I get the below error...

  TITLE: Microsoft SQL Server Management Studio

  ------------------------------

  Could not delete publication 'ABCD'.

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.0.2000.8&EvtSrc=Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Replication.ReplicationMenuItem&EvtID=CantDeletePublication&LinkId=20476

  ------------------------------

  ADDITIONAL INFORMATION:

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  ------------------------------

  'NHASQL182' is not defined as a Subscriber for 'server1'.

  Could not update the distribution database subscription table. The subscription status could not be changed.

  Changed database context to 'XYZ'. (Microsoft SQL Server, Error: 20032)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5000&EvtSrc=MSSQLServer&EvtID=20032&LinkId=20476

  ------------------------------

  BUTTONS:

  OK

  ------------------------------

  Any suggestion how to clean up the unused publication?

  Thanks.

• SQL_Hunt

  SSC-Dedicated

  Points: 33518

  More actions

  November 6, 2016 at 11:27 am

  #1910793

  I used the below script to drop subscription, but got the below error.

  Any advise please?

  -- Dropping the transactional subscriptions

  use [db]

  exec sp_dropsubscription @publication = N'abcd', @subscriber = N'servername', @destination_db = N'db_name', @article = N'all'

  GO

  -----------Error ----------

  Msg 20032, Level 16, State 1, Procedure sp_MSdrop_subscription, Line 85

  'servername' is not defined as a Subscriber for 'publisher'.

  Msg 14070, Level 16, State 1, Procedure sp_MSrepl_changesubstatus, Line 1273

  Could not update the distribution database subscription table. The subscription status could not be changed.

  Thanks.

• doug.brown

  SSCertifiable

  Points: 5608

  More actions

  November 7, 2016 at 1:16 pm

  #1911021

  Here are a couple of threads with suggested solutions that I found by googling the error:

  http://www.sqlservercentral.com/Forums/Topic1505442-291-1.aspx

  https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b64bcde9-4769-45df-a8c7-19606c9c33ca/unable-to-drop-subscription?forum=sqlreplication

  I'm an advocate for pasting errors into your favorite search engine and seeing what comes up. It has streamlined a lot of investigations for me.

• Bart Lewandowski

  Ten Centuries

  Points: 1099

  More actions

  November 8, 2016 at 5:40 am

  #1911156

  Seems like problem with updating distributor. Is it accessible?

  Try add parameter

  @ignore_distributor = 1

  BartL
  Replication Blog