Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_resetsnapshotdeliveryprogress

  No additional text.


Syntax

-- Name: sp_resetsnapshotdeliveryprogress

-- Description: This procedure removes all rows (if @drop_table = 'false')
--              in the MSsnapshotdeliveryprogress table (if it exists). This
--              will effectively wipes out all memory of any previous
--              progress that any snapshot delivery processes had made to
--              the subscriber database.

-- Notes: 1) This procedure should be called at the subscriber database.
--        2) This procedure will try to acquire the
--           'snapshot_delivery_in_progress_' application lock in
--           exclusive mode prior to truncating (or drop)
--           the MSsnapshotdeliveryprogress table. It will raise an error if
--           it cannot acquire the lock in 5 seconds and the specified
--           @verbose_level >= 1.

-- Parameter: @verbose_level int (optional, default 1)
--            @drop_table nvarchar(5) (optional, default N'false')
--                - specifies whether to drop the progress table or just
--                  truncate the table

-- Returns: 0 - succeeded
--          1 - failed

-- Security: Execute permission of this procedure is granted to public;
--           procedural security check will be performed to make sure
--           that the caller is either a db_owner of the current database
--           or a sysadmin.

create procedure sys.sp_resetsnapshotdeliveryprogress
    @verbose_level int = 1,
    @drop_table nvarchar(5) = N'false'
as
begin
    set nocount on
    declare @retcode int
    declare @lock_acquired int
    declare @lock_resource nvarchar(255)
    declare @cursor_allocated bit
    declare @cursor_opened bit
    declare @pubidprefix nvarchar(100)
    declare @pubid uniqueidentifier

    select @retcode = 0
    select @lock_acquired = 0
    select @lock_resource = N'snapshot_delivery_in_progress_' +
                            db_name() collate database_default
    select @cursor_allocated = 0
    select @cursor_opened = 0
    select @pubidprefix = N':'

    exec @retcode = sys.sp_MSreplcheck_subscribe
    if @retcode <> 0 or @@error <> 0
    begin
        select @retcode = 1
        goto Failure
    end

    -- Parameter validation
    select @drop_table = lower(@drop_table collate SQL_Latin1_General_CP1_CS_AS)
    if @drop_table not in (N'true', N'false')
    begin
        raiserror (14148, 16,-1, '@drop_table')
        select @retcode = 1
        goto Failure
    end

    -- Wiping out the content of the MSsnapshotdeliveryprogress table
    -- can disrupt snapshots that are being applied to this subscription
    -- database. Try to acquire the snapshot-delivery-in-progress application
    -- lock in exclusive mode prior to wiping out the table. Note that the
    -- the distribution/merge agent will acquire the same application lock in
    -- shared mode while a snapshot is being applied. Don't try to wait
    -- for the lock for too long as other snapshot delivery processes may be
    -- blocked by our waiting. In effect, we are saying that it is OK to
    -- starve the process that is trying to truncate the progress table.

    exec @retcode = sys.sp_getapplock @Resource = @lock_resource,
                                             @LockMode = N'Exclusive',
                                             @LockOwner = N'Session',
                                             @LockTimeout = 5000, -- 5 seconds
                                             @DbPrincipal = N'db_owner'

    if @@error <> 0
    begin
        select @retcode = 1
        goto Failure
    end

    if @retcode < 0
    begin
        if @retcode = -1 and @verbose_level >= 1
        begin
            raiserror(21514,16,-1)
            select @retcode = 1
        end
        else
        begin
            select @retcode = 0
        end
        goto Failure
    end

    select @retcode = 0, @lock_acquired = 1

    if object_id('dbo.MSsnapshotdeliveryprogress') is not null
    begin
        -- Eliminating merge snapshot progress requires additional cleanup in
        -- the merge meta-data tables so do it separately
        declare hpubid cursor local fast_forward for
            select convert(uniqueidentifier, right(progress_token, len(progress_token) - len(@pubidprefix)))
              from dbo.MSsnapshotdeliveryprogress
             where left(progress_token, len(@pubidprefix)) = @pubidprefix

        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_allocated = 1

        open hpubid
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_opened = 1

        fetch hpubid into @pubid
        while (@@fetch_status <> -1)
        begin

            exec @retcode = sys.sp_MSpurgepartialmergesnapshot
                    @pubid = @pubid
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
            fetch hpubid into @pubid
        end

        close hpubid
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_opened = 0

        deallocate hpubid
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        select @cursor_allocated = 0

        if @drop_table = N'false'
        begin
            truncate table dbo.MSsnapshotdeliveryprogress
            if @@error <> 0
            begin
                select @retcode = 1
                goto Failure
            end
        end
        else
        begin
            drop table dbo.MSsnapshotdeliveryprogress
            if @@error <> 0
            begin
                select @retcode = 1
                goto Failure
            end
        end
    end
    if @retcode <> 0 or @@error <> 0
    begin
        select @retcode = 1
        goto Failure
    end

Failure:
    if @cursor_opened = 1
    begin
        close hpubid
    end

    if @cursor_allocated = 1
    begin
        deallocate hpubid
    end

    if @lock_acquired = 1
    begin
        exec @retcode = sys.sp_releaseapplock @Resource = @lock_resource,
                                                     @LockOwner = N'Session',
                                                     @DbPrincipal = N'db_owner'
        if @@error <> 0 or @retcode < 0
        begin
           select @retcode = 1
        end
    end
    return @retcode
end

 
Last revision 2008RTM
See also

  sp_dropmergepullsubscription (Procedure)
sp_mergesubscription_cleanup (Procedure)
sp_MSremovedbreplication (Procedure)
sp_reinitmergepullsubscription (Procedure)
sp_reinitpullsubscription (Procedure)
sp_subscription_cleanup (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