-- Name: sp_changedynamicsnapshot_job
-- Description: This procedure changes the scheduled dynamic snapshot job
-- Parameters: @publication sysname (mandatory)
-- @dynamic_snapshot_jobname (optional, default '%')
-- @dynamic_snapshot_jobid uniqueidentifier (optional, default
-- null) When @dynamic_snapshot_jobid is null and
-- @dynamic_snapshot_jobname is '%', all dynamic snapshot
-- jobs for the specified publication will be changed.
-- Notes: 1) At most one of @dynamic_snapshot_jobid and
-- @dynamic_snapshot_jobname can be specified with a non-default
-- value.
--
-- Returns: 0 - succeeded
-- 1 - failed
-- Security: Only members of the 'sysadmin' server role and the 'db_owner'
-- database role can execute this procedure successfully even though execute
-- permission of this procedure is granted to public.
-- Requires Certificate signature for catalog access
create procedure sys.sp_changedynamicsnapshot_job (
@publication sysname,
@dynamic_snapshot_jobname sysname = N'%',
@dynamic_snapshot_jobid uniqueidentifier = null,
-- Scheduling information
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
-- Job Login information
@job_login nvarchar(257) = NULL,
@job_password sysname = NULL
)
as
set nocount on
declare @retcode int
declare @pubid uniqueidentifier
declare @dynamic_filters bit
declare @command_line nvarchar(4000)
declare @distribdb sysname
declare @rpcsrvname sysname
declare @distributor sysname
declare @distproc nvarchar(4000)
declare @id int
declare @partition_id int
declare @snapshot_ready int
declare @agent_id int
declare @dynamic_snapshot_jobid_from_cursor uniqueidentifier
-- Initializations
select @retcode = 0
select @pubid = null
select @dynamic_filters = 0
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
return (1)
if object_id('sysmergepublications') is NULL
begin
raiserror (20054, 16, -1)
return (1)
end
-- Verify that the given publication exists and get the pubid at the
-- same time
select @pubid = pubid,
@dynamic_filters = dynamic_filters,
@snapshot_ready = snapshot_ready
from dbo.sysmergepublications
where upper(publisher) = upper(publishingservername())
and publisher_db = db_name()
and name = @publication
if @pubid is null
begin
raiserror(20026, 16, -1, @publication)
return (1)
end
if @snapshot_ready <> 1
begin
raiserror (21075, 11, -1, @publication)
return (1)
end
-- The given publication must be enabled for dynamic filtering
if @dynamic_filters <> 1
begin
raiserror(20674, 16, -1)
return (1)
end
-- At most one of @dynamic_snapshot_jobid and @dynamic_snapshot_jobname
-- can be specified with a non-default value
if @dynamic_snapshot_jobid is not null and
@dynamic_snapshot_jobname <> N'%'
begin
raiserror(21329, 16, -1)
return (1)
end
if @dynamic_snapshot_jobid is null and @dynamic_snapshot_jobname = N'%'
begin
declare hJobsCursor cursor local fast_forward for
select job_id
from MSdynamicsnapshotjobs
where pubid = @pubid
if @@error <> 0
return 1
open hJobsCursor
if @@error <> 0
return 1
fetch hJobsCursor into @dynamic_snapshot_jobid_from_cursor
begin transaction
save transaction sp_MSchangedynamicsnapshotjobC
while (@@fetch_status <> -1)
begin
if @dynamic_snapshot_jobid_from_cursor is not null
begin
exec @retcode = sys.sp_MSchangemergedynamicsnapshotjob
@publication = @publication,
@dynamic_snapshot_jobid = @dynamic_snapshot_jobid_from_cursor,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@job_login = @job_login,
@job_password = @job_password
if @@error <> 0 or @retcode <> 0
goto CursorFailure
end
fetch hJobsCursor into @dynamic_snapshot_jobid_from_cursor
end
commit transaction
close hJobsCursor
deallocate hJobsCursor
return 0
CursorFailure:
rollback transaction sp_MSchangedynamicsnapshotjobC
commit transaction
close hJobsCursor
deallocate hJobsCursor
return 1
end
else
begin
exec @retcode = sys.sp_MSchangemergedynamicsnapshotjob
@publication = @publication,
@dynamic_snapshot_jobid = @dynamic_snapshot_jobid,
@dynamic_snapshot_jobname = @dynamic_snapshot_jobname,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@job_login = @job_login,
@job_password = @job_password
if @@error <> 0 or @retcode <> 0
goto Failure
end
return 0
Failure:
raiserror(20702, 16, -1)
return 1