Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdrop_distribution_agent

  No additional text.


Syntax
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

 
Last revision 2008RTM
See also

  sp_dropanonymoussubscription (Procedure)
sp_instdist (Procedure)
sp_MSadd_distribution_agent (Procedure)
sp_MScleanup_agent_entry (Procedure)
sp_MSdrop_anonymous_entry (Procedure)
sp_MSdrop_distribution_agentid (Procedure)
sp_MSdrop_distribution_agentid_dbowner_proxy (Procedure)
sp_MSdrop_subscription (Procedure)
sp_MSforce_drop_distribution_jobs (Procedure)
sp_MSrepl_addpushsubscription_agent (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