Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrestore_sub_merge

  No additional text.


Syntax
create procedure sys.sp_MSrestore_sub_merge
(
    @subscriber_security_mode 	int,
    @subscriber_login         	sysname,
    @subscriber_password      	nvarchar(524),
    @distributor_security_mode	int,
    @distributor_login			sysname,
    @distributor_password		sysname,
    @publisher_security_mode	int,
    @publisher_login			sysname,
    @publisher_password			sysname,
    @job_login					nvarchar(257),
    @job_password				sysname
)
AS

    SET NOCOUNT ON
    declare @retcode        int
    declare @detached       int
    declare @attached       int
    DECLARE @publisher      sysname        /* Expression used in the cursor */
    DECLARE @publisher_db   sysname     /* Expression used in the cursor */
    DECLARE @publication    sysname      /* Expression used in the cursor */
    DECLARE @distributor    sysname        /* Expression used in the cursor */
    declare @subnickname    binary(6)
    declare @subid             uniqueidentifier
    declare @detached_subid    uniqueidentifier
    declare @subscriber_server sysname
    declare @subscriber_db     sysname
    declare @enabled_for_syncmgr_int     int
    declare @enabled_for_syncmgr nvarchar(5)
    declare @pubid    uniqueidentifier, @prev_pubid uniqueidentifier
    declare @REPLICA_STATUS_BeforeRestore tinyint

    -- Initialization
    select @retcode = 0
    select @detached = 3
    select @attached = 4
    select @subscriber_server = @@SERVERNAME
    select @subscriber_db = db_name()
    set @REPLICA_STATUS_BeforeRestore= 7

    if object_id('sysmergesubscriptions') is not NULL
    begin
        set @publication= null
        select top 1 @publication = p.name from dbo.sysmergepublications p, dbo.sysmergesubscriptions s
            where p.allow_subscription_copy = 0 and p.pubid = s.subid and s.db_name = @subscriber_db and
                  UPPER(s.subscriber_server) = UPPER(@subscriber_server)
        if @publication is not null
        BEGIN
            RAISERROR (21205, 16, -1, @publication)
            RETURN (1)
        END
    end
    else
        return 0

    DECLARE hCdetachedsubscriptions CURSOR LOCAL FAST_FORWARD FOR
        SELECT subid from dbo.sysmergesubscriptions where status = @detached
    FOR READ ONLY

    -- Open a cursor for all subscriptions that have a status of 'detached'
    OPEN hCdetachedsubscriptions

    FETCH hCdetachedsubscriptions INTO @detached_subid
    WHILE (@@fetch_status <> -1)
    BEGIN
        /* Generate a guid for the Subscriber ID */
        set @subid = newid()

        /* Look for existing nickname from any other subscription */
        exec sys.sp_MSgetreplnick NULL, NULL , NULL,  @subnickname out
        if @@ERROR <> 0    goto Cleanup

        /* Generate a new replica nickname from the @subid */
        if (@subnickname is null)
        begin
            EXECUTE sys.sp_MSgenreplnickname
                            @srcguid= @subid,
                            @replnick= @subnickname output
            if @@ERROR <> 0    goto Cleanup
        end

        /*
        ** Touch up existing rows in dbo.sysmergesubscriptions & dbo.MSmerge_replinfo.
        ** Set status of attached subsription as "attached"
        */
        update dbo.sysmergesubscriptions
            set subid                 = @subid,
                subscriber_server     = @@servername,
                db_name             = @subscriber_db,
                status                 = @attached,
                replnickname        = @subnickname
            where subid = @detached_subid
        if @@ERROR <> 0    goto Cleanup

        update dbo.MSmerge_replinfo
            set repid = @subid,
                login_name     = suser_sname(suser_sid()),
                merge_jobid = NULL
            where repid = @detached_subid
        if @@ERROR <> 0    goto Cleanup

        -- update identity range table with new subid and also with no identity information
        -- for that subid so that a new one can be issued on the first merge.
        if object_id('MSmerge_identity_range') is not NULL
        begin
            update dbo.MSmerge_identity_range
                set range_begin = NULL,
                    range_end = NULL,
                    next_range_begin = NULL,
                    next_range_end = NULL,
                    max_used = NULL,
                    subid = @subid
                where subid = @detached_subid
            if @@ERROR <> 0    goto Cleanup

            exec @retcode = sys.sp_MScreate_zero_identity_constraint @subid
            if @retcode<>0 or @@error<>0
                goto Cleanup
        end

        FETCH hCdetachedsubscriptions INTO @detached_subid
    END

    close hCdetachedsubscriptions
    deallocate hCdetachedsubscriptions

    -- Check for expired subscriptions.
    exec @retcode= sys.sp_MScheckforexpiredmergesubscriptions
                        @mark_expired_as_failed_attach=1
    if @@error<>0 or @retcode<>0 return 1

    -- The open local generations from the detach-database are again the open generations in the
    -- newly attached database. We will change the guidsrc of these open generations since they
    -- are now really different generations.
    -- However the nickname of the detach-database is still
    -- in the nicknames list. This needs to be replaced by the nickname that was newly created
    -- for the detach-database, otherwise with message based the detach-database will never
    -- receive changes the attach-database puts into such a generation.
    if (object_id('dbo.MSmerge_genhistory','U')) is not null
    begin
        update dbo.MSmerge_genhistory
            set nicknames= @subnickname,
                guidsrc = newid()
            where genstatus=0
        if @@ERROR <> 0    goto Cleanup
    end

    if object_id('sysmergearticles') is not NULL
    begin
        if exists (select * from dbo.sysmergearticles)
        begin
            select @pubid = NULL
            select top 1 @pubid = pubid from dbo.sysmergepublications
            order by pubid

            while @pubid is not null
            begin
                exec @retcode = sys.sp_MSregenerate_mergetriggersprocs @pubid=@pubid
                if @@error <> 0 or @retcode <> 0
                    goto Cleanup

                select @prev_pubid = @pubid
                select @pubid = NULL

                select top 1 @pubid = pubid from dbo.sysmergepublications
                where pubid > @prev_pubid
                order by pubid
            end
        end
    end

    /* Add pull subscription agents only if MSsubscription_properties exists */
    if object_id('MSsubscription_properties') is not NULL
    	AND (@job_login IS NOT NULL
    		OR @job_password IS NOT NULL
			OR @subscriber_security_mode != 1
			OR @subscriber_login IS NOT NULL
			OR @subscriber_password IS NOT NULL		
			OR @publisher_security_mode != 1
			OR @publisher_login IS NOT NULL
			OR @publisher_password IS NOT NULL
			OR @distributor_security_mode != 1
			OR @distributor_login IS NOT NULL
			OR @distributor_password IS NOT NULL)		
    begin       		
        -- Add the agents for pull or anonymous subscriptions.
        DECLARE hCmergesubscription CURSOR LOCAL FAST_FORWARD FOR
            SELECT publisher, publisher_db, publication, distributor, enabled_for_syncmgr
                FROM dbo.MSsubscription_properties
                -- only do this for merge subscriptions
                where publication_type = 2
                --where publication is not NULL and
                --    publication <> N'' and
                --    subscription_type <> @push
        FOR READ ONLY
        OPEN hCmergesubscription

        FETCH hCmergesubscription INTO @publisher,
            @publisher_db, @publication, @distributor, @enabled_for_syncmgr_int

        WHILE (@@fetch_status <> -1)
        BEGIN
            /* Enable the subscription to be synchronized via SyncMgr if the original one was */
            if @enabled_for_syncmgr_int = 0
                set @enabled_for_syncmgr = 'false'
            else
                set @enabled_for_syncmgr = 'true'

            EXECUTE @retcode = sys.sp_addmergepullsubscription_agent
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @distributor = @distributor,
                @subscriber_security_mode = @subscriber_security_mode,
			    @subscriber_login = @subscriber_login,
			    @subscriber_password = @subscriber_password,
                @publisher_security_mode = @publisher_security_mode,
                @publisher_login = @publisher_login,
                @publisher_password = @publisher_password,
                @distributor_security_mode = @distributor_security_mode,
                @distributor_login = @distributor_login,
                @distributor_password = @distributor_password,
                @job_login = @job_login,
				@job_password = @job_password,
                @frequency_type  = 2 ,  /* 2== OnDemand */
                @enabled_for_syncmgr = @enabled_for_syncmgr

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

            FETCH hCmergesubscription INTO @publisher,
                @publisher_db, @publication, @distributor, @enabled_for_syncmgr_int
        END

        close hCmergesubscription
        deallocate hCmergesubscription
    end
    else
    begin
        /*
        ** Check to see if MSsubscription_properties table exists.
        ** If not, create it.
        */
        exec @retcode = sys.sp_MScreate_sub_tables_internal
            @tran_sub_table = 0,
            @property_table = 1,
            @sqlqueue_table = 0
        if @@error <> 0 or @retcode <> 0 goto Cleanup
    end

    return 0

Cleanup:
    return 1

 
Last revision 2008RTM
See also

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