Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_changedynamicsnapshot_job

  No additional text.


Syntax

-- 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


 
Last revision 2008RTM
See also

  sp_MSchangemergedynamicsnapshotjob (Procedure)
sp_MSdrop_rladmin (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash