Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSregisterdynsnapseqno

  No additional text.


Syntax

-- Name: sp_MSregisterdynsnapseqno

-- Description: This procedure works exactly like
--              sp_MSregistermergesnappubid except that it uses
--              the dynamic snapshot sequence number (a guid) to detect
--              whether a different dynamic snapshot is being delivered over
--              a previously interrupted dynamic snapshot.

-- Parameters: @snapshot_session_token nvarchar(260) (mandatory)
--             @dynsnapseqno uniqueidentifier (mandatory)

-- Note: This procedure should only be called by the merge agent at the
--       subscriber database.

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

-- Security: This is a public interface object, security check is performed
--           inside this procedure to restrict access to sysadmins and
--           db_owners of the subscriber database.

create procedure sys.sp_MSregisterdynsnapseqno (
    @snapshot_session_token nvarchar(260),
    @dynsnapseqno uniqueidentifier
    )
as
begin
    set nocount on
    declare @retcode int
    declare @dynsnapseqnoprefix nvarchar(100)
    declare @pubidprefix nvarchar(100)
    declare @snapshot_progress_token nvarchar(500)
    declare @transaction_opened bit
    declare @previous_dynsnapseqno uniqueidentifier
    declare @snapshot_progress_token_hash int
    declare @pubid uniqueidentifier

    -- Initializations
    select @retcode = 0
    select @dynsnapseqnoprefix = N':'
    select @pubidprefix = N':'
    select @transaction_opened = 0

    select @previous_dynsnapseqno = null

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

    if object_id('dbo.MSsnapshotdeliveryprogress', 'U') is not null
    begin

        -- Try to get the previous dynamic snapshot sequence number
        select @previous_dynsnapseqno =
                    convert(uniqueidentifier, right(progress_token, len(progress_token) - len(@dynsnapseqnoprefix)))
          from dbo.MSsnapshotdeliveryprogress
         where session_token = @snapshot_session_token
           and left(progress_token, len(@dynsnapseqnoprefix)) = @dynsnapseqnoprefix
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

        -- If the dynamic snapshot sequence number is different, cleanup
        -- everything for the new dynamic snapshot
        if @previous_dynsnapseqno is not null and
           @previous_dynsnapseqno <> @dynsnapseqno
        begin
            begin transaction
            save transaction sp_MSregisterdynsnapseqno
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
            select @transaction_opened = 1

            -- Try to get the pubid for this snapshot
            select @pubid =
                    convert(uniqueidentifier, right(progress_token, len(progress_token) - len(@pubidprefix)))
              from dbo.MSsnapshotdeliveryprogress
             where session_token = @snapshot_session_token
               and left(progress_token, len(@pubidprefix)) = @pubidprefix

            -- Do cleanup
            exec @retcode = sys.sp_MSpurgepartialmergesnapshot
                    @pubid = @pubid
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

            -- Register both the pubid and the dynsnapseqno again because
            -- both registrations would have been removed by the cleanup above
            select @snapshot_progress_token = @pubidprefix + convert(nvarchar(100), @pubid)
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

            exec @retcode = sys.sp_MSrecordsnapshotdeliveryprogress
                    @snapshot_session_token = @snapshot_session_token,
                    @snapshot_progress_token = @snapshot_progress_token
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

            select @snapshot_progress_token = @dynsnapseqnoprefix + convert(nvarchar(100), @dynsnapseqno)
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

            exec @retcode = sys.sp_MSrecordsnapshotdeliveryprogress
                    @snapshot_session_token = @snapshot_session_token,
                    @snapshot_progress_token = @snapshot_progress_token
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

            commit transaction
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
            select @transaction_opened = 0
        end
        else if @previous_dynsnapseqno is null
        begin
            select @snapshot_progress_token = @dynsnapseqnoprefix + convert(nvarchar(100), @dynsnapseqno)
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

            exec @retcode = sys.sp_MSrecordsnapshotdeliveryprogress
                    @snapshot_session_token = @snapshot_session_token,
                    @snapshot_progress_token = @snapshot_progress_token
            if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
        end
    end
    else
    begin
        select @snapshot_progress_token = @dynsnapseqnoprefix + convert(nvarchar(100), @dynsnapseqno)
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end

        exec @retcode = sys.sp_MSrecordsnapshotdeliveryprogress
                @snapshot_session_token = @snapshot_session_token,
                @snapshot_progress_token = @snapshot_progress_token
        if @@error<>0 or @retcode<>0 begin select @retcode=1 goto Failure end
    end

Failure:
    if @transaction_opened = 1
    begin
        rollback transaction sp_MSregisterdynsnapseqno
        commit transaction
    end

    return @retcode
end

 
Last revision 2008RTM
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