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