Article ID: 183081
Article Last Modified on 10/3/2003
---- begin ----
/* Remove all orphaned jobs (no entry in MSsubscriber_jobs) from
MSjobs */
delete MSjobs from MSjobs j where
j.publisher_id = @publisher_id and
j.publisher_db = @publisher_db and
j.job_id not in (select job_id from MSsubscriber_jobs sj (index =
ncMSsubscriber_jobs) where
sj.publisher_id = j.publisher_id and
sj.publisher_db = j.publisher_db and
sj.job_id = j.job_id) and
j.job_id <> isnull ( /* added the isnull function */
(select max(job_id) from MSjobs j (index = ucMSjobs) where
j.publisher_id = @publisher_id and
j.publisher_db = @publisher_db and
j.xactid_page <> 0)
,0) /* if NULL, return zero instead of NULL */
and
j.job_id <> (select max(job_id) from MSjobs j (index = ucMSjobs)
where
j.publisher_id = @publisher_id and
j.publisher_db = @publisher_db)
if @@error <> 0
begin
close hC2
DEALLOCATE hC2
rollback transaction sp_replcleanup
return (1)
end
---- end ----
Additional query words: repl partial removal comparison
Keywords: kbbug kbpending KB183081