"Chris" <Chris@discussions.microsoft.com> wrote in message
news:8602FFB6-F5C7-4D31-8D01-1E5D3319AB27@microsoft.com...
> SQL2000 trans repl - seperate dedicated distributor.
>
> Posted question prior about a failed subscriber. Server crashed,
> completely
> lost. We were able to drop subscriptions at pub, but distribution agent
> was
> not deleted. Just stuck in a red-x - "...subscription has expired "... err
> msg.
>
> Per Hillary's response - I deleted rows from MSsubscription_agents (at
> recovered subscr) & MSdistribution_agents (at distr), and manually deleted
> job. All ok so far.
>
> Now - I added subscription at publisher to same server using
> sp_addsubscr -
> it appears to have executed successfully, however distribution agent is
> NOT
> created.
>
> Please help - thanks.
>
>
>
> Response I got from Hilary Cotter:
>
> Look in the following locations for references to these jobs and delete
> them
> there.
>
> MSsubscription_agents on the subscriber. Note the publisher name,
> publisher_db, and publication. Evaluate and delete them.
>
> On the distributor, look in MSdistribution_agents. Note the name of the
> agent.
>
> You may have to manually delete the job with this name in the
> management/jobs folder.
>
>

"Hilary Cotter" wrote:

> Can you do this?
>
> use MyPublicationDatabase
> go
>
> sp_helppublication 'MyPublication'
>
> sp_helpsubscription 'MyPublication'
>
> --note value for distribution_job_id
>
> declare @distribution_job_id uniqueidentifier
> set @distribution_job_id =0xDC451942396FB24EB7F5A4C867E1E152
>
> select * from msdb.dbo.sysjobs where job_id=@distribution_job_id
>
> This is your job - does it exist? I have seen cases where they do not show
> up in the agents folder.
>
> --
> RelevantNoise.com - dedicated to mining blogs for business intelligence.
>
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Chris" <Chris@discussions.microsoft.com> wrote in message
> news:8602FFB6-F5C7-4D31-8D01-1E5D3319AB27@microsoft.com...
> > SQL2000 trans repl - seperate dedicated distributor.
> >
> > Posted question prior about a failed subscriber. Server crashed,
> > completely
> > lost. We were able to drop subscriptions at pub, but distribution agent
> > was
> > not deleted. Just stuck in a red-x - "...subscription has expired "... err
> > msg.
> >
> > Per Hillary's response - I deleted rows from MSsubscription_agents (at
> > recovered subscr) & MSdistribution_agents (at distr), and manually deleted
> > job. All ok so far.
> >
> > Now - I added subscription at publisher to same server using
> > sp_addsubscr -
> > it appears to have executed successfully, however distribution agent is
> > NOT
> > created.
> >
> > Please help - thanks.
> >
> >
> >
> > Response I got from Hilary Cotter:
> >
> > Look in the following locations for references to these jobs and delete
> > them
> > there.
> >
> > MSsubscription_agents on the subscriber. Note the publisher name,
> > publisher_db, and publication. Evaluate and delete them.
> >
> > On the distributor, look in MSdistribution_agents. Note the name of the
> > agent.
> >
> > You may have to manually delete the job with this name in the
> > management/jobs folder.
> >
> >
>
>