drop subscription from subscriber

• Emanuel Villa

  SSC Enthusiast

  Points: 141

  More actions

  July 7, 2005 at 2:51 pm

  #167447

  Hi Everyone...

  I create a Publication in one server to another, and it was working very cool... but... the publisher server crashes and once we recover it, the publications where lost.

  Easy... I have done them again... but some of this publications fail because said they are already in use; and in the suscriber they are too. But in the publisher not.

  what can I do for resolve this?

  I need to drop subscription in suscriber because if I drop subscription on publisher said that not exist... How I resolve this? Can Anybody help me?

  Thanks in advance

• Steve-3_5_7_9

  SSChampion

  Points: 10832

  More actions

  July 8, 2005 at 5:15 am

  #572240

  Here you go:

  The fix:

  1. Drop the SUBSCRIBER (if exists on new publication server)
  2. On the SUBSCRIBER run (see BOL)

  a.      sp_removedbreplication [ @dbname = ] 'dbname'

  b.      this will remove any residual replication objects; so the subscriber starts with a clean database.

  c.       sp_removedbreplication is useful when restoring a replicated database that has no replication objects needing to be restored.

  d.      To check that this has worked you can run this script in the database being PUBLISHED or SUBSCRIBER.  No rows should be returned

  Select 'drop table ' + name from sysobjects where name like '%conflict%' and type='u'

  Select 'drop trigger ' + name from sysobjects where type = 'tr' and status < 0 and category = 2

  Select 'drop proc ' + name from sysobjects where name like 'sp_%' and type ='p' and category = 2

  Select 'drop view ' + name from sysobjects where name like 'tsvw%' and type ='v' and category = 2

  Select 'drop view ' + name from sysobjects where name like 'ctsv%' and type ='v' and category = 2

  Select 'drop table ' + name from sysobjects where name like 'MSMerge%' and type='u'

        

          e. If rows are returned, then you can just run the OUTPUT generated from this script

  You may want to clean up the publisher as well since it crashed.  I had to do this as well when my publisher server crashed.

  Steps Follow:

  1.      On the new PUBLISHER server run STEP 2 ABOVE, as well.  Drop the PUBLICATION if it existed

  2.    Also, we want to check for leftover components at the distributor by running:

   

   

  use distribution

  select * from msmerge_agents where publication =  ‘<pubname>’

  select * from msmerge_subscriptions where publisher_db ='<yourpublicationdb>'

  select * from mspublications where publication = '<yourpublication>'

  3.  At this point no rows should return.

  4.  Now you can recreate the distribution/publication/subscribers