Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Invalid object name 'syssubscriptions'- when running sp_removedbreplication 'dbname' Expand / Collapse
Author
Message
Posted Tuesday, August 5, 2008 9:46 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, February 10, 2011 8:32 AM
Points: 23, Visits: 118
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?
Post #546850
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse