sql server replication: Running Win2k & SQL 2k
How do I remove orphaned subscriptions? I have several subscriptions that
appear in the distribution agent of the publisher that do not exist on the
subscriber.

Is there an SP to remove these?? Is there a way I can do it manually?

TIA,

It seems that ActiveX distribution and merge agents, as well as the merge
agents created with SQL CE subscribers are prone to not being cleaned up
correctly. Or perhpas I'm doing something incorrect.

First off make sure the agents aren't still running at the publisher. Use
sp_MSenum_replication_agents to determine the names of the active agents.
You have to pass the agent type
1=snapshot
2=logreader
3=distribution
4=merge
5=misc
9=queue reader

Then you have to determine the agent status, to do this change to the
distribution database and issue the following
sp_MSenum_distribution 'AgentName'

0=initializing
1=starting
2=stopped
3=running
4=idle
5=retry
6=failed

Once you have figured out the agent status you can begin to delete them.

In your subscription database issue an sp_helpsubscription or
sp_helpmergesubscription

Delete the subscription using sp_dropsubscription or
spdropmergesubscription. Use the values obtained from the above two
commands. Issue this in the publication database.

If this doesn't work you have to manually edit the system tables.




--
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html


[quoted text, click to view]