Remove Replication Subscription

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Remove Replication Subscription

Author

Topic

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-01-29 : 16:02:58

I have a remote SQL server that show's a subscription running, that subscription no longer realy exists as the publication got removed off the primary SQL server.

how do I get this subcription off the Remote Server so I can Rebuild the Publication on the primary. It will not lot me delete it via EM.

I tried

exec sp_dropsubscription @publication = N'CCCEstimateing', @article = N'all', @subscriber = N'Servertwo', @destination_db = N'cccestimateing'
GO

Got the error

Server: Msg 14010, Level 16, State 1, Procedure sp_dropsubscription, Line 136
The remote server is not defined as a subscription server.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-29 : 16:15:22

You ran this on the publication side right? sp_dropsubscription does not get run on the subscription database, but rather on the publication database.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-01-29 : 16:17:06

Ok New error

Server: Msg 208, Level 16, State 1, Procedure sp_dropsubscription, Line 145
Invalid object name 'syssubscriptions'.

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-29 : 16:28:50

Does that table exist in the publication database? It should. The fact that you are getting the error leads me to believe that someone has improperly deleted replication.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-01-29 : 16:32:24

quote:
Originally posted by tduggan

The fact that you are getting the error leads me to believe that someone has improperly deleted replication.

Tara

Yes they did. (no more temps from that company)

So how do I get the orphaned Subscription off the backup server?

Jim
Users <> Logic

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-01-29 : 16:34:00

You are going to have to manually delete data from the system tables. You'll have to go through the replication stored procedures to see which tables to delete from.

Tara

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2004-01-30 : 09:50:29

Crap

I had to deleat and restore the entire DB.

What a pain.

Thanks For the help Tara!!!!!!!!!

MMMMmmmmmmmmmmmmmmm maybe one of our Great brains could come up with a easy way.

Jim
Users <> Logic

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources