Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreatenewreplnick

  No additional text.


Syntax
create procedure sys.sp_MScreatenewreplnick
    @replnick_old binary(6),
    @replnick_new binary(6)
as
    declare @repid_new uniqueidentifier
    declare @pubid_new uniqueidentifier

    -- After restore, the replica has a new replnick. However, we need to preserve
    -- knowledge about the previous replnick, in order to do priority resolution.
    -- For that purpose, we create a new entry in MSmerge_replinfo/sysmergesubscriptions.
    -- The entry with the changed replnick would not flow to other replicas, because
    -- its subid is already known there. Therefore, we create an additional new entry,
    -- which has the new replnick, but also a new subid.
    -- These two additional entrie both get status=7, to distinguish them from the entries
    -- that represent an actual replica.
    
    -- Example:
    -- Original situation:
    --      repid_1, subid_1, pubid_1, replnick_1, status=1
    -- Resulting situation:
    --      a) repid_1, subid_1, pubid_1, replnick_2, status=1
    --      b) repid_2, subid_2, pubid_2, replnick_1, status=7
    --      c) repid_3, subid_3, pubid_3, replnick_2, status=7
    
    -- This creation of two additional entries is not done for every existing entry
    -- with replnick_1, but only for one of them, because the only purpose is to preserve
    -- knowledge about the replnick.

    -- Make a temporary copy of the sysmergesubscriptions entry that has the old nickname.
    select top 1 * into #sm
        from dbo.sysmergesubscriptions where replnickname = @replnick_old and status <> 7 -- REPLICA_STATUS_BeforeRestore

    -- get the corresponding MSmerge_replinfo entry
    select top 1 * into #ri from dbo.MSmerge_replinfo where repid in (select subid from #sm)

    update #sm
        set status= 7 -- REPLICA_STATUS_BeforeRestore

    -- Update the nicknames of the actual replicas. ("a" in the example.)
    update dbo.sysmergesubscriptions
        set replnickname= @replnick_new
        where replnickname = @replnick_old and
              status <> 7

    -- Add an entry with the old nickname, but new ids. ("b" in the example.)
    
    set @repid_new= newid()
    set @pubid_new= newid()

    update #ri
        set repid= @repid_new

    update #sm
        set subid= @repid_new,
            pubid= @pubid_new


    insert into dbo.sysmergesubscriptions
         (subscriber_server,db_name,pubid,datasource_type,subid,replnickname,replicastate,status,
          subscriber_type,subscription_type,sync_type,description,priority,recgen,recguid,sentgen,sentguid,
          schemaversion,schemaguid,last_validated,attempted_validate,last_sync_date,last_sync_status,last_sync_summary,
          metadatacleanuptime,partition_id,cleanedup_unsent_changes,replica_version,supportability_mode,application_name,last_makegeneration_datetime)
    select subscriber_server,db_name,pubid,datasource_type,subid,replnickname,replicastate,status,
           subscriber_type,subscription_type,sync_type,description,priority,recgen,recguid,sentgen,sentguid,
           schemaversion,schemaguid,last_validated,attempted_validate,last_sync_date,last_sync_status,last_sync_summary,
           metadatacleanuptime,partition_id,cleanedup_unsent_changes,replica_version,supportability_mode,application_name,last_makegeneration_datetime
    from #sm

    -- because of the identity column sync_info added to MSmerge_replinfo for lightweight subscriptions,
    -- we have specify to the expilicit columns in this insert
    --insert into dbo.MSmerge_replinfo
    --  select * from #ri
    insert into dbo.MSmerge_replinfo
        (repid, use_interactive_resolver, validation_level,
         resync_gen, login_name, hostname, merge_jobid)
     select repid, use_interactive_resolver, validation_level,
            resync_gen, login_name, hostname, merge_jobid from #ri

    -- Add an entry with the new nickname, and also new ids. ("c" in the example.)
    
    set @repid_new= newid()
    set @pubid_new= newid()

    update #ri
        set repid= @repid_new

    update #sm
        set subid= @repid_new,
            replnickname= @replnick_new,
            pubid= @pubid_new

    insert into dbo.sysmergesubscriptions
         (subscriber_server,db_name,pubid,datasource_type,subid,replnickname,replicastate,status,
          subscriber_type,subscription_type,sync_type,description,priority,recgen,recguid,sentgen,sentguid,
          schemaversion,schemaguid,last_validated,attempted_validate,last_sync_date,last_sync_status,last_sync_summary,
          metadatacleanuptime,partition_id,cleanedup_unsent_changes,replica_version,supportability_mode,application_name,last_makegeneration_datetime)
    select subscriber_server,db_name,pubid,datasource_type,subid,replnickname,replicastate,status,
           subscriber_type,subscription_type,sync_type,description,priority,recgen,recguid,sentgen,sentguid,
           schemaversion,schemaguid,last_validated,attempted_validate,last_sync_date,last_sync_status,last_sync_summary,
           metadatacleanuptime,partition_id,cleanedup_unsent_changes,replica_version,supportability_mode,application_name,last_makegeneration_datetime
    from #sm


    insert into dbo.MSmerge_replinfo
        (repid, use_interactive_resolver, validation_level,
         resync_gen, login_name, hostname, merge_jobid)
     select repid, use_interactive_resolver, validation_level,
            resync_gen, login_name, hostname, merge_jobid from #ri

    drop table #ri
    drop table #sm


 
Last revision 2008RTM
See also

  sp_MSmerge_create_sub_table (Procedure)
sp_MSrestoredbreplication (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