sp_MSdrop_distribution_agent (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_MSdrop_distribution_agent(smallint @publisher_id
, nvarchar @publisher_db
, nvarchar @publication
, smallint @subscriber_id
, nvarchar @subscriber_db
, int @subscription_type
, bit @keep_for_last_run
, bit @job_only)

MetaData:

 CREATE PROCEDURE sys.sp_MSdrop_distribution_agent   
(  
    @publisher_id smallint,  
    @publisher_db sysname,  
    @publication sysname,  
    @subscriber_id smallint,  
    @subscriber_db sysname,  
    @subscription_type int,  
    @keep_for_last_run bit = 0,  
    @job_only bit = 0  
)   
AS  
BEGIN  
  
    SET NOCOUNT ON  
  
    --   
    --  Declarations.  
     --   
    DECLARE @stopcode       int  
            ,@retcode        int  
            ,@job_id         binary(16)  
            ,@job_step_uid     uniqueidentifier  
            ,@is_continuous  bit  
            ,@local_job      bit  
            ,@publisher      sysname  
            ,@schedule_name  sysname  
            ,@job_command    nvarchar(512)  
            ,@name           nvarchar(100)  
            ,@agent_id       int  
            ,@queue_id      sysname  
            ,@qservicestatus int  
            ,@qservername   nvarchar(255)  
            ,@subscriber    sysname  
  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
              
    select @stopcode = 1  
            ,@qservername = queue_server  
            ,@job_id = job_id, @local_job = local_job, @name = name, @agent_id = id,  
            @job_step_uid = job_step_uid,  
            @queue_id = queue_id  
    FROM MSdistribution_agents WHERE  
        publisher_id = @publisher_id AND  
        publisher_db = @publisher_db AND  
        publication = @publication and  
        subscriber_id = @subscriber_id and  
        subscriber_db = @subscriber_db and  
        subscription_type = @subscription_type  
      
    --  Delete Perfmon instance  
    dbcc deleteinstance ("SQL Replication Distribution", @name)  
  
    select @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id  
  
    select @subscriber = srvname from master..sysservers where srvid = @subscriber_id  
    --  Return if not exists  
    IF @local_job IS NULL  
        RETURN(0)  
  
    BEGIN TRAN  
  
    if @queue_id is not null  
        and @queue_id != N'mssqlqueue'  
        and @job_only = 0  
    begin  
        --   
        --  MQ specific processing  
        --             
        exec @retcode = sys.sp_MSdropmqforsubscription @qservername, @queue_id  
        IF @retcode != 0  
            GOTO UNDO  
    end  
  
    IF @local_job = 1 and @keep_for_last_run = 0  
    BEGIN  
        IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @job_id)  
        BEGIN  
            --  Checks if the job name matches one that is generated   
            --  by replication  
            EXEC @retcode = sys.sp_MSisdistributionjobnamegenerated  
                                @publisher = @publisher,  
                                @publisher_db = @publisher_db,  
                                @publication = @publication,  
                                @subscriber = @subscriber,  
                                @job_id = @job_id  
            IF @@ERROR <> 0  
                GOTO UNDO  
              
            --  Only drop the job if the name was generated  
            IF @retcode = 0  
            BEGIN  
                EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @job_id,  
                                                        @job_step_uid = @job_step_uid  
                IF @@ERROR <> 0 or @retcode <> 0  
                    GOTO UNDO  
            END  
        END  
    END  
  
    IF @local_job = 1 and @keep_for_last_run = 1  
    BEGIN  
            select @job_command=command from msdb.dbo.sysjobsteps where job_id=@job_id and step_id=2  
  
            if PATINDEX('%-[Cc][Oo][Nn][Tt][Ii][Nn][Uu][Oo][Uu][Ss]%', @job_command) > 0  
                begin  
                    select @is_continuous = 1  
                    create table #sqlstatus(status nvarchar(20))  
                    insert into #sqlstatus (status) exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent'  
                    if exists (select * from #sqlstatus where status='Running.')   
                            exec @stopcode = msdb.dbo.sp_stop_job @job_id = @job_id  
                                 if @@ERROR<>0 GOTO UNDO  
                    drop table #sqlstatus     
                    if @stopcode=0   
                        waitfor delay '00:00:30'  
                end  
              
            EXEC @retcode = msdb.dbo.sp_update_job @job_id=@job_id, @delete_level=3 --  NOTE: Run once, success or failure!  
            IF @@ERROR <> 0 or @retcode <> 0  
                GOTO UNDO  
          
            EXEC @retcode = msdb.dbo.sp_delete_jobstep @job_id=@job_id, @step_id=3  
            IF @@ERROR <> 0 or @retcode <> 0  
                GOTO UNDO  
            EXEC @retcode = msdb.dbo.sp_delete_jobstep @job_id=@job_id, @step_id=1  
            IF @@ERROR <> 0 or @retcode <> 0  
                GOTO UNDO  
  
            select @job_command=command from msdb.dbo.sysjobsteps where job_id=@job_id and step_id=1          
            select @job_command = @job_command + ' -UnSubscribe 0 '  --  currently the value does not really matter  
              
            EXEC @retcode = msdb.dbo.sp_update_jobstep @job_id=@job_id, @step_id=1,   
                                @on_success_action=1,   
                                @on_fail_action=2,   
                                @command=@job_command  
            IF @@ERROR <> 0 or @retcode <> 0  
                GOTO UNDO  
  
              
            select @schedule_name = formatmessage(20532)  
            EXEC @retcode = msdb.dbo.sp_update_jobschedule @job_id=@job_id, @name=@schedule_name, @freq_subday_type = 2, @freq_subday_interval=30  
            IF @@ERROR<>0 or @retcode<>0  
                GOTO UNDO  
              
            if (@is_continuous  = 1) and (@stopcode = 0)  
                begin  
                    EXEC @retcode = msdb.dbo.sp_start_job @job_id=@job_id  
                     if @@ERROR<>0   
                        GOTO UNDO  
                end  
            --   
            --  The last run of this job will be as scheduled  
             --   
    END  
  
   --  In case this was a PeerToPeer agent, delete all the cached PeerToPeer info  
    DELETE FROM MScached_peer_lsns WHERE agent_id=@agent_id  
       IF @@ERROR <> 0   
        GOTO UNDO  
  
    IF @job_only = 0  
    BEGIN  
        --  Remove agent entry  
        DELETE MSdistribution_agents WHERE id = @agent_id  
        IF @@ERROR <> 0   
            GOTO UNDO  
    END  
      
    --  Remove associated history   
    DELETE MSdistribution_history WHERE agent_id = @agent_id  
    IF @@ERROR <> 0   
        GOTO UNDO  
      
    COMMIT TRAN  
  
    RETURN(0)  
  
UNDO:  
    if @@TRANCOUNT = 1  
        ROLLBACK TRAN  
    else  
        COMMIT TRAN  
    return(1)  
END