sp_MSunmarkreplinfo (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSunmarkreplinfo(nvarchar @object
, nvarchar @owner
, smallint @type)

MetaData:

 create procedure sys.sp_MSunmarkreplinfo(  
    @object                sysname,            --  Name of the table, unqualitied  --   
    @owner                sysname = NULL,            --  Name of the owner, unqualified  --   
    @type                smallint = 0        --  default is to unmark, as name implies  --   
)AS  
    declare @id    int  
    declare @qualified_name            nvarchar(517)  
    declare @retcode                int  
  
    exec @retcode = sys.sp_MSreplcheck_subscribe_withddladmin  
    if @@error<>0 or @retcode<>0  
        return (1)  
  
    if @owner is NULL or @owner=''  
        select @owner = schema_name(schema_id) from sys.objects where name=@object  
    select @qualified_name = QUOTENAME(@owner) + '.' + QUOTENAME(@object)   
  
    if object_id(@qualified_name, 'U') is NULL  
        return 0  
  
    BEGIN TRANSACTION  
    select @id = object_id(@qualified_name)  
    if not (@id is null)  
    begin  
        EXEC %%Object(MultiName = @qualified_name).LockMatchID(ID = @id, Exclusive = 1, BindInternal = 0)  
-- EXEC %%Object(MultiName = @qualified_name).LockExclusiveMatchID(ID = @id)  
        if @@error <> 0  
            select @id = null  
    end  
  
    if not (@id is null)  
    begin  
        if @type = 0    --  type = 0, unmark; else mark the bit  --   
            EXEC %%Relation(ID = @id).SetMergePublished(Value = 0, SetColumns = 1)  
        else  
            EXEC %%Relation(ID = @id).SetMergePublished(Value = 1, SetColumns = 1)  
    end  
    COMMIT TRANSACTION  

sp_MSupdatecachedpeerlsn (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdatecachedpeerlsn(int @type
, int @agent_id
, nvarchar @originator
, nvarchar @originator_db
, int @originator_publication_id
, int @originator_db_version
, varbinary @originator_lsn)

MetaData:

 create procedure sys.sp_MSupdatecachedpeerlsn  
(  
    @type                        int,        --  1-insert/update, 2-clear  
    @agent_id                    int,  
    @originator                    sysname = NULL,  
    @originator_db                sysname = NULL,  
    @originator_publication_id    int = NULL,  
    @originator_db_version        int = NULL,  
    @originator_lsn                varbinary(16) = NULL  
)  
as  
begin  
    declare @retcode bit  
  
    --  security check - the distribution agent must have access  
    exec @retcode = sys.sp_MScheck_pull_access @agent_id = @agent_id, @agent_type = 0  
    if @@error <> 0 or @retcode <> 0  
    begin  
        return 1  
    end  
      
    if @type not in (0, 1, 2)  
    begin  
        --  Invalid '@type' value for stored procedure 'sys.sp_MSupdatecachedpeerlsn'.  
        raiserror(20587, 16, -1, '@type', 'sys.sp_MSupdatecachedpeerlsn')  
        return 1  
    end  
  
    begin tran t_MScached_peer_lsns  
      
    --  insert/update row  
    if @type = 1  
    begin  
        update MScached_peer_lsns  
            set originator_lsn = @originator_lsn  
            where agent_id                         = @agent_id  
                and UPPER(originator)             = UPPER(@originator)  
                and originator_db                 = @originator_db  
                and originator_publication_id     = @originator_publication_id  
                and originator_db_version         = @originator_db_version  
        if @@rowcount < 1  
        begin  
            insert into MScached_peer_lsns  
            (  
                agent_id,   
                originator,   
                originator_db,  
                originator_publication_id,  
                originator_db_version,  
                originator_lsn  
            )  
            values  
            (  
                @agent_id,   
                UPPER(@originator),   
                @originator_db,  
                @originator_publication_id,  
                @originator_db_version,  
                @originator_lsn  
            )  
            if @@rowcount < 1 or @@error <> 0  
                goto FAILURE  
        end  
          
        if @@error <> 0  
        begin  
            --  The distribution agent was unable to update the cached lsns for Originator:%s OriginatorDB:%s OriginatorDBVersion:%d OriginatorPublicationID:%d.  
            raiserror(21680, 16, -1, @originator, @originator_db, @originator_db_version, @originator_publication_id)  
            goto FAILURE  
        end  
    end  
    --  clear cache for the specified agent  
    else if @type = 2  
    begin  
        delete from MScached_peer_lsns   
            where agent_id = @agent_id  
        if @@error <> 0  
                   goto FAILURE  
    end  
      
    commit transaction t_MScached_peer_lsns  
  
    return 0  
FAILURE:  
    if @@trancount > 0  
        rollback transaction t_MScached_peer_lsns  
  
    --  The procedure sys.sp_MSupdatecachedpeerlsn failed to UPDATE the resource MScached_peer_lsns Server error =  21499.  
    raiserror (21499, 16, -1, 'sys.sp_MSupdatecachedpeerlsn', 'UPDATE', 'MScached_peer_lsns', @@error)  
       return 1  
end  

sp_MSuniquename (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSuniquename(nvarchar @seed
, int @start)

MetaData:

   
create procedure sys.sp_MSuniquename  
    @seed nvarchar(128), @start int = null  
as  
    --  Return a unique name for sysobjects, based on a passed-in seed.  --   
    set nocount on  
    declare @i int, @append nvarchar(10), @seedlen int, @temp nvarchar(128), @recalcseedlen int, @seedcharlen int  
    select @i = 1, @seedlen = datalength(@seed), @recalcseedlen = 1, @seedcharlen = 0  
    if (@start is not null and @start >= 0)  
        select @i = @start  
    while 1 < 2  
    begin  
        --  This is probably overkill, but start at max length of seed name, leaving room under OSQL_DBLSYSNAME_SET for @append.  --   
        --  We'll work our way back along the string if more room needed (pathological user).  --   
        select @append = ltrim(str(@i)) + N'__' + ltrim(str(@@spid))  
        if (@recalcseedlen = @i or @seedcharlen = 0)  
        begin  
            while @recalcseedlen <= @i  
                select @recalcseedlen = @recalcseedlen * 10  
            select @seedcharlen = @seedlen  
            if ((@seedlen + datalength(@append)) > 128) begin  
                select @seedlen = 128 - datalength(@append)  
  
                --  Get the charlen of this datalength for the substring() call.  --   
                select @seedcharlen = @seedlen  
               --  exec sp_GetMBCSCharLen @seed, @seedlen, @seedcharlen out  --   
            end        --  Recalc seedlen  --   
        end        --  Check seedlen  --   
  
        select @temp = substring(@seed, 1, @seedcharlen) + @append  
  
        --  If I don't set a limit somewhere, it's gonna look hung --  I'd rather get a nonunique error.  --   
        if object_id(@temp) is null or @i > 999999        --  if increased, watch out for overflow of @recalcseedlen  --   
        begin  
            set nocount off  
            select Name = @temp, Next = @i + 1  
            return 0  
        end  
        select @i = @i + 1  
    end  

sp_MSupdatepeerlsn (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdatepeerlsn(nvarchar @originator
, nvarchar @originator_db
, int @originator_publication_id
, int @originator_db_version
, varbinary @originator_lsn)

MetaData:

 create procedure sys.sp_MSupdatepeerlsn  
(  
    @originator                    sysname,  
    @originator_db                sysname,  
    @originator_publication_id        int,  
    @originator_db_version        int,  
    @originator_lsn                varbinary(10)                      
)  
as  
begin  
    declare @retcode         bit,  
            @error            int,  
            @rows_affected    int  
      
    --  security check for subscriber  
    exec @retcode = sys.sp_MSreplcheck_subscribe   
    if @@error <> 0 or  @retcode <> 0   
    begin  
        return 1  
    end  
  
    begin transaction tran_sp_MSupdatepeerlsn  
    save transaction tran_sp_MSupdatepeerlsn  
      
    --  update the peer_lsn  
    update MSpeer_lsns  
        set last_updated     = getdate(),  
            originator_lsn     = @originator_lsn  
        from MSpeer_lsns with (index(uci_MSpeer_lsns))  
        where originator                    = UPPER(@originator)  
            and originator_db                 = @originator_db  
            and originator_publication_id    = @originator_publication_id   
            and originator_db_version        = @originator_db_version  
            and originator_lsn                <= @originator_lsn  
  
    --  cache the row count and error  
    select @rows_affected     = @@rowcount,  
            @error            = @@error  
  
    --  if we hit an error let the user know and exit  
    if @error <> 0  
    begin  
        goto FAILURE  
    end  
  
    --  if row count is 0 then we know we are not a peer and the subcription is not found.  
    if @rows_affected <> 1  
    begin  
        declare @pub_id varchar(10)  
  
        select @pub_id = cast(@originator_publication_id as varchar)  
  
        --  No peers were found for %s:%s:%s.  
        raiserror(20807, 16, -1, @originator, @originator_db, @pub_id)  
        goto FAILURE  
    end  
  
    commit transaction tran_sp_MSupdatepeerlsn  
      
    return 0  
FAILURE:  
    rollback transaction tran_sp_MSupdatepeerlsn  
    commit transaction tran_sp_MSupdatepeerlsn  
  
    --  The procedure sys.sp_MSupdatepeerlsn failed to UPDATE the resource MSpeer_lsns. Server error =  0.  
    raiserror (21499, 16, -1, 'sys.sp_MSupdatepeerlsn', 'UPDATE', 'MSpeer_lsns.', @error)  
  
      
    return 1  
end  

sp_MSupdatelastsyncinfo (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdatelastsyncinfo(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @subscription_type
, int @last_sync_status
, nvarchar @last_sync_summary)

MetaData:

 create procedure sys.sp_MSupdatelastsyncinfo  
    @publisher      sysname,                    --  publishing server name  
    @publisher_db   sysname,                    --  publishing database name. If NULL then same as current db  
    @publication    sysname,                    --  publication name,  
    @subscription_type int,  
    @last_sync_status     int,  
    @last_sync_summary     sysname  
AS  
    set nocount on  
    declare @retcode int  
  
    --   Security Check  
    EXEC @retcode = sys.sp_MSreplcheck_subscribe  
    IF @@ERROR <> 0 or @retcode <> 0  
        RETURN(1)  
  
    if object_id('dbo.MSsubscription_agents') is null  
        return(1)  
  
    --  For non independent agent publications  
    if @publication is null or @publication = ''  
        set @publication = 'ALL'  
  
    if not exists (select * from MSsubscription_agents where  
            UPPER(publisher) = UPPER(@publisher)  
            and publisher_db =  @publisher_db  
            and publication = @publication  
            and subscription_type = @subscription_type)  
    begin  
        exec sys.sp_MSinit_subscription_agent  
            @publisher,  
            @publisher_db,  
            @publication,  
            @subscription_type  
    end  
  
    update MSsubscription_agents set       
        last_sync_time = getdate(),   
        last_sync_status = @last_sync_status,   
        last_sync_summary = @last_sync_summary  
        where UPPER(publisher) = UPPER(@publisher)  
            and publisher_db =  @publisher_db  
            and publication = @publication  
            and subscription_type = @subscription_type  
  
    update MSreplication_subscriptions set  
        time = convert(smalldatetime, getdate())   
    WHERE UPPER(publisher) = UPPER(@publisher) AND  
        publisher_db  = @publisher_db AND  
        ((@publication = N'ALL' and independent_agent = 0) or   
        @publication = publication) and  
        subscription_type = @subscription_type  

sp_MSupdateinitiallightweightsubscription (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdateinitiallightweightsubscription(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication_name
, uniqueidentifier @pubid
, bit @allow_subscription_copy
, int @retention
, int @conflict_logging
, int @status
, bit @allow_synctoalternate
, int @replicate_ddl
, bit @automatic_reinitialization_policy)

MetaData:

 create procedure sys.sp_MSupdateinitiallightweightsubscription  
    @publisher                    sysname,  
    @publisher_db                sysname,  
    @publication_name            sysname,  
    @pubid                        uniqueidentifier,  
    @allow_subscription_copy    bit,  
    @retention                    int,  
    @conflict_logging           int,  
    @status                     int,  
    @allow_synctoalternate        bit,  
    @replicate_ddl                int,  
    @automatic_reinitialization_policy bit  
as  
    set nocount on  
  
    declare @retcode int  
      
    --  security check  
    exec @retcode = sys.sp_MSreplcheck_subscribe   
    if @retcode <> 0 or @@error <> 0 return 1  
      
    declare @pubid_local uniqueidentifier  
  
    select @pubid_local = pubid from dbo.sysmergepublications   
        where upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(@publisher collate SQL_Latin1_General_CP1_CS_AS) and   
              publisher_db = @publisher_db and   
              name = @publication_name  
      
    update dbo.sysmergepublications   
        set pubid=    @pubid,   
            retention= @retention,  
            status= @status,  
            centralized_conflicts= case @conflict_logging  
                when 1 then 1  
                when 2 then 1  
                else 0  
            end,  
            decentralized_conflicts= case @conflict_logging  
                when 1 then 0  
                when 2 then 1  
                else 1  
            end,  
            allow_subscription_copy= @allow_subscription_copy,  
            allow_synctoalternate= @allow_synctoalternate,  
            replicate_ddl= @replicate_ddl,  
            automatic_reinitialization_policy= @automatic_reinitialization_policy  
        where pubid = @pubid_local  
    if @@error <> 0 return 1  
  
    update dbo.sysmergesubscriptions set pubid= @pubid, replicastate= newid() where pubid = @pubid_local  
    if @@error <> 0 return 1  
  
    update dbo.sysmergesubscriptions set subid = @pubid where subid = @pubid_local  
    if @@error <> 0 return 1  
      
    update dbo.MSmerge_replinfo set repid = @pubid where repid = @pubid_local  
    if @@error <> 0 return 1  
      
    execute @retcode= sys.sp_MSrepl_ddl_triggers @type='merge', @mode='add'  
    if @@error <> 0 or @retcode <> 0 return (1)  
          
    return 0  

sp_MSupdategenhistory (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdategenhistory(uniqueidentifier @guidsrc
, uniqueidentifier @pubid
, bigint @gen
, int @art_nick
, int @is_ssce_empty_sync
, smallint @publication_number
, int @partition_id)

MetaData:

   
create procedure sys.sp_MSupdategenhistory  
    (@guidsrc uniqueidentifier,   
     @pubid uniqueidentifier,   
     @gen bigint,   
     @art_nick int = NULL,  
     @is_ssce_empty_sync int = 0,  
     @publication_number smallint = NULL,  
     @partition_id int = NULL)  
as  
          
    --   
    --  Check to see if current publication has permission  
     --   
    declare @retcode int  
    , @changecount int  
  
    if ({fn ISPALUSER(@pubid)} <> 1)  
    begin  
        if (@pubid is NULL)  
        begin  
                RAISERROR (21723, 16, -1, 'sp_MSupdategenhistory')  
                return 1  
        end  
        else  
        begin  
                RAISERROR (14126, 11, -1)  
                return 1  
        end  
    end  
      
    if (@guidsrc is null)  
    begin  
        RAISERROR(14043, 16, -1, '@guidsrc', 'sp_MSupdategenhistory')  
        return (1)  
    end  
          
    if @art_nick = 0 set @art_nick = NULL  
    set @changecount = 0  
      
    begin tran  
    save tran sp_MSupdategenhistory  
      
    exec @retcode = sys.sp_MSget_gen_approx_changecount @gen, @changecount OUTPUT  
        if @retcode <> 0 or @@error <> 0  
                goto FAILURE  
  
    --  If changecount is zero and this is a upload from ssce then delete the generation.  
    --  This prevents uploading of empty generations.  
    if @changecount = 0 and @is_ssce_empty_sync = 1  
    begin  
        delete from dbo.MSmerge_genhistory where generation = @gen  
        commit  
        return (0)  
    end  
      
    if @partition_id is null  
    begin  
        exec @retcode = sys.sp_MSmap_generation_to_partitionids @gen, @art_nick  
        if @retcode <> 0 or @@error <> 0  
                goto FAILURE  
    end  
    else  
    begin  
        insert into dbo.MSmerge_generation_partition_mappings with (rowlock)  
            (publication_number, partition_id, generation, changecount)  
                values (@publication_number, @partition_id, @gen, @changecount)  
        if @@error <> 0  
                goto FAILURE  
    end  
                          
    update dbo.MSmerge_genhistory with (rowlock)        
        set genstatus = 2,   
            art_nick = case when isnull(@art_nick,0) <> 0 then @art_nick else art_nick end,   
            coldate= getdate(),  
            changecount = @changecount  
    where generation = @gen --  and guidsrc = @guidsrc   
    if @@rowcount = 0  
    begin  
        declare @replnick binary(6)  
        declare @nickbin binary(8)  
  
        exec sys.sp_MSgetreplnick @replnick = @replnick out  
        if @@ERROR<>0 goto FAILURE  
  
        --  Append guard byte  
        set @nickbin= @replnick + 0xFF  
  
        set identity_insert dbo.MSmerge_genhistory on  
  
        insert into dbo.MSmerge_genhistory with (rowlock)  
            (guidsrc, genstatus, generation, art_nick, nicknames, coldate)   
            values (@guidsrc, 2, @gen, @art_nick, @nickbin , getdate())  
  
        if @@ERROR<>0 goto FAILURE  
              
        set identity_insert dbo.MSmerge_genhistory off  
  
    end  
  
    commit  
  
    return (0)  
      
FAILURE:  
    rollback tran sp_MSupdategenhistory  
    commit tran  
    RAISERROR(25021, 16, -1, @gen)  
    return(1)  

sp_MSupdategenerations_afterbcp (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdategenerations_afterbcp(uniqueidentifier @pubid)

MetaData:

 create procedure sys.sp_MSupdategenerations_afterbcp  
    @pubid uniqueidentifier  
as  
    declare @guidsrc uniqueidentifier  
    declare @guidstr varchar(100)  
    declare @pubid_ins uniqueidentifier  
    declare @nicknames varbinary(1000)  
    declare @art_nick int  
    declare @artnick int  
    declare @coldate datetime  
    declare @subscriber_number int  
    declare @genstatus int  
    declare @changecount int  
    declare @source_gen bigint  
    declare @dest_gen bigint  
    declare @objid sysname  
    declare @cmd nvarchar(4000)  
    declare @retcode int  
    declare @temp_genhistory_table sysname  
    declare @qual_temp_genhistory_table sysname  
  
    --  Security check  
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck   
    if (@retcode <> 0) or (@@error <> 0)  
            return 1  
  
    select @guidstr = sys.fn_MSguidtostr(@pubid)  
    select @temp_genhistory_table = 'MSmerge_genhistory_' + LOWER(@guidstr)  
    select @qual_temp_genhistory_table = 'dbo.' + @temp_genhistory_table  
      
    select @objid = object_id from sys.objects where name = @temp_genhistory_table  
    if @objid is NULL  
    begin  
        raiserror(20008, 16, -1)  
        return 1  
    end     
  
    begin tran  
    save tran updategenerations_afterbcp  
    --  insert into genhistory, update to contents +tombstone , drop temp table should all be in the same transaction  
    --  to avoid the scenario where merge agent fails after inserting into genhistory but before updating contents  
    --  tombstone. While retrying merge, bcp would not be re-applied and the same old rows from the temp table  
    --  could get reinserted into MSmerge_genhistory (with new generation no, but same guidsrc)  
  
    select @cmd = 'insert into dbo.MSmerge_genhistory (guidsrc, pubid, art_nick, nicknames, coldate, genstatus, changecount, subscriber_number)   
    select guidsrc, pubid, art_nick, nicknames, coldate, genstatus, changecount, subscriber_number from ' + @qual_temp_genhistory_table  
  
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
          
    select @cmd = 'alter table ' + @qual_temp_genhistory_table + ' add mgh_generation bigint'  
  
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
          
        --  add clustered index  
    select @cmd = 'create unique clustered index c1' + @temp_genhistory_table + ' on ' + @qual_temp_genhistory_table + '(guidsrc)'   
  
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
  
    select @cmd = 'update ' + @qual_temp_genhistory_table + ' set mgh_generation = mgh.generation   
    from ' + @qual_temp_genhistory_table + ' tgh    
    join dbo.MSmerge_genhistory mgh on mgh.guidsrc = tgh.guidsrc'      
      
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
  
    select @cmd = 'drop index c1' + @temp_genhistory_table + ' on ' + @qual_temp_genhistory_table   
      
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
          
    --  add clustered index  
    select @cmd = 'create unique clustered index c2' + @temp_genhistory_table + ' on ' + @qual_temp_genhistory_table + '(generation)'   
  
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
  
    select @cmd = 'update dbo.MSmerge_contents set generation = tgh.mgh_generation   
    from dbo.MSmerge_contents mc   
    join ' + @qual_temp_genhistory_table + ' tgh on tgh.generation = mc.generation and tgh.art_nick = mc.tablenick'  
  
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
  
    select @cmd = 'update dbo.MSmerge_tombstone set generation = tgh.mgh_generation   
    from dbo.MSmerge_tombstone mt  
    join ' + @qual_temp_genhistory_table + ' tgh on tgh.generation = mt.generation and tgh.art_nick = mt.tablenick'  
  
    exec @retcode = sys.sp_executesql @cmd  
    if @@error <> 0 or @retcode <> 0  
        goto FAILURE  
  
    --  drop temp genhistory table  
    exec ('drop table ' + @qual_temp_genhistory_table)  
    commit tran  
  
    return (0)  
FAILURE:    
    if @@trancount>0  
    begin  
        rollback tran updategenerations_afterbcp  
        commit tran  
    end  
    return(1)  

sp_MSupdate_tracer_history (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdate_tracer_history(int @tracer_id)

MetaData:

 --   
--  Name:   
--         sp_MSupdate_tracer_history  
--    
--  Description:   
--         This procedure will update the tracer history row's distrib_commit   
--         time to getdate().   
--     
--  Parameters:   
--         @tracer_id    int        Id of the tracer to be updated  
--   
--  Returns:   
--         0 - succeeded  
--       1 - failed  
--   
--  Result:   
--         None  
--   
--  Security:   
--         Sysadmin/dbo (run by logreader only)  
--   
create procedure sys.sp_MSupdate_tracer_history  
(  
    @tracer_id    int  
)  
as  
begin  
    set nocount on  
      
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
  
    --  update the requested tracer token  
    --  if the tracer token row no longer exists then  
    --  we will exit with no error because this may happen  
    --  if cleanup comes bye and removes the token row  
    update MStracer_tokens  
        set distributor_commit = getdate()  
        where tracer_id = @tracer_id  
    if @@error <> 0  
    begin  
        return 1  
    end  
  
    return 0  
end  

sp_MSupdate_subscriber_info (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdate_subscriber_info(nvarchar @publisher
, nvarchar @subscriber
, tinyint @type
, nvarchar @login
, nvarchar @password
, int @commit_batch_size
, int @status_batch_size
, int @flush_frequency
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date
, int @retryattempts
, int @retrydelay
, nvarchar @description
, int @security_mode)

MetaData:

 CREATE PROCEDURE sys.sp_MSupdate_subscriber_info  
(  
    @publisher sysname,  
    @subscriber sysname,  
    @type tinyint = NULL,  
    @login sysname = NULL,  
    @password nvarchar(524) = '%',  
    @commit_batch_size int = NULL,  
    @status_batch_size int = NULL,  
    @flush_frequency int = NULL,  
    @frequency_type int = NULL,  
    @frequency_interval int = NULL,  
    @frequency_relative_interval int = NULL,  
    @frequency_recurrence_factor int = NULL,  
    @frequency_subday int = NULL,  
    @frequency_subday_interval int = NULL,  
    @active_start_time_of_day int = NULL,  
    @active_end_time_of_day int = NULL,  
    @active_start_date int = NULL,  
    @active_end_date int = NULL,  
    @retryattempts int = NULL,  
    @retrydelay int = NULL,  
    @description nvarchar (255) = NULL,  
    @security_mode int = NULL  
)  
AS  
begin  
   set nocount on  
     
   declare @cmd1 nvarchar (255)  
   declare @retcode int  
   declare @platform_nt binary  
  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
      
   select @platform_nt = 0x1  
  
   IF (UPPER(@subscriber) = UPPER(@@SERVERNAME) and ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)  
        BEGIN  
            RAISERROR(21038, 16, -1)  
            goto FAILED  
        END  
  
  
   begin transaction  
   save transaction update_subscriber  
     
   --  Check if subscriber exists  --   
   if not exists (select * from MSsubscriber_info   
                  where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber))  
       goto FAILED  
  
   if not exists (select * from MSsubscriber_schedule   
                  where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0)  
       goto FAILED      
     
   if @type is not NULL  
      update MSsubscriber_info set type = @type   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber)  
      if @@error <> 0  
         goto FAILED  
  
    if @security_mode = 1  
    begin  
        --  set the password here to newid. by doing  
        --  this we will force a reset of the password  
        --  to an encrypted invalid password. the encryp  
        --  and storing of the password is below...  
          
        select @password = newid()  
          
        update MSsubscriber_info   
            set security_mode = 1,  
                login = N''  
            where UPPER(publisher) = UPPER(@publisher)   
                and UPPER(subscriber) = UPPER(@subscriber)  
        if @@error <> 0  
            goto FAILED  
    end  
    else if @security_mode = 0  
    begin  
        update MSsubscriber_info   
            set security_mode = 0  
            where UPPER(publisher) = UPPER(@publisher)   
                and UPPER(subscriber) = UPPER(@subscriber)  
        if @@error <> 0  
            goto FAILED  
    end  
    else if @security_mode is not NULL  
    begin  
        raiserror(14109, 16, -1)  
        goto FAILED  
    end  
  
    if @login is not NULL  
    begin  
        update MSsubscriber_info   
            set login = @login   
        where UPPER(publisher) = UPPER(@publisher)   
            and UPPER(subscriber) = UPPER(@subscriber)  
        if @@error <> 0  
            goto FAILED  
    end  
      
    if isnull(@password, '') <> '%'  
    begin  
        --  Encrypt the password  
        EXEC @retcode = sys.sp_MSreplencrypt @password OUTPUT  
        IF @@error <> 0 OR @retcode <> 0  
            goto FAILED  
  
        update MSsubscriber_info   
            set password = @password   
            where UPPER(publisher) = UPPER(@publisher)   
                and UPPER(subscriber) = UPPER(@subscriber)  
        if @@error <> 0  
            goto FAILED  
    end  
       
   if @frequency_type is not NULL  
      update MSsubscriber_schedule set frequency_type = @frequency_type   
        where  UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @frequency_interval is not NULL  
      update MSsubscriber_schedule set frequency_interval = @frequency_interval   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @frequency_relative_interval is not NULL  
      update MSsubscriber_schedule set frequency_relative_interval = @frequency_relative_interval   
      where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
       if @@error <> 0  
         goto FAILED  
     
   if @frequency_recurrence_factor is not NULL  
      update MSsubscriber_schedule set frequency_recurrence_factor = @frequency_recurrence_factor   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @frequency_subday is not NULL  
      update MSsubscriber_schedule set frequency_subday = @frequency_subday   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @frequency_subday_interval is not NULL  
      update MSsubscriber_schedule set frequency_subday_interval = @frequency_subday_interval   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @active_start_time_of_day is not NULL  
      update MSsubscriber_schedule set active_start_time_of_day = @active_start_time_of_day   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @active_end_time_of_day is not NULL  
      update MSsubscriber_schedule set active_end_time_of_day = @active_end_time_of_day   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @active_start_date is not NULL  
      update MSsubscriber_schedule set active_start_date = @active_start_date   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @active_end_date is not NULL  
      update MSsubscriber_schedule set active_end_date = @active_end_date   
      where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0  
      if @@error <> 0  
         goto FAILED  
     
   if @description is not NULL  
      update MSsubscriber_info set description = @description   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber)  
      if @@error <> 0  
         goto FAILED  
  
    if @security_mode is not NULL  
      update MSsubscriber_info set security_mode = @security_mode   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber)  
      if @@error <> 0  
         goto FAILED  
  
   commit transaction  
   return (0)  
     
FAILED:  
    if @@trancount > 0  
    begin  
        rollback transaction update_subscriber  
        commit tran --  to finish off the tran we started in this proc (though   
                    --  work was rolled back to savepoint)  
    end  
   return (1)  
end  

sp_MSupdate_subscription (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSupdate_subscription(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @subscriber
, int @article_id
, int @status
, varbinary @subscription_seqno
, nvarchar @destination_db)

MetaData:

 CREATE PROCEDURE sys.sp_MSupdate_subscription  
(  
    @publisher sysname,  
    @publisher_db sysname,  
    @subscriber sysname,  
    @article_id int,  
    @status int,  
    @subscription_seqno varbinary(16),  
    -- post 6x  
    @destination_db sysname = '%'  
)  
as  
begin  
    set nocount on  
    declare @publisher_id smallint    
    declare @subscriber_id smallint  
    declare @automatic tinyint  
    declare @snapshot_seqno_flag bit  
    declare @virtual smallint   
    declare @virtual_anonymous smallint   
    declare @retcode int  
    declare @active tinyint  
    declare @subscribed tinyint  
    declare @agent_id int  
    declare @sync_type tinyint  
    declare @virtual_agent_id int  
    declare @publication_id int  
    declare @publisher_database_id int  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
      
    select @automatic = 1  
    select @virtual = - 1  
    select @virtual_anonymous = - 2  
    select @active = 2  
    select @subscribed = 1  
  
    --  Check if publisher is a defined as a distribution publisher in the current database  
    exec @retcode = sys.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT  
    if @retcode <> 0  
    begin  
        return(1)  
    end  
  
    --  Check if subscriber exists  
    if @subscriber is null  
    begin  
        select @subscriber_id = @virtual  
        select @destination_db = '%'  
    end  
    else  
        select @subscriber_id = srvid from master.dbo.sysservers, MSsubscriber_info where   
            UPPER(srvname) = UPPER(@subscriber) and  
            UPPER(subscriber) = UPPER(@subscriber) and  
            UPPER(publisher) = UPPER(@publisher)  
    if @subscriber_id is NULL  
    begin  
        raiserror (20032, 16, -1, @subscriber, @publisher)   
        return (1)  
    end  
  
    select @publisher_database_id = id  
      from dbo.MSpublisher_databases  
     where publisher_db = @publisher_db  
       and publisher_id = @publisher_id   
  
    begin tran  
    save transaction MSupdate_subscription  
  
    if @status = @active  
    begin  
        --  Activating the subscription  
        --   
        --   It will be used by:  
        --   1. no_sync subscriptions  
        --   2. subscriptions on immediate_sync pub that are activate   
        --   using virtual subscritpions's snapshots.  
        --   3. snapshot agents for 6.5 publishers  
         --   
        --  Get agent_id etc  
        select @agent_id = agent_id, @sync_type = sync_type,  
            @publication_id = publication_id  
            from dbo.MSsubscriptions where  
            publisher_id = @publisher_id and  
            publisher_database_id = @publisher_database_id and  
            article_id = @article_id and  
            subscriber_id = @subscriber_id and   
            --  Use equal so 6.x publisher will get nothing (since @destination_db is '%')  
            subscriber_db = @destination_db  
  
        --  If immediate_sync publication and sync type is auto_sync  
        --  Set the subscription_seqno and snapshot_seqno to be of the virtual subscription   
        --  for real subscription when activating the subscription.  
        --  We have to do it for the whole publication to prevent the  
        --  distribution agent from picking up partial snapshot transaction  
        if  @subscriber IS NOT NULL AND  
            @sync_type = @automatic and  
            exists (select * from dbo.MSpublications p where  
                --  publication_id is unique across dist db  
                p.publication_id = @publication_id and  
                p.immediate_sync = 1   
                )  
        begin  
            --  Get virtual agent_id  
            select @virtual_agent_id = agent_id from dbo.MSsubscriptions where  
                publisher_id = @publisher_id and  
                publisher_database_id = @publisher_database_id and  
                article_id = @article_id and  
                subscriber_id = @virtual  
  
            --  Note it is possible that the virtual subscriptions  
            --  were deactivated during clean up.  
            --  Update the subscription table for the whole publication  --   
            --  Note: You need to change sp_MSreset_subscription_seqno when you   
            --  change this query  
            update dbo.MSsubscriptions  set   
                snapshot_seqno_flag =    
                    (select subscription_seqno from dbo.MSsubscriptions rs2  
                        where  
                        rs2.agent_id = @virtual_agent_id and  
                        rs2.article_id = rs1.article_id),  
                status =      
                    (select status from dbo.MSsubscriptions rs2  
                        where  
                        rs2.agent_id = @virtual_agent_id and  
                        rs2.article_id = rs1.article_id),  
                --  Use current date rather than virtual sub date for the  
                --  calculation in cleanup   
                subscription_time = getdate(),  
                subscription_seqno =   
                    (select subscription_seqno from dbo.MSsubscriptions rs2  
                        where  
                        rs2.agent_id = @virtual_agent_id and  
                        rs2.article_id = rs1.article_id),  
                publisher_seqno =   
                    (select publisher_seqno from dbo.MSsubscriptions rs2  
                        where  
                        rs2.agent_id = @virtual_agent_id and  
                        rs2.article_id = rs1.article_id),  
                ss_cplt_seqno =   
                    (select ss_cplt_seqno from dbo.MSsubscriptions rs2  
                        where  
                        rs2.agent_id = @virtual_agent_id and  
                        rs2.article_id = rs1.article_id)  
                from dbo.MSsubscriptions rs1 where  
                    agent_id = @agent_id and  
                    sync_type = @automatic and  
                    status = @subscribed              
            if @@ERROR <> 0  
                goto UNDO  
        end  
        else  
        begin  
            update dbo.MSsubscriptions set status = @status, subscription_time = getdate(),   
                publisher_seqno = @subscription_seqno, ss_cplt_seqno = @subscription_seqno,  
                --  Have to do this. Refer to anonymous agent "no init sync" option logic above  
                --  and sp_MSset_snapshot_seqno.  
                snapshot_seqno_flag = 0  
              from dbo.MSsubscriptions with (index(iMSsubscriptions))   
                where  
                  publisher_id = @publisher_id and  
                  publisher_database_id = @publisher_database_id and  
                  article_id = @article_id and  
                  ((@subscriber_id <> @virtual and (subscriber_id = @subscriber_id and ((@destination_db = N'%') or (subscriber_db = @destination_db)) )) or  
                  --  Activate virtual_anonymous but NOT virtual  
                  --  This is for no init option for anonymous agent  
                  --  Refer to sp_addsubscription , sp_MSget_repl_cmds_anonymous  
                  --  and sp_MSset_snapshot_seqno  
                  (@subscriber_id =  @virtual and subscriber_id = @virtual_anonymous))  
            if @@error <> 0  
                goto UNDO  
          
            --  For 6.5 publishers.  
            --  Snapshot agents of 6.5 publishers will call sp_changesubstatus which will  
            --  RPC this stored procedure to activate the subscription. The RPC calls are  
            --  not in one transaction.  
            --  We have to do it for the whole publication to prevent the  
            --  distribution agent from picking up partial snapshot transaction  
  
            --  Get publication_id   
            --  The publication_id and sync type are set by SNAPSHOT agent   
            --  calling sp_MSset_snapshot_xact_seqno  
            --  Don't do it if @subscriber_id is virtual to prevent virtual sub  
            --  to be activated.  
            if @destination_db = '%' and @subscriber_id <> @virtual  
            begin  
                declare @publication_id_6x int  
                --  Get the publication_id.  
                --  Note that if the sync_type is not automatic, the publication_id  
                --  will be null. In this case, we will not do the later  
                --  update (we don't need to)  
                select top 1 @publication_id_6x = publication_id from dbo.MSsubscriptions   
                    where   publisher_id = @publisher_id and  
                            publisher_database_id = @publisher_database_id and  
                            subscriber_id = @subscriber_id and   
                            sync_type = @automatic and  
                            article_id = @article_id  
  
                if @publication_id_6x <> NULL  
                begin  
                    update dbo.MSsubscriptions set status = @status, subscription_time = getdate()   
                      from dbo.MSsubscriptions with (index(iMSsubscriptions))  
                        where  
                          publisher_id = @publisher_id and  
                          publisher_database_id = @publisher_database_id and  
                          subscriber_id = @subscriber_id and   
                          sync_type = @automatic and  
                          publication_id = @publication_id_6x and  
                          status <> @status  
                    if @@error <> 0  
                        goto UNDO  
                end  
            end  
        end  
    end --  End activating the subscription  
    else  
    begin --  Deactivating the subscription or change it from 'subscribed' to 'initiated'  
        --   
        --   If @status is @active, it will be used by:  
        --   sp_reinitsubscription at publisher to reset the subscription status to 'subscribed'  
        --   If @status is @initiated (2), it will be used by snapshot agent with   
        --   sp_MSactivate_auto_sub => sp_changesubstatus.  
         --   
        update dbo.MSsubscriptions  
           set status = @status,  
               publisher_seqno = @subscription_seqno,   
               ss_cplt_seqno = @subscription_seqno  
          from dbo.MSsubscriptions with (index(iMSsubscriptions))  
            where  
            publisher_id = @publisher_id and  
            publisher_database_id = @publisher_database_id and  
            article_id = @article_id and  
            ((@subscriber_id <> @virtual and (subscriber_id = @subscriber_id and ((@destination_db = N'%') or (subscriber_db = @destination_db)) )) or  
            --  Deactivating both virtual and virtual anonymous  
            (@subscriber_id =  @virtual and (subscriber_id = @virtual or (subscriber_id = @virtual_anonymous and  
            --  When changing to @initiated, do not change activated virtual_anonymous_subscription.  
            (@status = @subscribed or snapshot_seqno_flag = 0)))))  
  
        if @@error <> 0  
            goto UNDO  
  
        --  Set the reset_partial_snapshot_progress bit if we are reinitializing  
        if @status = @subscribed  
        begin  
            update dbo.MSdistribution_agents  
               set reset_partial_snapshot_progress = 1  
              from dbo.MSdistribution_agents da  
        inner join dbo.MSsubscriptions sub  
                on (da.id = sub.agent_id  
                or da.virtual_agent_id = sub.agent_id  
                or da.anonymous_agent_id = sub.agent_id)  
             where  
                sub.publisher_id = @publisher_id and  
                sub.publisher_database_id = @publisher_database_id and  
                sub.article_id = @article_id and  
                ((@subscriber_id <> @virtual and (sub.subscriber_id = @subscriber_id and ((@destination_db = N'%') or (sub.subscriber_db = @destination_db)) )) or  
                --  Deactivating both virtual and virtual anonymous  
                (@subscriber_id =  @virtual and (sub.subscriber_id = @virtual or (sub.subscriber_id = @virtual_anonymous and  
                --  When changing to @initiated, do not change activated virtual_anonymous_subscription.  
                (@status = @subscribed or sub.snapshot_seqno_flag = 0))))) and  
                --  Don't update virtual agents  
                da.subscriber_id not in (@virtual, @virtual_anonymous)  
            if @@error <> 0  
                goto UNDO  
        end  
    end  
      
    commit transaction  
    return (0)  
  
UNDO:  
    if @@TRANCOUNT > 0  
    begin  
        ROLLBACK TRAN MSupdate_subscription  
        COMMIT TRAN  
    end  
    return(1)  
end