create procedure sys.sp_MSaddmergepub_snapshot (
@publication sysname,
@freqtype int = 4 , /* 4== Daily */
@freqinterval int = 1, /* Every day */
@freqsubtype int = 4, /* Sub interval = Minute */
@freqsubinterval int = 5, /* Every five minutes */
@freqrelativeinterval int = 1,
@freqrecurrencefactor int = 0,
@activestartdate int = 0, /* 12:00 am - 11:59 pm */
@activeenddate int = 99991231 , /* No start date */
@activestarttimeofday int = 0,
@activeendtimeofday int = 235959, /* No end time */
@newtaskid int = 0 OUTPUT,
@snapshot_job_name nvarchar(100) = null,
@publisher_security_mode int = null,
@publisher_login sysname = null,
@publisher_password sysname = null,
@job_login nvarchar(257) = null,
@job_password sysname = null
) AS
SET NOCOUNT ON
/*
** Declarations.
*/
declare @retcode int
declare @distributor sysname
declare @dist_rpcname sysname
declare @distribdb sysname
declare @distproc nvarchar(300)
declare @database sysname
declare @newid int
declare @fFoundPublication int
declare @task_args nvarchar(4000)
declare @pubid uniqueidentifier
declare @snapshot_jobid binary(16)
declare @job_existing bit
,@publishingservername sysname
/*
** Initializations
*/
select @fFoundPublication = 0
,@publishingservername = publishingservername()
if (@snapshot_job_name is null) or (@snapshot_job_name = N'')
begin
select @job_existing = 0
end
else
begin
select @job_existing = 1
end
EXEC @retcode = sys.sp_helpmergepublication @publication, @fFoundPublication output, @pubid output
if @@ERROR <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END
/* If the publication does not exist return error */
if @fFoundPublication = 0
BEGIN
RAISERROR (21040, 11, -1, @publication)
RETURN (1)
END
/*
** Get distributor information
*/
EXEC @retcode = sys.sp_helpdistributor @distributor = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT,
@rpcsrvname = @dist_rpcname OUTPUT
if @@error <> 0 OR @retcode <> 0 or @distributor IS NULL OR @distribdb IS NULL
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
select @database = DB_NAME()
/*
** Make sure the publication does not already have a task.
*/
if EXISTS (select * FROM dbo.sysmergepublications WHERE pubid = @pubid and snapshot_jobid IS NOT NULL)
BEGIN
declare @valid_agent_exists bit
select @valid_agent_exists = 0
-- check if the agent really exists on the distributor
SELECT @distproc = RTRIM(@dist_rpcname) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MScheck_snapshot_agent'
EXECUTE @retcode = @distproc
@publisher = @publishingservername,
@publisher_db = @database,
@publication = @publication,
@valid_agent_exists = @valid_agent_exists output
if @valid_agent_exists = 0
begin
update dbo.sysmergepublications set snapshot_jobid = NULL where pubid = @pubid
if @@error<>0 return 1
end
else
begin
RAISERROR (14101, 11, -1, @publication)
RETURN(1)
end
END
select @task_args = '-Publisher ' + QUOTENAME(publishingservername())
select @task_args = @task_args + ' -PublisherDB ' + QUOTENAME(@database)
select @task_args = @task_args + ' -Distributor ' + QUOTENAME(@distributor)
select @task_args = @task_args + ' -Publication ' + QUOTENAME(@publication)
select @task_args = @task_args + ' -ReplicationType 2'
/*
** Create task on distributor
*/
SELECT @distproc = RTRIM(@dist_rpcname) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MSadd_snapshot_agent'
EXECUTE @retcode = @distproc
@name = @snapshot_job_name,
@publisher = @publishingservername,
@publisher_db = @database,
@publication = @publication,
@publication_type = 2, -- Merge type
@local_job = 1,
@freqtype = @freqtype,
@freqinterval = @freqinterval,
@freqsubtype = @freqsubtype,
@freqsubinterval = @freqsubinterval,
@freqrelativeinterval = @freqrelativeinterval,
@freqrecurrencefactor = @freqrecurrencefactor,
@activestartdate = @activestartdate,
@activeenddate =@activeenddate ,
@activestarttimeofday = @activestarttimeofday,
@activeendtimeofday = @activeendtimeofday,
@command = @task_args,
@snapshot_jobid = @snapshot_jobid OUTPUT,
@job_existing = @job_existing,
@publisher_security_mode = @publisher_security_mode,
@publisher_login = @publisher_login,
@publisher_password = @publisher_password,
@job_login = @job_login,
@job_password = @job_password,
@internal = N'YUKON'
if @@ERROR <> 0 or @retcode <> 0
RETURN(1)
SELECT @newtaskid = 1
UPDATE dbo.sysmergepublications set snapshot_jobid = @snapshot_jobid WHERE pubid = @pubid
if @@ERROR <> 0
RETURN(1)
return (0)