Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSdrop_snapshot_agent

  No additional text.


Syntax
create procedure sys.sp_MSdrop_snapshot_agent
(
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname
)
AS
begin

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode    int
    DECLARE @job_id     binary(16)
    DECLARE @job_step_uid uniqueidentifier
    DECLARE @local_job  bit
    DECLARE @publisher_id smallint
    DECLARE @name       nvarchar(100)
    DECLARE @agent_id   int
    declare @no_of_agents int

    
    -- security check
    -- only db_owner can execute this
    
    if (is_member ('db_owner') != 1)
    begin
        raiserror(14260, 16, -1)
        return (1)
    end
    
    -- security check
    -- Has to be executed from distribution database
    
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
    begin
        raiserror(21482, 16, -1, 'sp_MSdrop_snapshot_agent', 'distribution')
        return (1)
    end

    /*
    ** Initializations
    */
    select @publisher_id = srvid from master.dbo.sysservers where
        UPPER(srvname) = UPPER(@publisher)

    -- if this is a merge publiction which is dynamically filtered and we have
    -- dynamic snapshot jobs we should delete the dynamic snapshot jobs as well
    -- for this publication
    select @no_of_agents = count(*) from MSsnapshot_agents WHERE
        publisher_id = @publisher_id AND
        publisher_db = @publisher_db AND
        publication = @publication

    if @no_of_agents = 1
    begin
        SELECT @job_id = job_id, @job_step_uid = job_step_uid, @local_job = local_job, @name = name, @agent_id = id  FROM MSsnapshot_agents WHERE
            publisher_id = @publisher_id AND
            publisher_db = @publisher_db AND
            publication = @publication

        -- Delete Perfmon instance
        dbcc deleteinstance ("SQL Replication Snapshot", @name)

        -- Return if not exists
        IF @local_job IS NULL
            RETURN(0)

        BEGIN TRAN

        IF @local_job = 1
        BEGIN
            -- Don't drop the job for third party publications.
            if exists (select * from msdb..MSdistpublishers where
                UPPER(name) = UPPER(@publisher) and
                thirdparty_flag = 0)
            begin
                IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @job_id)
                BEGIN
                    -- Checks if job name was generated by replication
                    EXEC @retcode = sys.sp_MSissnapshotjobnamegenerated
                                        @publisher = @publisher,
                                        @publisher_db = @publisher_db,
                                        @publication = @publication,
                                        @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
        END

        DELETE MSsnapshot_agents WHERE id = @agent_id
        IF @@ERROR <> 0
            GOTO UNDO

        -- Remove history
        DELETE MSsnapshot_history WHERE agent_id = @agent_id
        IF @@ERROR <> 0
            GOTO UNDO

        COMMIT TRAN
    END
    ELSE
    BEGIN
        -- this case can only happen if this is a merge publication and we have regular and
        -- dynamic snapshot agents for it.
        BEGIN TRAN

        declare agents_cursor CURSOR LOCAL FAST_FORWARD FOR select job_id, job_step_uid, local_job, name, id from MSsnapshot_agents where
            publisher_id = @publisher_id and
            publisher_db = @publisher_db AND
            publication = @publication
        open agents_cursor
        fetch agents_cursor into @job_id, @job_step_uid, @local_job, @name, @agent_id
        while (@@fetch_status <> -1)
        begin
             -- Delete Perfmon instance
            dbcc deleteinstance ("SQL Replication Snapshot", @name)

            -- Return if not exists
            IF @local_job = 1
            begin
                -- ignore errors if the job does not exist
                if exists (select * from msdb.dbo.sysjobs where job_id = @job_id)
                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

            DELETE MSsnapshot_agents WHERE id = @agent_id
            IF @@ERROR <> 0
                GOTO UNDO

            -- Remove history
            DELETE MSsnapshot_history WHERE agent_id = @agent_id
            IF @@ERROR <> 0
                GOTO UNDO

            fetch agents_cursor into @job_id, @job_step_uid, @local_job, @name, @agent_id
        end
        close agents_cursor
        deallocate agents_cursor
        COMMIT TRAN
    END
    RETURN(0)

UNDO:
    if @@TRANCOUNT = 1
        ROLLBACK TRAN
    else
        COMMIT TRAN
    return(1)
end

 
Last revision 2008RTM
See also

  sp_instdist (Procedure)
sp_MSadd_snapshot_agent (Procedure)
sp_MSdropmergepub_snapshot (Procedure)
sp_MSdrop_publication (Procedure)
sp_MSforce_drop_distribution_jobs (Procedure)
sp_MSrepl_droppublication (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