Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSchangemergedynamicsnapshotjob

  No additional text.


Syntax
-- This proc calls the distribution proc to change the dynamic snaphsot job
-- it should NOT be a public proc
create procedure sys.sp_MSchangemergedynamicsnapshotjob (
    @publication sysname,
    @dynamic_snapshot_jobname sysname = '%',
    @dynamic_snapshot_jobid uniqueidentifier,
    @frequency_type				 int,
    @frequency_interval			 int,
    @frequency_subday			 int,
    @frequency_subday_interval	 int,
    @frequency_relative_interval int,
    @frequency_recurrence_factor int,
    @active_start_date			 int,
    @active_end_date			 int,
    @active_start_time_of_day	 int,
    @active_end_time_of_day		 int,
    @job_login                               nvarchar(257),
    @job_password                        sysname
)
as
begin
    set nocount on

    -- This should not be a public proc
    declare @retcode int
    declare @pubid uniqueidentifier

    declare @distributor sysname
    declare @distribdb sysname
    declare @rpcsrvname sysname
    declare @distproc nvarchar(4000)
    declare @publisher sysname
    declare @publisher_db sysname
    declare @agent_id int
    declare @suser_sname sysname
    declare @host_name sysname

    select @retcode = 0
    select @pubid = null
    select @publisher = upper(publishingservername())
    select @publisher_db = db_name()


    if object_id('sysmergepublications') is NULL
    begin
        raiserror (20054, 16, -1)
        return (1)
    end

    -- Make sure that caller is a member of 'sysadmins' or 'db_owner'
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@error <> 0 or @retcode <> 0
        return (1)

    select @pubid = pubid
      from dbo.sysmergepublications
     where name = @publication
       and upper(publisher) = upper(publishingservername())
       and publisher_db = db_name()

    if @pubid is null
    begin
        raiserror(20026, 16, -1, @publication)
        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
    begin	
        select @dynamic_snapshot_jobid = job_id,
               @suser_sname = dynamic_filter_login,
               @host_name = dynamic_filter_hostname
          from dbo.MSdynamicsnapshotjobs
         where pubid = @pubid
           and name = @dynamic_snapshot_jobname
    end
    else
    begin
        select @suser_sname = dynamic_filter_login,
               @host_name = dynamic_filter_hostname
          from dbo.MSdynamicsnapshotjobs
         where pubid = @pubid
           and job_id = @dynamic_snapshot_jobid
    end

    -- check if suser_sname and host_name are null
    if @suser_sname is NULL and @host_name is NULL
    begin
        raiserror(21720, 16, -1)
        return (1)
    end

    if @job_login IS NOT NULL
    begin
        if sys.fn_replisvalidwindowsloginformat(@job_login) != 1
        begin
            -- '@job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_changedynamicsnapshot_job'.
            raiserror(21797, 16, -1, '@job_login', 'sp_changedynamicsnapshot_job')
            return(1)
        end
    end
		
    -- Get distributor information for RPC
    exec @retcode = sys.sp_MSrepl_getdistributorinfo @distributor = @distributor output,
                                           @distribdb = @distribdb output,
                                           @rpcsrvname = @rpcsrvname output
    if @@error <> 0 or @retcode <> 0
        return (1)

    select @distproc = quotename(rtrim(@rpcsrvname)) + N'.' + quotename(@distribdb) + N'.' + N'dbo.sp_MSchangedynamicsnapshotjobatdistributor'

    begin transaction
save transaction changemergedynamicsnapshotjob

    exec @retcode = @distproc
        @publisher = @publisher,
        @publisher_db = @publisher_db,
        @publication = @publication,
        @dynamic_filter_login = @suser_sname,
        @dynamic_filter_hostname = @host_name,
        @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 @retcode <> 0 or @@error <> 0
    begin
        raiserror(20701, 16, -1)
        goto Failure
    end

    commit transaction
    return 0

Failure:
    rollback transaction changemergedynamicsnapshotjob
    commit transaction
    return 1
end

 
Last revision 2008RTM
See also

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