create procedure sys.sp_MSdistributionagentjobcontrol
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,
@subscriber_db sysname,
@action tinyint
)
as
begin
-- For dependent agents, @publication must be 'ALL' when this
-- procedure is called through the replication rpc link.
declare @job_id uniqueidentifier
declare @publisher_id int
declare @subscriber_id int
declare @retcode int
set @retcode = 0
set @publisher_id = null
select @publisher_id = server_id
from sys.servers
where upper(name) = upper(@publisher) collate database_default
if @publisher_id is null
begin
raiserror (21618, 16, -1, @publisher)
return 1
end
set @subscriber_id = null
select @subscriber_id = server_id
from sys.servers
where upper(name) = upper(@subscriber) collate database_default
if @subscriber_id is null
begin
raiserror (14048, 16, -1, @subscriber)
return 1
end
set @job_id = null
select @job_id = msda.job_id
from dbo.MSdistribution_agents as msda
inner join msdb.dbo.sysjobs_view as sjv
on msda.job_id = sjv.job_id
inner join msdb.dbo.sysjobsteps as sjs
on sjv.job_id = sjs.job_id
where msda.publisher_id = @publisher_id
and msda.publisher_db = @publisher_db
and msda.publication = @publication
and msda.subscriber_id = @subscriber_id
and msda.subscriber_db = @subscriber_db
and sjv.category_id = 10
and sjs.subsystem = N'Distribution'
if @job_id is null
begin
-- "Could not find a distribution agent job for the specified transactional\snapshot push subscription."
raiserror(21846, 16, -1)
return 1
end
exec @retcode = sys.sp_MSreplicationagentjobcontrol
@job_id = @job_id,
@action = @action
return @retcode
end