Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdropmergedynamicsnapshotjob

  No additional text.


Syntax

-- Name: sp_MSdropmergedynamicsnapshotjob

-- Description: This procedure drops a scheduled dynamic snapshot job for
--				a publication and the associated meta-data in
--				MSdynamicsnapshotjobs. This procedure will also remove all
--				files in the associated dynamic snapshot location.

-- 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 dropped.
--			   @ignore_distributor bit (optional, default 0)

-- 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_MSdropmergedynamicsnapshotjob (
    @publication sysname,
    @dynamic_snapshot_jobname sysname = '%',
    @dynamic_snapshot_jobid uniqueidentifier = null,
    @ignore_distributor bit = 0
)
as
begin
    set nocount on

    declare @retcode int
    declare @pubid uniqueidentifier
    declare @dynamic_snapshot_location nvarchar(255)
    declare @guidstr nvarchar(40)
    declare @dynamic_snapshot_jobid_from_cursor uniqueidentifier
    declare @dir nvarchar(4000)

    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 @backward_comp_level int
    declare @dynamic_filter_login sysname
    declare @dynamic_filter_hostname sysname
    declare @partition_id int

    select @retcode = 0
    select @pubid = null
    select @dynamic_snapshot_location = null
    select @publisher = 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,
           @backward_comp_level = backward_comp_level
      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 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_MSdropdynamicsnapshotjobC

        while (@@fetch_status <> -1)
        begin
            if @dynamic_snapshot_jobid_from_cursor is not null
            begin
                exec @retcode = sys.sp_MSdropmergedynamicsnapshotjob
                                    @publication = @publication,
                                    @dynamic_snapshot_jobid = @dynamic_snapshot_jobid_from_cursor,
                                    @ignore_distributor = @ignore_distributor
                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_MSdropdynamicsnapshotjobC
        commit transaction
        close hJobsCursor
        deallocate hJobsCursor
        return 1
    end

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

    if @dynamic_snapshot_jobid is null
    begin	
        select @dynamic_snapshot_location = dynamic_snapshot_location,
               @dynamic_snapshot_jobid = job_id,
               @dynamic_filter_login = dynamic_filter_login,
               @dynamic_filter_hostname = dynamic_filter_hostname,
               @partition_id = @partition_id,
               @agent_id = agent_id
          from dbo.MSdynamicsnapshotjobs
         where pubid = @pubid
           and name = @dynamic_snapshot_jobname
    end
    else
    begin
        select @dynamic_snapshot_location = dynamic_snapshot_location,
               @dynamic_filter_login = dynamic_filter_login,
               @dynamic_filter_hostname = dynamic_filter_hostname,
               @partition_id = @partition_id,
               @agent_id = agent_id
          from dbo.MSdynamicsnapshotjobs
         where pubid = @pubid
           and job_id = @dynamic_snapshot_jobid
    end

    if @dynamic_snapshot_location is null
    begin
        if @dynamic_snapshot_jobid is null
        begin
            raiserror(21326, 16, -1, N'@dynamic_snapshot_jobname', @dynamic_snapshot_jobname)
        end
        else
        begin
            select @guidstr = coalesce(convert(nvarchar(40), @dynamic_snapshot_jobid) collate database_default, '(NULL)' collate database_default)
            raiserror(21326, 16, -1, N'@dynamic_snapshot_jobid', @guidstr)
        end
        return (1)
    end

    -- Get distributor info for RPC
    if @ignore_distributor = 0
    begin
        exec @retcode = sys.sp_helpdistributor @distributor = @distributor output,
                                               @distribdb = @distribdb output,
                                               @rpcsrvname = @rpcsrvname output

        select @rpcsrvname = rtrim(@rpcsrvname)


        select @distproc = QUOTENAME(RTRIM(@rpcsrvname)) + N'.' + QUOTENAME(@distribdb) + N'.dbo.sp_MScleanupdynamicsnapshotfolder'
        exec @retcode = @distproc
               @publisher = @publisher,
               @publisher_db = @publisher_db,
               @publication = @publication,
               @dynamic_filter_login = @dynamic_filter_login,
               @dynamic_filter_hostname = @dynamic_filter_hostname,
               @dynamic_snapshot_location = @dynamic_snapshot_location,
               @partition_id = @partition_id

        if @backward_comp_level < 90 and isnull(is_srvrolemember('sysadmin'),0) = 1
        begin
            select @distproc = QUOTENAME(RTRIM(@rpcsrvname)) + N'.' + QUOTENAME(@distribdb) + N'.' + N'dbo.sp_MSdeletefoldercontents'
            -- Try to delete the files
            select @dir = @dynamic_snapshot_location
            exec @retcode = @distproc @dir
            -- Ignore errors
        end
    end
    select @retcode = 0

    begin transaction
    save transaction sp_MSdropmergedynamicsnapshotjob

    -- Delete row from MSdynamicsnapshotjobs
    delete dbo.MSdynamicsnapshotjobs
     where pubid = @pubid
       and job_id = @dynamic_snapshot_jobid

    if @@error <> 0
        goto Failure

    if @ignore_distributor = 0
    begin
        -- Drop the dynamic snapshot job at the distributor
        select @distproc = QUOTENAME(RTRIM(@rpcsrvname)) + N'.' + QUOTENAME(@distribdb) + N'.' + N'dbo.sp_MSdrop_dynamic_snapshot_agent'

        exec @retcode = @distproc @publisher, @publisher_db, @publication, @agent_id
        if @retcode <> 0 or @@error <> 0
            goto Failure
    end
    commit transaction
    return 0

Failure:
    rollback transaction sp_MSdropmergedynamicsnapshotjob
    commit transaction
    return 1
end

 
Last revision 2008RTM
See also

  sp_addmergearticle (Procedure)
sp_changemergearticle (Procedure)
sp_dropdynamicsnapshot_job (Procedure)
sp_dropmergepublication (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