sp_MShelp_distribution_agentid (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_MShelp_distribution_agentid(smallint @publisher_id
, nvarchar @publisher_db
, nvarchar @publication
, smallint @subscriber_id
, nvarchar @subscriber_db
, int @subscription_type
, nvarchar @subscriber_name
, uniqueidentifier @anonymous_subid
, bit @reinitanon)

MetaData:

 CREATE PROCEDURE sys.sp_MShelp_distribution_agentid  
(  
    @publisher_id smallint,  
    @publisher_db sysname,  
    @publication sysname = NULL,  
    @subscriber_id smallint,  
    @subscriber_db sysname,  
    @subscription_type int, --  0 = push 1 = pull 2=anonymous,  --   
    --  For anonymous only  
    @subscriber_name    sysname = NULL,  
    @anonymous_subid    uniqueidentifier = NULL,  
    @reinitanon         bit = 0  
)  
as  
begin  
    set nocount on  
  
    declare @independent_agent bit  
    declare @xact_seqno_length int  
    declare @agent_id int  
    declare @third_party_flag bit  
    declare @retcode int  
    declare @anonymous int  
    declare @sub_agent_id int  
    declare @allow_subscription_copy bit  
    declare @immediate_sync bit  
    declare @endraiserror bit  
  
    select @anonymous         = 2  
    select @endraiserror     = 0  
      
    if @publication is null  
        select @independent_agent = 0  
    else  
        select @independent_agent = 1  
  
    select  top 1  
        @third_party_flag = thirdparty_flag,  
        @allow_subscription_copy = allow_subscription_copy,  
        @immediate_sync = immediate_sync  
        from dbo.MSpublications where  
        publisher_id = @publisher_id and  
        publisher_db = @publisher_db and  
        publication = @publication or   
        (@publication is null and independent_agent = 0)  
  
    --  Get agent id  
    if @subscription_type = @anonymous  
    begin  
        exec @retcode = sys.sp_MSadd_anonymous_agent  
            @publisher_id   = @publisher_id,  
            @publisher_db   = @publisher_db,  
            @publication    = @publication,  
            @subscriber_db  = @subscriber_db,  
            @subscriber_name = @subscriber_name,  
            @anonymous_subid =  @anonymous_subid output,  
            @agent_id = @agent_id output,  
            @reinitanon = @reinitanon  
        if @@error <> 0 or @retcode <> 0  
            return (1)  
  
        --  Refer to sp_MSget_subscription_guid  
        select @sub_agent_id = virtual_agent_id from  
            MSdistribution_agents where  
            id = @agent_id  
    end   
    else  
    begin  
        select @agent_id = id  
        from MSdistribution_agents where  
             publisher_id = @publisher_id and  
             publisher_db = @publisher_db and  
             (publication = @publication or  
             (@publication is null and publication = N'ALL')) and  
             subscription_type = @subscription_type and  
             subscriber_id = @subscriber_id and  
             subscriber_db = @subscriber_db  
  
        --  If cannont find the agent entry, ignore @subscriber_db and try again   
        --  for non SQL subscribers  
        --  Note that this preserved backward compatibility for 7.0 publisher, 7.0 pull distribution agent or 6.5 pub.  
        --  In 7.0, we hard code name 'DSN'.  
        --  In 6.5 pub, the db name is real database name.  
        --  In 8.0, distribution agent sends in unlocalized '(default destination)' as default db name for   
        --  non SQL subscribers..  
        if @agent_id is null   
        begin  
            declare @publisher sysname  
            declare @subscriber sysname  
            select @publisher = srvname from master.dbo.sysservers where  
                srvid = @publisher_id  
            select @subscriber = srvname from master.dbo.sysservers where  
                srvid = @subscriber_id  
            if exists (select * from MSsubscriber_info where  
                publisher = @publisher and  
                subscriber = @subscriber and  
                type <> 0)  
            begin  
                select @agent_id = id  
                from MSdistribution_agents where  
                     publisher_id = @publisher_id and  
                     publisher_db = @publisher_db and  
                     (publication = @publication or  
                     (@publication is null and publication = N'ALL')) and  
                     subscription_type = @subscription_type and  
                     subscriber_id = @subscriber_id   
            end  
        end  
  
        select @sub_agent_id = @agent_id  
    end  
  
    --  If the agent id is still null here we have a problem and must either   
    --  raiserror and exit or perform more processing and do a deferred raiserror  
    if @agent_id is null  
    begin  
        --  If cannot find the publication and the agent, raise  
        --   error saying invalid publication.  
        --    
        --  Note:   
        --  We can not fail if publication not exists but agent   
        --  exists It is an upgrade case.   
        if @third_party_flag is null  
        begin  
            --  The publication(s) does not exist just exit completely  
            RAISERROR (21073, 16, -1)  
            return(1)  
        end  
          
        --  Set flag to perform the deferred raiseerror at the end of the proc  
        select @endraiserror = 1  
  
        --  Attempt to retrieve the agent id for the invalid subscription or the   
        --  subscription whose publication does not allow independednt agents  
        --  this will allow our agents to continue on and log history information  
        select top 1 @agent_id = agent_id  
            from dbo.MSpublications mp,  
                    MSsubscriptions ms  
            where mp.publication                = @publication      
                    and mp.publisher_id         = @publisher_id   
                    and    mp.publisher_db         = @publisher_db  
                    and ms.publisher_id         = mp.publisher_id  
                    and    ms.publisher_db         = mp.publisher_db  
                    and ms.subscription_type     = @subscription_type  
                    and ms.subscriber_id         = @subscriber_id   
                    and ms.subscriber_db         = @subscriber_db  
  
        --  without the agent id we can not continue but must still raiserror  
        if @agent_id is null  
        begin  
            goto EndRaiseError  
        end  
  
        select @sub_agent_id = @agent_id  
    end  
  
    --  Reset null properties  
    --  It is an upgrade case.  
    if @third_party_flag is null   
        select @third_party_flag = 0  
    if @allow_subscription_copy is null  
        select @allow_subscription_copy = 0  
  
    --   
    --  Get the time when the subscription is active and succeed.  
     --   
    --   
    select Top 1 @last_status=runstatus, @last_sync = time from MSdistribution_history   
        where agent_id = @agent_id order by timestamp DESC  
      
    if @last_status = 6 and EXISTS (select * from MSdistribution_history where agent_id = @agent_id and runstatus = @success)   
                select Top 1 @last_sync = time from MSdistribution_history where agent_id = @agent_id and runstatus = @success  
                        order by timestamp DESC      
  
    if @last_sync is not NULL and @independent_agent = 1 -- by pass the retention check for non-independent agnt  
    begin  
        if (@last_sync < dateadd(hour, -@retention, getdate()))  
           and (@retention <> 0)  
                 select @expired = 1   
    end  
     --   
 --    
    --  Avoid returning a NULL value   
    --  Otherwise, distribution agent may fail  
     --   
    select @xact_seqno_length = 0  
  
    --    
    --   Get the lengh of xact_seqno  
    --    Currently, unique across the publisher  
     --   
    select top 1 @xact_seqno_length = DATALENGTH(subscription_seqno)  
    from dbo.MSsubscriptions s where  
        agent_id = @sub_agent_id  
          
    --  xact_seqno for snapshot trans are longer for native publishers --   
    if @third_party_flag = 0  
    begin  
        select @xact_seqno_length = 14  
    end  
  
    --  Security check. Do it here to let the agent fail at the beginning  
    exec @retcode = sys.sp_MScheck_pull_access  
        @agent_id = @sub_agent_id,  
        @agent_type = 0 --  distribution agent  
    if @@error <> 0 or @retcode <> 0  
        return (1)  
      
    --  Get update_mode  
    declare @update_mode int  
      
    --  Use max because:  
    --  One agent can have mixed read only (0) and synctran (1) subscriptions.   
    --  The update mode value  
    --  is used in subscriber triggers. It is ok to set update mode to synctran  
    --  in mixed case because the triggers will not be create for read only.  
    --  Queued mode require independent agent.  
    select @update_mode = max(update_mode) from dbo.MSsubscriptions where  
        agent_id = @agent_id  
      
    --  For anonymous agents, update_mode is read only.  
    if @update_mode is null  
        set @update_mode = 0  
  
    --  Get attach_version guid  
    declare @attach_version binary(16)  
    if @allow_subscription_copy <> 0  
    begin  
        declare @publication_id int  
        declare @virtual_agent_id int  
        declare @virtual smallint  
        set @virtual = -1  
  
        --  Get publication_id  
        select @publication_id = publication_id  
            from dbo.MSpublications where  
            publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication = @publication   
  
        if @publication_id is null  
        begin  
            RAISERROR (21040, 16, -1, @publication)  
            return 1  
        end  
  
        --  Get version agent_id  
        select top 1 @virtual_agent_id = agent_id from dbo.MSsubscriptions where  
            publisher_id = @publisher_id and  
            publisher_db = @publisher_db and  
            publication_id = @publication_id and  
            subscriber_id = @virtual  
  
        select @attach_version = subscription_guid from MSdistribution_agents  
            where id = @virtual_agent_id  
    end  
    else  
        --  set a irrelevent guid. It should never be used.  
        select @attach_version = newid()  
  
    --  Get subscription guid  
    --  Use sub_agent_id so that both anonymous and well-known work.  
    --  Refer to sp_MSget_subscription_guid. You need to make change to that sp when changing  
    --  this.  
    declare @subscription_guid binary(16)  
    select @subscription_guid = subscription_guid from MSdistribution_agents where  
        id = @sub_agent_id  
      
    select 'xact_seqno_length' = @xact_seqno_length,  
            'agent_id' = @agent_id,  
            'agent_name' = name,  
            'anonymous subid' = anonymous_subid,  
            'expired ' = convert(int, 0),  
            'dts_package_name' = dts_package_name,  
            'dts_package_password' = sys.fn_repldecryptver4(dts_package_password),  
            'dts_package_location' = dts_package_location,  
            'immediate_sync' = @immediate_sync,  
            'allow_subscription_copy' = @allow_subscription_copy,  
            'queue_id' = queue_id,  
            'update_mode' = @update_mode,  
            'attach_version' = @attach_version,  
            'subscription_guid' = @subscription_guid,  
            'queue_server' = queue_server,  
            'reset_partial_snapshot_progress' = reset_partial_snapshot_progress  
            ,subscriptionstreams  
        from MSdistribution_agents where id = @agent_id  
    if @@error <> 0 return 1  
      
EndRaiseError:  
    if @endraiserror = 1  
    begin  
        --  Raise a special error for a common error case: user specified publication  
        --  name for non independent agent publication.  
        --  If the specified publication name is in dbo.MSpublications table then  
        --  the publication is configured to use a non-independent distribution  
        --  agent. Raise a different error if this is the case  
        if @publication is not null and   
            exists (select * from dbo.MSpublications   
                    where publication = @publication and   
                          publisher_id = @publisher_id and  
                          publisher_db = @publisher_db and  
                          independent_agent = 0)    
        begin  
            RAISERROR (21133, 16, -1, @publication)  
        end  
        --  If anything else then we know the subscription is bad  
        else  
        begin  
             --  Invalid subscription  
            RAISERROR (21056, 16, -1, @publication)  
        end  
      
        return (1)  
    end  
  
    return (0)  
end