Invalid object name 'syssubscriptions'- when running sp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Replication » Invalid object name 'syssubscriptions'- when...

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

Rate Topic

Display Mode

Topic Options

Author

Message

mike-306849

Posted Tuesday, August 05, 2008 9:46 AM

Grasshopper

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 »

Permissions