create procedure sys.sp_get_job_status_mergepullsubscription_agent
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@runstatus int output
)
as
begin
set nocount on
declare @retcode int
declare @job_id uniqueidentifier
declare @subscription_type int
declare @pubid uniqueidentifier
declare @subscriber sysname
declare @subscriber_db sysname
set @retcode = 0
set @job_id = null
set @subscription_type = null
set @subscriber = @@servername
set @subscriber_db = db_name()
select @runstatus = NULL
if object_id('dbo.sysmergepublications') is null
begin
raiserror (20017, 16, -1)
return 1
end
select @pubid = pubid
from dbo.sysmergepublications
where name = @publication
and upper(publisher) = upper(@publisher)
and publisher_db = @publisher_db
if @pubid is null
begin
raiserror (20026, 16, -1, @publication)
return 1
end
select @subscription_type = subscription_type
from dbo.sysmergesubscriptions
where upper(subscriber_server) = upper(@subscriber)
and pubid <> subid
and pubid = @pubid
and db_name = @subscriber_db
if @subscription_type is null or @subscription_type = 0
begin
raiserror (20017, 16, -1)
return 1
end
if object_id('dbo.MSsubscription_properties') is not null
begin
select @job_id = sjs.job_id
from MSsubscription_properties sp
inner join msdb.dbo.sysjobsteps sjs
on sp.job_step_uid = sjs.step_uid
inner join msdb.dbo.sysjobs_view sjv
on sjv.job_id = sjs.job_id
where upper(publisher) = upper(@publisher)
and publisher_db = @publisher_db
and publication = @publication
and sjv.master_server = 0
and sjv.category_id = 14
and sjs.subsystem = N'Merge'
and (sjs.database_name is null or sjs.database_name = db_name())
end
if @job_id is null
begin
-- "The specified pull subscription is not configured with a synchronization agent job."
raiserror(21848, 16, -1)
return 1
end
-- this table we hold only single row
declare @job_info table(
runstatus int,
message nvarchar(1024),
date int,
time int,
datetime nvarchar(50)
)
insert into @job_info exec sp_MSenum_replication_job @job_id=@job_id
select top 1 @runstatus=runstatus from @job_info
end