Invalid object name 'syssubscriptions'- when running sp_removedbreplication 'dbname'

• mike-306849

  SSChasing Mays

  Points: 607

  More actions

  August 5, 2008 at 9:47 am

  #103552

  First off, I'll say that I'm not sure who did what to this server before I got called about this - all I know is they were trying to remove transactional replication. I'm trying to run sp_removedbreplication on one of our databases and I keep getting this error back.

  .Net SqlClient Data Provider: Msg 208, Level 16, State 1, Procedure sp_MSpublishdb, Line 110

  Invalid object name 'syssubscriptions'.

  I've tracked this down - I think - to this area of sp_MSpublishdb

  EXEC @retcode = dbo.sp_dropsubscription @publication = 'all',

  @article = 'all', @subscriber = 'all',

  @ignore_distributor = @ignore_distributor

  so I tried running sp_dropsubscription myself with @ignore_distributor = 1

  And I think that is failing here

  DECLARE hCdrop_subscription1 CURSOR LOCAL FAST_FORWARD FOR

  SELECT DISTINCT srvname

  FROM master..sysservers a, syssubscriptions b

  WHERE srvstatus & @subscriber_bit <> 0

  AND a.srvid = b.srvid

  Now when I first started looking at this, syssubscriptions was gone. I had a an old backup of the database though with that table in there, no rows but the structure was right. So I created the table and am still getting this message that Syssubscriptions is missing. I can run this

  SELECT DISTINCT srvname

  FROM master..sysservers a, syssubscriptions b

  WHERE srvstatus & @subscriber_bit <> 0

  AND a.srvid = b.srvid

  just fine. I get no rows back, but it runs no problem.

  Has anyone run into this?