    create table #dist_trans (subscriber_id smallint NOT NULL, undelivered_commands int NOT NULL, 
        delivered_commands int NOT NULL, delivery_rate float NOT NULL, 
        delivery_latency int NOT NULL)

    declare hc CURSOR LOCAL FAST_FORWARD FOR select distinct agent_id
        from MSsubscriptions
        where
        publisher_id = @publisher_id and
        subscriber_id >= 0 and
        status = 2
        for read only

    open hc
    fetch hc into  @agent_id
    while (@@fetch_status <> -1)
    begin
        -- Get the lastest numbers from distribution_history
        select  @xact_seqno = NULL

        -- Get latest seqno, rate and latency
        set rowcount 1
        select  @xact_seqno = xact_seqno,
--              @delivered_commands = delivered_commands,
                @delivery_rate = current_delivery_rate,
                @delivery_latency = current_delivery_latency
            -- from MSdistribution_history  (READPAST)
            from MSdistribution_history 
            where
            agent_id = @agent_id and
            xact_seqno <> 0x0
            order by timestamp DESC
        set rowcount 0

        if @xact_seqno IS NULL
            select  @xact_seqno = 0x00,
                    @delivery_rate = 0,
                    @delivery_latency = 0


        set rowcount 1
        select @subscriber_id = subscriber_id FROM MSsubscriptions
            where agent_id = @agent_id
        set rowcount 0
        
        -- Get the delivered trans number
        select @delivered_commands = 0
        select @delivered_commands = isnull(count(*), 0)
            from
            -- MSrepl_commands rc (READPAST), MSsubscriptions s
            MSrepl_commands rc, MSsubscriptions s
            where
            /*
            ** Query from sp_MSget_repl_commands
            */
            s.agent_id = @agent_id and
            s.status = @active_status and
            rc.publisher_database_id = s.publisher_database_id and
--          rc.publisher_id = s.publisher_id and
--          rc.publisher_db = s.publisher_db and
            rc.xact_seqno <= @xact_seqno and
            rc.article_id = s.article_id and
            rc.partial_command = 0
            and
            ((rc.xact_seqno >= s.subscription_seqno and (rc.type & @snapshot_bit) <> @snapshot_bit) or
            rc.xact_seqno = s.subscription_seqno)

        -- Get the undelivered trans number
        select @undelivered_commands = 0
        select @undelivered_commands = isnull(count(*), 0)
            from
            -- MSrepl_commands rc (READPAST), MSsubscriptions s
            MSrepl_commands rc , MSsubscriptions s
            where
            /*
            ** Query from sp_MSget_repl_commands
            */
            s.agent_id = @agent_id and
            s.status = @active_status and
            rc.publisher_database_id = s.publisher_database_id and
            rc.xact_seqno > @xact_seqno and
            rc.article_id = s.article_id and
            rc.partial_command = 0
            and
            ((rc.xact_seqno >= s.subscription_seqno and (rc.type & @snapshot_bit) <> @snapshot_bit) or
            rc.xact_seqno = s.subscription_seqno)

        insert into #dist_trans values (@subscriber_id, @undelivered_commands,
            @delivered_commands, @delivery_rate, @delivery_latency)
        
        fetch hc into  @agent_id
    end

    close hc
    deallocate hc

    select 'subscriber' = srvname, 
            'delivered commands' = sum(delivered_commands),
            'undelivered_commands' = sum(undelivered_commands),
            'delivery_rate' = sum(delivery_rate),
            'delivery_latency' = (select isnull(avg(delivery_latency), 0) from #dist_trans, master.dbo.sysservers where
                srvname = s1.srvname and delivery_latency > 0)
        from #dist_trans, master.dbo.sysservers s1
        where subscriber_id = srvid
        group by srvname

    drop table #dist_trans
        
GO

raiserror(15339,-1,-1,'sp_MSremove_published_jobs')
go
CREATE PROCEDURE sp_MSremove_published_jobs
@server sysname,
@database sysname
AS
    -- 6.5 publisher and 7.0 publisher will call this
    -- publisher_database_id will be drop in sp_MSdrop_publication.
    return(0)
go

raiserror(15339,-1,-1,'sp_MSset_snapshot_xact_seqno')
go

CREATE PROCEDURE sp_MSset_snapshot_xact_seqno
@publisher_id int,
@publisher_db sysname,
@article_id int, 
@xact_seqno varbinary(16),
@reset bit = 0,         /* @reset = 1 is used for Scheduled Snapshot publications by snapshot */
@publication sysname = NULL,
@publisher_seqno varbinary(16) = 0x00
/* 
** Required for 6x publishers!
*/
AS

    DECLARE @virtual smallint     /* const: virtual subscriber id */
    DECLARE @virtual_anonymous smallint /* const: virtual anonymous subscriber id */
    DECLARE @old_xact_seqno varbinary(16)
    DECLARE @old_publisher_seqno varbinary(16)
    DECLARE @subscribed tinyint
    DECLARE @automatic tinyint
    DECLARE @old_snapshot_seqno_flag bit
    DECLARE @publication_id int

    SELECT @virtual = -1
    SELECT @virtual_anonymous = -2
    SELECT @subscribed = 1
    SELECT @automatic = 1

    -- 6.5 only!!! @publication is not null only if the publisher is 6.5 sever!
    -- Set the publication_id and sync_type in MSsubscriptions.
    -- It will be used in sp_MSupdate_subscriptions 
    IF @publication IS NOT NULL
    BEGIN
        -- Get the publication id
        SELECT @publication_id = publication_id FROM MSpublications
            WHERE   publisher_id = @publisher_id AND
                    publisher_db = @publisher_db AND    
                    publication  = @publication

        -- Set the pubid and the sync_type
        -- Avoid update rows with no change to reduce update locks.
        
        UPDATE MSsubscriptions SET publication_id = @publication_id
            WHERE   publisher_id = @publisher_id AND
                    publisher_db = @publisher_db AND    
                    article_id  = @article_id and
                    status = @subscribed and
                    publication_id <> @publication_id
        
        -- Have to do this to avoid no sync subs from 6.5 publisher being
        -- updated.
        UPDATE MSsubscriptions SET sync_type = @automatic
            WHERE   publisher_id = @publisher_id AND
                    publisher_db = @publisher_db AND    
                    article_id  = @article_id and
                    status = @subscribed and
                    sync_type <> @automatic
    END

    begin tran
    save TRANSACTION MSset_snapshot_xact_seqno

    /* 
    ** Set snapshot_xact_seqno for all new subscriptions,
    ** plus the virtual subscription or all subscriptions if @reset = 1
    ** Note virtual anonymous subscription will not be set
    ** (2 virtual subscriptions of anonymous publication will be activated
    ** immediately without snapshot
    ** 
    ** @reset = 1 is used for Scheduled Snapshot publications by snapshot
    */
    UPDATE MSsubscriptions SET subscription_seqno = @xact_seqno,
        publisher_seqno = @publisher_seqno,
        snapshot_seqno_flag = 1,
        subscription_time = getdate()
      WHERE 
         MSsubscriptions.publisher_id = @publisher_id and
         MSsubscriptions.publisher_db = @publisher_db and
         MSsubscriptions.article_id = @article_id and 
         /* virtual subscriptions are automatic sync type */
         MSsubscriptions.sync_type = @automatic and 
         (MSsubscriptions.status = @subscribed or 
         MSsubscriptions.subscriber_id = @virtual or
         -- Set for virtual anonymous account if snapshot_seqno_flag
         -- is 0.
         -- The virtual anonymous account is activated immediately at subscription
         -- time for no init option for anonymous agent.
         (MSsubscriptions.subscriber_id = @virtual_anonymous and 
         MSsubscriptions.snapshot_seqno_flag = 0) or
         @reset = 1) 

    IF @@ERROR <> 0
    BEGIN
        if @@trancount > 0
        begin
            ROLLBACK TRANSACTION MSset_snapshot_xact_seqno
            commit tran
        end
        RETURN (1)
    END

    COMMIT TRANSACTION
GO


raiserror(15339,-1,-1,'sp_MSdrop_article')
go
CREATE PROCEDURE sp_MSdrop_article
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@article sysname

as

    set nocount on

    declare @publisher_id smallint
    declare @publication_id int
    declare @article_id int
    declare @retcode int
    declare @thirdparty_flag bit
    declare @immediate_sync bit

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Make sure publication exists
    select @publication_id = publication_id, @thirdparty_flag = thirdparty_flag,
        @immediate_sync = immediate_sync
        from MSpublications where 
        publication = @publication and
        publisher_id = @publisher_id and 
        publisher_db = @publisher_db
    if @publication_id is NULL
    begin
        raiserror(20026, 16, -1, @publication)
        return (1)
    end

    -- Make sure article exists
    select @article_id = article_id from MSarticles where 
        publication_id = @publication_id and
        publisher_id = @publisher_id and 
        publisher_db = @publisher_db and 
        article = @article
    if @article_id is NULL
    begin
        if @thirdparty_flag = 1
        begin
            raiserror(20027, 16, -1, @article)
            return (1)
        end
        else
            return (0)
    end

    -- Check to make sure that there are no subscriptions on the article
    if exists (select * from MSsubscriptions where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication_id = @publication_id and
        article_id = @article_id and
        subscriber_id >= 0)             -- ignore virtual subscriptions
    begin
        raiserror(14046, 16, -1)
        return(1)
    end

    begin tran
    save tran MSdrop_article

    -- For third party publications drop immediate sync and anonymous virtual subscriptions
    -- SQL Server publications will do this via RPC calls to sp_MSadd_subscription
    if @thirdparty_flag = 1 and @immediate_sync = 1
    begin
        begin
            exec @retcode = dbo.sp_MSdrop_subscription
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @article_id = @article_id,
                @subscriber = NULL                  -- virtual subscription
            if @retcode <> 0 or @@error <> 0
            begin
                if @@trancount > 0
                begin
                    rollback tran MSdrop_article
                    commit tran
                end
                return (1)
            end
        end
    end

    delete from MSarticles where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication_id = @publication_id and
        article_id = @article_id
    if @@error <> 0
    begin
        if @@trancount > 0
        begin
            rollback tran MSdrop_article
            commit tran
        end
        return (1)
    end

    commit tran
go

raiserror(15339,-1,-1,'sp_MSdrop_snapshot_agent')
GO
CREATE PROCEDURE sp_MSdrop_snapshot_agent (
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname
) AS


    SET NOCOUNT ON

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

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



    SELECT @job_id = job_id, @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
        IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE job_id = @job_id)
        BEGIN
            EXEC @retcode = msdb.dbo.sp_delete_job @job_id = @job_id
            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

    IF @@ERROR <> 0 
        GOTO UNDO

    -- Update global replication status table
    EXEC dbo.sp_MSupdate_replication_status
        @publisher,
        @publisher_db,
        @publication,
        @agent_type = 1,
        @agent_name = @name,
        @status = -1    -- delete status

    COMMIT TRAN

    RETURN(0)

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


raiserror(15339,-1,-1,'sp_MSdrop_logreader_agent')
GO
CREATE PROCEDURE sp_MSdrop_logreader_agent (
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname  --Only used by 3rd party publisher
) AS


    SET NOCOUNT ON

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

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


    SELECT @job_id = job_id, @local_job = local_job, @name = name, @agent_id = id FROM MSlogreader_agents WHERE
        publisher_id = @publisher_id AND
        publisher_db = @publisher_db AND
        publication = @publication

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

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

    BEGIN TRAN

    IF @local_job = 1
    BEGIN
        IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE job_id = @job_id)
        BEGIN
            EXEC @retcode = msdb.dbo.sp_delete_job @job_id = @job_id
            IF @@ERROR <> 0 or @retcode <> 0
                GOTO UNDO
        END
    END

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

    -- Remove history
    DELETE MSlogreader_history WHERE
        agent_id = @agent_id

    IF @@ERROR <> 0 
        GOTO UNDO

    -- Update global replication status table
    EXEC dbo.sp_MSupdate_replication_status
        @publisher,
        @publisher_db,
        @publication,
        @agent_type = 2,
        @agent_name = @name,
        @status = -1    -- delete status

    COMMIT TRAN

    RETURN(0)

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

raiserror(15339,-1,-1,'sp_MSdrop_publication')
go
CREATE PROCEDURE sp_MSdrop_publication
@publisher sysname,
@publisher_db sysname,
@publication sysname
as

    set nocount on

    declare @publisher_id smallint
    declare @publication_id int
    declare @retcode int
    declare @article sysname
    declare @thirdparty_flag bit
    DECLARE @working_dir nvarchar(255)
    DECLARE @working_dir_drive nvarchar(255)
    DECLARE @pub_dir nvarchar(255)


    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Make sure publication exists
    select @publication_id = publication_id, @thirdparty_flag = thirdparty_flag 
        from MSpublications where publication = @publication and
        publisher_id = @publisher_id and publisher_db = @publisher_db
    if @publication_id is NULL
    begin
        -- We don't know whether or not it is a third party or not so we can not 
        -- return error.
        -- raiserror(20026, 16, -1, @publication)
        -- return (1)
        return (0)
    end

    -- Make sure that there are no subscriptions on the publication.
    if exists (select * from MSsubscriptions s, MSpublications p where 
        p.publisher_id = @publisher_id and
        p.publisher_db = @publisher_db and
        p.publication = @publication and
        s.publisher_id = @publisher_id and
        s.publisher_db = @publisher_db and
        s.publication_id = p.publication_id and
        s.subscriber_id >= 0)               -- ignore virtual subscriptions
    begin
        raiserror(14005, 16, -1)
        return(1)
    end

    SELECT @working_dir = working_directory FROM msdb..MSdistpublishers
        where UPPER(name) = UPPER(@publisher)

    IF @working_dir IS NOT NULL
    BEGIN
        /* 
        ** We have to convert UNC to drive, otherwise will get 'Access denied' error in xp_cmdshell
        */
        EXEC master.dbo.sp_MSunc_to_drive @unc_path = @working_dir, 
            @local_server = @@SERVERNAME, @local_path = @working_dir_drive OUTPUT

        /*
        ** Delete publication directory in the distributor's directory.
        */
        SELECT @pub_dir = 'rmdir /S /Q ' + 
                    @working_dir_drive + '\unc\' + 
                    @@SERVERNAME + '_' +
                    @publisher_db + '_' +
                    @publication 
        EXECUTE  master..xp_cmdshell @pub_dir, NO_OUTPUT
        -- Ignore retcode error since the directory may not exist
        IF @@ERROR <> 0 
            RETURN(1)
   
        SELECT @pub_dir = 'rmdir /S /Q ' + 
                    @working_dir + '\inet\' + 
                    @@SERVERNAME + '_' +
                    @publisher_db + '_' +
                    @publication 
        EXECUTE master..xp_cmdshell @pub_dir, NO_OUTPUT
        -- Ignore retcode error since the directory may not exist
        IF @@ERROR <> 0 
            RETURN(1)
    END

    begin tran
    save tran MSdrop_publication

    -- Delete all articles if a third party publication
    if @thirdparty_flag = 1 
    begin
        -- Delete all articles in the publication
        declare hCarticles CURSOR LOCAL FAST_FORWARD FOR select article from MSarticles where 
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication_id = 
                (select publication_id from MSpublications where 
                    publisher_id = @publisher_id and
                    publisher_db = @publisher_db and
                    publication = @publication)
        open hCarticles
        fetch hCarticles into @article
        while (@@fetch_status <> -1)
        begin
            exec @retcode = dbo.sp_MSdrop_article @publisher, @publisher_db, @publication, @article
            if @retcode != 0 or @@error != 0
            begin
                close hCarticles
                deallocate hCarticles
                if @@trancount > 0
                begin
                    rollback tran MSdrop_publication
                    commit tran
                end
                return (1)
            end
                
            fetch hCarticles into @article
        end
        close hCarticles
        deallocate hCarticles
    end

    delete from MSpublications where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @@error <> 0
    begin
        if @@trancount > 0
        begin
            rollback tran MSdrop_publication
            commit tran
        end
        raiserror (14006, 16, -1)
        return (1)
    end

    -- Drop snapshot agent
    exec @retcode = dbo.sp_MSdrop_snapshot_agent
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication
    if @@ERROR<> 0 or @retcode <> 0
    begin
        if @@trancount > 0
        begin
            rollback tran MSdrop_publication
            commit tran
        end
        return (1)
    end

    -- Drop the logreader agent if
    -- 1. thirdparty publisher OR 
    -- 2. no publication left in the publisher database
    if @thirdparty_flag = 1 OR
        not exists (select * from MSpublications where 
            publisher_id = @publisher_id and
            publisher_db = @publisher_db)
    begin
        exec @retcode = dbo.sp_MSdrop_logreader_agent
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication
        if @@ERROR<> 0 or @retcode <> 0
        begin
            if @@trancount > 0
            begin
                rollback tran MSdrop_publication
                commit tran
            end
            return (1)
        end
    end

    -- Delete anonymous agents
    delete MSdistribution_agents where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @@ERROR<> 0 or @retcode <> 0
    begin
        if @@trancount > 0
            rollback tran MSdrop_publication
        return (1)
    end

    delete MSmerge_agents where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @@ERROR<> 0 or @retcode <> 0
    begin
        if @@trancount > 0
            rollback tran MSdrop_publication
        return (1)
    end

    -- Cleanup publication access list table
    delete MSpublication_access where
        publication_id = @publication_id
    if @@ERROR<> 0 or @retcode <> 0
    begin
        if @@trancount > 0
            rollback tran MSdrop_publication
        return (1)
    end

    -- Remove publisher_id, publisher_db pair if no other publication is using it.  This is used
    -- to store a publisher_database_id in the MSrepl_transactions and MSrepl_commands table.
    if not exists (select * from MSpublications where publisher_id = @publisher_id and
        publisher_db = @publisher_db)
    begin
        declare @publisher_database_id int

        select @publisher_database_id = id from MSpublisher_databases where 
            publisher_id = @publisher_id and 
            publisher_db = @publisher_db

        delete from MSpublisher_databases where 
            publisher_id = @publisher_id and publisher_db = @publisher_db
        if @@error <> 0
        begin
            if @@trancount > 0
            begin
                rollback tran MSdrop_publication
                commit tran
            end
            return (1)
        end

        -- Cleaning up MSrepl_originators
        delete MSrepl_originators where
            publisher_database_id = @publisher_database_id
        if @@error <> 0
        begin
            if @@trancount > 0
                rollback tran MSdrop_publication
            return (1)
        end

    end

    commit tran
go
raiserror(15339,-1,-1,'sp_MSadd_snapshot_agent')
GO
CREATE PROCEDURE sp_MSadd_snapshot_agent (
    @name nvarchar(100) = NULL,
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,  
    @publication_type int = 0,              -- 0 Transactional 1 Snapshot 2 Merge
    @local_job bit,  
    @freqtype  int = 4 ,                  /* 4== Daily */
    @freqinterval int  = 1,             /* Every day */
    @freqsubtype int =  4,                 /* Sub interval = Minute */
    @freqsubinterval int = 5,              /* Every five minutes */
    @freqrelativeinterval int = 1, 
    @freqrecurrencefactor int = 0, 
    @activestartdate int = 0,             /* 12:00 am - 11:59 pm */
    @activeenddate int =99991231 ,         /* No start date */    
    @activestarttimeofday int = 0,         
    @activeendtimeofday int = 235959,     /* No end time */    
    @command nvarchar(4000) = NULL,
    @job_existing bit = 0,   -- for 6x publisher
    @snapshot_jobid binary(16) = NULL OUTPUT
) AS


    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode            int
    DECLARE @publisher_id       smallint
    DECLARE @profile_id         int
    DECLARE @snapshot_type      int
    DECLARE @databasename       sysname
    DECLARE @agent_id           int

    DECLARE @distributor_security_mode      int                     /* 0 standard; 1 integrated */
    DECLARE @distributor_login              sysname 
    DECLARE @distributor_password           sysname
    DECLARE @category_name      sysname
    DECLARE @platform_nt        binary

    /*
    ** Initializations
    */
    select @platform_nt = 0x1

    select @publisher_id = srvid from master..sysservers where
        UPPER(srvname) = UPPER(@publisher)

    -- Always use integrated security on winNT
    if (@platform_nt = platform() & @platform_nt)
        set @distributor_security_mode = 1
    else
    begin
        select  @distributor_security_mode = 0,
                @distributor_login  = login,
                @distributor_password = password
            from msdb..MSdistpublishers where UPPER(name) = UPPER(@@servername)
	end

    select @command = @command + ' -DistributorSecurityMode ' + 
        convert(nvarchar(10),@distributor_security_mode) +  ' ' 
    if @distributor_security_mode <> 1
    begin
        if @distributor_login is not NULL
            select @command = @command + '-DistributorLogin ' + @distributor_login + ' '
        if @distributor_password is not NULL
            select @command = @command + '-DistributorEncryptedPassword ' + quotename(@distributor_password) + ' '
    end

    BEGIN TRAN
    
    -- If creating locally, try to drop it first
    IF @local_job = 1 and @job_existing = 0
    begin
        EXEC dbo.sp_MSdrop_snapshot_agent 
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication  
        IF @@ERROR <> 0
            GOTO UNDO
    end

    /* Code for snapshot agent type in MSagent_profiles */
    SELECT @snapshot_type = 1

    -- Get the default profile ID for the snapshot agent type. If a third party publication
    -- no profile is used.
    if exists (select * from MSpublications where 
                publisher_id = @publisher_id and
                publisher_db = @publisher_db and
                publication = @publication and
                thirdparty_flag = 1)
    begin
        set @profile_id = 0
    end
    else
    begin
        SELECT @profile_id = profile_id FROM msdb..MSagent_profiles WHERE 
            agent_type = @snapshot_type and
            def_profile = 1
    end

    -- Get the job id if it already exists
    if @local_job = 1 and @job_existing = 1
    begin
        select @snapshot_jobid = job_id from msdb..sysjobs_view where 
            job_id = @snapshot_jobid and
            originating_server = '(local)'
        if @snapshot_jobid IS NULL
        begin
            -- Message from msdb.dbo.sp_verify_job_identifiers
            RAISERROR(14262, -1, -1, 'Job', @name)          
            GOTO UNDO
        end

        --Drop the agent entry if it already exists, and recreate
        delete from MSsnapshot_agents where job_id = @snapshot_jobid
    end

    /* 
    ** Insert row
    */
    INSERT INTO MSsnapshot_agents (name, publisher_id, publisher_db, publication, publication_type,
                            local_job, profile_id)
         VALUES ('',@publisher_id, @publisher_db, @publication, @publication_type, @local_job, @profile_id)
    IF @@ERROR <> 0
        GOTO UNDO

    set @agent_id = @@IDENTITY
        
    IF @name IS NULL
        SELECT @name = CONVERT(nvarchar(23),@publisher ) + '-' + CONVERT(nvarchar(23),@publisher_db) + '-' + 
                        CONVERT(nvarchar(23),@publication) + '-'  + CONVERT(nvarchar(3), @@IDENTITY)
    
    -- Add Perfmon instance
    dbcc addinstance ("SQL Replication Snapshot", @name)

    IF @local_job = 1 and @job_existing = 0
    BEGIN

    -- ********WORKAROUND********
        DECLARE @nullchar nchar(20)
        SELECT @nullchar = NULL
    -- ********WORKAROUND********

        set @databasename = db_name()
        -- Get Snapshot category name (assumes category_id = 15)
        select @category_name = name FROM msdb.dbo.syscategories where category_id = 15

        EXECUTE @retcode = dbo.sp_MSadd_repl_job 
            @name = @name, 
            @subsystem = 'Snapshot', 
            @server = @publisher, 
            @databasename = @databasename, 
            @enabled = 1, 
            @freqtype = @freqtype, 
            @freqinterval = @freqinterval, 
            @freqsubtype = @freqsubtype, 
            @freqsubinterval = @freqsubinterval, 
            @freqrelativeinterval = @freqrelativeinterval, 
            @freqrecurrencefactor = @freqrecurrencefactor, 
            @activestartdate = @activestartdate, 
            @activeenddate = @activeenddate, 
            @activestarttimeofday = @activestarttimeofday, 
            @activeendtimeofday = @activeendtimeofday, 
            @nextrundate = 0,
            @nextruntime = 0,
            @runpriority = 0,
            @emailoperatorname = @nullchar,
            @retryattempts = 10, 
            @retrydelay = 1, 
            @command = @command, 
            @loghistcompletionlevel = 0, 
            @emailcompletionlevel = 0, 
            @description = @nullchar,
            @tagadditionalinfo = @nullchar,
            @tagobjectid = 0, 
            @tagobjecttype = 0, 
            @category_name = @category_name,
            @failure_detection = 1,
            @agent_id = @agent_id,
            @job_id = @snapshot_jobid OUTPUT

  
       IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO
    END

/* Moved up
    -- Get the job id if it already exists
    if @local_job = 1 and @job_existing = 1 
    begin
        select @snapshot_jobid = job_id from msdb..sysjobs_view where 
            job_id = @snapshot_jobid and
            originating_server = '(local)'
        if @snapshot_jobid IS NULL
        begin
            -- Message from msdb.dbo.sp_verify_job_identifiers
            RAISERROR(14262, -1, -1, 'Job', @name)          
            GOTO UNDO
        end
    end
*/

    -- Generate a job GUID for remote agents. This will be used by the UI to uniquely
    -- identify rows returned by the enums
    if @local_job = 0
    begin
        set @snapshot_jobid = newid();
    end

    UPDATE MSsnapshot_agents SET name = @name,
        job_id = @snapshot_jobid WHERE
        id = @agent_id
    IF @@ERROR <> 0
        GOTO UNDO

    -- Update global replication status table
    EXEC dbo.sp_MSupdate_replication_status
        @publisher,
        @publisher_db,
        @publication,
		@publication_type,
        @agent_type = 1,
        @agent_name = @name,
        @status = 0     -- not running status

    COMMIT TRAN

    RETURN(0)

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

raiserror(15339,-1,-1,'sp_MSadd_logreader_agent')
GO
CREATE PROCEDURE sp_MSadd_logreader_agent (
    @name nvarchar(100) = NULL,
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,   --Only used by 3rd party publisher
    @local_job bit,
    @job_existing bit = 0,
    @job_id binary(16) = NULL
) AS


    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode            int
    DECLARE @agent_args         nvarchar(255)
    DECLARE @publisher_id       smallint
    DECLARE @profile_id         int
    DECLARE @logreader_type     int
    DECLARE @databasename       sysname
    DECLARE @agent_id           int
    DECLARE @distributor_security_mode      int                     /* 0 standard; 1 integrated */
    DECLARE @distributor_login              sysname 
    DECLARE @distributor_password           sysname
    DECLARE @category_name      sysname
    DECLARE @platform_nt binary

    /*
    ** Initializations
    */
    select @platform_nt = 0x1

    select @publisher_id = srvid from master..sysservers where
        UPPER(srvname) = UPPER(@publisher)
    
    -- Always use integrated security on winNT
    if (@platform_nt = platform() & @platform_nt)
        set @distributor_security_mode = 1
    else
    begin
        select  @distributor_security_mode = 0,
                @distributor_login  = login,
                @distributor_password = password
            from msdb..MSdistpublishers where UPPER(name) = UPPER(@@servername)
	end

    BEGIN TRAN

    -- If creating locally, try to drop it first
    IF @local_job = 1 and @job_existing = 0
    begin
        EXEC dbo.sp_MSdrop_logreader_agent 
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication  
        IF @@ERROR <> 0
            GOTO UNDO
    end

    /* Code for log reader agent type in MSagent_profiles */
    SELECT @logreader_type = 2

    -- Get the default profile ID for the logreader agent type. If a third party publication
    -- no profile is used.
    if exists (select * from MSpublications where 
                publisher_id = @publisher_id and
                publisher_db = @publisher_db and
                publication = @publication and
                thirdparty_flag = 1)
    begin
        set @profile_id = 0
    end
    else
    begin
        SELECT @profile_id = profile_id FROM msdb..MSagent_profiles WHERE 
            agent_type = @logreader_type and
            def_profile = 1
    end

    /* 
    ** Insert row
    */
    INSERT INTO MSlogreader_agents (name, publisher_id, publisher_db, publication, 
                    local_job, profile_id)
         VALUES ('',@publisher_id, @publisher_db, @publication, @local_job, @profile_id)
    IF @@ERROR <> 0
        GOTO UNDO

    set @agent_id = @@IDENTITY
        
    IF @name IS NULL
        SELECT @name = CONVERT(nvarchar(47),@publisher ) + '-' + CONVERT(nvarchar(47),@publisher_db) + '-' + CONVERT(nvarchar(47), @@IDENTITY)
    
    -- Add Perfmon instance
    dbcc addinstance ("SQL Replication Logreader", @name)

    IF @local_job = 1 and @job_existing = 0
    BEGIN            
        SELECT @agent_args = '-Publisher ' + QUOTENAME(@publisher)
        SELECT @agent_args = @agent_args + ' -PublisherDB ' + QUOTENAME(@publisher_db)
        SELECT @agent_args = @agent_args + ' -Distributor ' + QUOTENAME(@@SERVERNAME)

        select @agent_args = @agent_args + ' -DistributorSecurityMode ' + 
            convert(nvarchar(10),@distributor_security_mode) +  ' ' 
        if @distributor_security_mode <> 1
        begin
            if @distributor_login is not NULL
                select @agent_args = @agent_args + '-DistributorLogin ' + @distributor_login + ' '
            if @distributor_password is not NULL
                select @agent_args = @agent_args + '-DistributorEncryptedPassword ' + quotename(@distributor_password) + ' '
        end

--    *******WORKAROUND*******
        DECLARE @nullchar nchar(20)
        SELECT @nullchar = NULL
--    *******WORKAROUND*******

        set @databasename = db_name()
        -- Get Logreader category name (assumes category_id = 13)
        select @category_name = name FROM msdb.dbo.syscategories where category_id = 13

        EXECUTE @retcode = dbo.sp_MSadd_repl_job
        @name = @name,
        @subsystem = 'LogReader',
        @server = @publisher,
        @databasename = @databasename,
        @enabled = 1,
        @freqtype = 64,       /* Auto-Start */
        @freqinterval                   = 1,
        @freqsubtype                    = 1,
        @freqsubinterval                = 1,
        @freqrelativeinterval= 1,
        @freqrecurrencefactor   = 1,
        @activestartdate                = 0,
        @activeenddate                  = 0,
        @activestarttimeofday   = 0,
        @activeendtimeofday     = 0,
        @nextrundate                    = 12355,
        @nextruntime                    = 13423,
        @runpriority                    = 0,
        @emailoperatorname              = @nullchar,
        @retryattempts = 10,            
        @retrydelay = 1,    
        @command = @agent_args,
        @loghistcompletionlevel = 0,
        @category_name = @category_name,
        @failure_detection = 1,
        @agent_id = @agent_id,
        @job_id = @job_id OUTPUT

  
       IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO
    END

    if @local_job = 1 and @job_existing = 1 
    begin
        select @job_id = job_id from msdb..sysjobs_view where 
            job_id = @job_id and
            originating_server = '(local)'
        if @job_id IS NULL
        begin
            -- Message from msdb.dbo.sp_verify_job_identifiers
            RAISERROR(14262, -1, -1, 'Job', @name)          
            GOTO UNDO
        end
    end

    -- Generate a job GUID for remote agents. This will be used by the UI to uniquely
    -- identify rows returned by the enums
    if @local_job = 0
    begin
        set @job_id = newid();
    end

    UPDATE MSlogreader_agents SET name = @name,
        job_id = @job_id WHERE
        id = @agent_id
    IF @@ERROR <> 0
        GOTO UNDO

    -- Update global replication status table
    EXEC dbo.sp_MSupdate_replication_status
        @publisher,
        @publisher_db,
        @publication,
        @agent_type = 2,
        @agent_name = @name,
        @status = 0     -- not running status


    COMMIT TRAN

    RETURN(0)

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

raiserror(15339,-1,-1,'sp_MSadd_publication')
go
CREATE PROCEDURE sp_MSadd_publication
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@publication_id int = 0,                    -- BUG REMOVE
@publication_type int = 1,                  -- 0 = Transactional 1 = Snapshot  2 = Merge
@independent_agent bit = 0,
@immediate_sync bit = 0,
@allow_push bit = 1,
@allow_pull bit = 0,
@allow_anonymous bit = 0,
@snapshot_agent nvarchar(100) = NULL,
@logreader_agent nvarchar (100) = NULL,
@description nvarchar(255) = NULL,
@retention int =60,
@vendor_name nvarchar(100) = 'Microsoft SQL Server'
as

    set nocount on

    declare @thirdparty_flag bit                    -- 0 = SQL Server 1 = Third Party
    declare @publisher_id smallint
    declare @retcode int
    declare @platform_nt binary
    declare @platform_desktop int
    declare @agentname nvarchar(100)
    declare @max_distretention int

    select @platform_nt = 0x1
    select @platform_desktop = 0x100

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    EXEC @retcode = sp_helpdistributor @max_distretention = @max_distretention OUTPUT
    if @retcode <>0
    begin
    	return (1)
    end

	/*
	** This is to make sure that retention value is not larger than distribution database retention period for non-merge publications
	*/
	if @retention > @max_distretention and @publication_type<>2
	begin 
		RAISERROR(21085, 16, -1) 
		return (1)
	end

    -- Get third party flag
    select @thirdparty_flag = thirdparty_flag from msdb..MSdistpublishers
        where UPPER(name) = UPPER(@publisher)
    
    -- Parameter Check: @publication_type
    -- Make sure that the publication type is one of the following:
    -- 0  transactional
    -- 1  snapshot
    -- 2  merge
    if @publication_type not in (0,1,2)
    begin
        raiserror(20033, 16, -1)
        return (1)
    end

    -- disable tran publishing on Win9x and/or desktop sku
    if (@publication_type = 0) and (platform() & @platform_nt != @platform_nt)
    begin
        raiserror(21052, 16, -1)
        return (1)
    end

    if (@publication_type = 0) and (platform() & @platform_desktop = @platform_desktop)
    begin
        raiserror(21108, 16, -1)
        return (1)
    end

    -- Parameter Check: @immediate_sync
    -- The publication must support independent_agent to support immediate_sync
    if @immediate_sync = 1 and @independent_agent != 1
    begin
        raiserror(21022, 16, -1)
        return (1)
    end

    -- Parameter Check: @allow_anonymous
    -- The publication must support immediate_sync to support anonymous.
    if @allow_anonymous = 1 and @immediate_sync != 1
    begin
        raiserror(20011, 16, -1)
        return (1)
    end
    
    -- Make sure publication does not already exist
    if exists (select * from MSpublications where publication = @publication and
        publisher_id = @publisher_id and publisher_db = @publisher_db)
    begin
        if @thirdparty_flag = 1
        begin
            raiserror(14016, 16, -1, @publication)
            return (1)
        end
        else
        begin
            exec @retcode = dbo.sp_MSdrop_publication 
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication
            if @@error <> 0 or @retcode <> 0
                return (1)
        end
    end

    begin tran
    save tran MSadd_publication

    insert into MSpublications values (@publisher_id, @publisher_db, @publication, 
        @publication_type, @thirdparty_flag, @independent_agent, @immediate_sync, @allow_push,
        @allow_pull, @allow_anonymous, @description, @vendor_name, @retention)
    if @@error <> 0
        goto UNDO

    -- Enable the distribution cleanup agent if transactional or snapshot publicational
    if @publication_type = 0 or @publication_type = 1
    begin
		select @agentname = name from msdb..sysjobs j, msdb..sysjobsteps s where 
			j.job_id = s.job_id and
			j.category_id = 11 and
			s.database_name = db_name()

        exec @retcode = msdb.dbo.sp_update_job @job_name=@agentname, @enabled=1
        if @@error <> 0 or @retcode <> 0
            goto UNDO
    end

    -- Add snapshot and logreader agent
    -- If null is passed in, we know that the agent is created already. (For SQL server).
    -- If not null is passed in, add the agents without creating local jobs (For third party).

    if @snapshot_agent is not null
    begin
        exec @retcode = dbo.sp_MSadd_snapshot_agent
            @name = @snapshot_agent,
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication,
            @publication_type = @publication_type,
            @local_job = 0
        if @@error <> 0 or @retcode <> 0
            goto UNDO
    end

    if @logreader_agent is not null
    begin
        exec @retcode = dbo.sp_MSadd_logreader_agent
            @name = @logreader_agent,
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication,
            @local_job = 0
        if @@error <> 0 or @retcode <> 0
            goto UNDO
    end

    -- If publisher_id, publisher_db pair is not in MSpublisher_databases then add it.  This will be used
    -- to store a publisher_database_id in the MSrepl_transactions and MSrepl_commands table.
    if not exists (select * from MSpublisher_databases where publisher_id = @publisher_id and
        publisher_db = @publisher_db)
    begin
        insert into MSpublisher_databases (publisher_id, publisher_db) values (@publisher_id, @publisher_db)
        if @@error <> 0
            goto UNDO
    end

    commit tran

    return(0)

UNDO:
    if @@TRANCOUNT > 0
    begin
        ROLLBACK TRAN MSadd_publication
        COMMIT TRAN
    end
    return(1)
GO
 
/* 
**  History runstatus values defined in sqlrepl.h
**
**  Start       1
**  Succeed     2
**  Inprogress  3
**  Idle        4
**  Retry       5
**  Failure     6
*/
GO

raiserror(15339,-1,-1,'sp_MSrepl_raiserror')
go
create proc sp_MSrepl_raiserror
@agent sysname,
@agent_name nvarchar(100),
@status int,
@message nvarchar(255),
@subscriber sysname = NULL,
@publication sysname = NULL,
@article sysname = NULL
as
    if @status = 2      --Succeeded
        raiserror (14150, 10, -1, @agent, @agent_name, @message)
    else if @status = 5 --Retry Failure
        raiserror (14152, 10, -1, @agent, @agent_name, @message)
    else if @status = 6 --Failure
    begin
        raiserror (14151, 18, -1, @agent, @agent_name, @message)
    end
    else if @status = 7
    begin
        raiserror (20574, 10, -1, @subscriber, @article, @publication)
    end
    else if @status = 8
    begin
        raiserror (20575, 10, -1, @subscriber, @article, @publication)
    end
    else if @status = 9
    begin
        raiserror (14158, 10, -1, @agent, @agent_name, @message)
    end

go

raiserror(15339,-1,-1,'sp_MSget_new_errorid')
GO

CREATE PROCEDURE sp_MSget_new_errorid
@errorid int OUTPUT 
AS
    set nocount on

    SELECT @errorid = NULL

    BEGIN TRAN sp_MSget_new_errorid

    SET ROWCOUNT 1
    SELECT @errorid = id FROM MSrepl_errors (UPDLOCK PAGLOCK)
        ORDER BY id DESC
    SET ROWCOUNT 0

    IF @errorid IS NULL
        SELECT @errorid = 1
    ELSE
        SELECT @errorid = @errorid + 1

    INSERT INTO MSrepl_errors VALUES (@errorid, 
        GETDATE(), NULL, /* Error with type NULL is placeholder, refer to sp_MSget_repl_error */
        NULL, NULL, NULL, NULL)

    /* return an 0 error_id if failed to insert the row */
    IF @@ERROR <> 0
        SELECT @errorid = 0

    SELECT @errorid

    COMMIT TRAN
GO   

raiserror(15339,-1,-1,'sp_MSadd_snapshot_history')
go
CREATE PROCEDURE sp_MSadd_snapshot_history
@agent_id int,
@runstatus int, 
@comments nvarchar(255),
@delivered_transactions int = 0,    
@delivered_commands int = 0,        
@log_error bit = 0,
@perfmon_increment bit = 1,
@update_existing_row bit = 0,
@do_raiserror bit = 1
AS

    DECLARE @current_time datetime
    DECLARE @start_time datetime
    DECLARE @duration int
    DECLARE @delivery_rate float
    DECLARE @error_id int
    DECLARE @retcode int
    DECLARE @idle int
    DECLARE @succeed int
    DECLARE @startup int
    DECLARE @retry int
    DECLARE @failure int
    DECLARE @inprogress int
    DECLARE @lastrow_timestamp timestamp
    DECLARE @publisher sysname
    DECLARE @publisher_db sysname
    DECLARE @publication sysname
    DECLARE @agent_name nvarchar(100)
    DECLARE @perfmon_delivery_rate int

    /* 
    ** Status const defined in sqlrepl.h 
    */
    set @startup = 1
    set @succeed = 2
    set @inprogress = 3
    set @idle = 4
    set @retry = 5
    set @failure = 6


    SELECT @current_time = GETDATE()

    -- Get named information 
    select @publisher = srvname, @publisher_db = publisher_db, @publication = publication,
        @agent_name = name from master..sysservers, MSsnapshot_agents where
        id = @agent_id and
        publisher_id = srvid

    -- Update Perfmon counter
    if @perfmon_increment = 1
    begin
        if @runstatus = @startup
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Snapshot", 1)
        else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure)
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Snapshot", -1)
    end

    /* Get start_time for latest agent run */
    IF @runstatus <> 1  -- Start status
    BEGIN
        SELECT TOP 1 @start_time = start_time, @lastrow_timestamp = timestamp
            FROM MSsnapshot_history (rowlock)
            WHERE 
            agent_id = @agent_id
            ORDER BY timestamp DESC
    END
    ELSE
        SELECT @start_time = @current_time

    /* Calculate agent run duration */
    SELECT @duration = DATEDIFF(second, @start_time, @current_time) 

    /* Calculate delivery_rate */
    IF @duration <> 0 
       SELECT @delivery_rate = (@delivered_commands * 1.0)/@duration 
    ELSE
       SELECT @delivery_rate = 0

    -- Set Perfmon counters
    if @runstatus = @idle or @runstatus = @inprogress
    begin
        dbcc addinstance ("SQL Replication Snapshot", @agent_name)
        dbcc incrementinstance ("SQL Replication Snapshot", "Snapshot:Delivered Cmds/sec", @agent_name, @delivered_commands)
		dbcc incrementinstance ("SQL Replication Snapshot", "Snapshot:Delivered Trans/sec", @agent_name, @delivered_transactions)
    end

    /* 
    ** Set error id to 0 unless the user want to log errors associate with this 
    ** history message.
    */
    SELECT @error_id = 0
    IF @log_error = 1
        -- Ignore errors here. @error_id will be set to 0 in case of errors  
        EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT

    -- Insert idle record or update if history record is already 'idle'
    IF @runstatus = @idle or @update_existing_row = 1
    begin
        -- Attempt to update the last row if it is IDLE
        UPDATE MSsnapshot_history SET runstatus = @runstatus, time = @current_time, duration = @duration,
            comments = @comments,
            delivered_transactions = @delivered_transactions,
            delivered_commands = @delivered_commands,
            delivery_rate = @delivery_rate
            WHERE
            agent_id = @agent_id and
            timestamp = @lastrow_timestamp and
            runstatus = @runstatus

        -- Insert idle record if there is not one
        if @@ROWCOUNT = 0
        begin
            INSERT INTO MSsnapshot_history VALUES (@agent_id, @runstatus, @start_time, 
            @current_time, @duration, @comments, @delivered_transactions, @delivered_commands, 
            @delivery_rate, @error_id, NULL)
        end
    end
    else
    begin
        INSERT INTO MSsnapshot_history VALUES (@agent_id, @runstatus, @start_time, 
            @current_time, @duration, @comments, @delivered_transactions, @delivered_commands, 
            @delivery_rate, @error_id, NULL)
    end

    -- Update global replication agent status table
    exec dbo.sp_MSupdate_replication_status 
        @publisher, 
        @publisher_db,
        @publication,
        @agent_type = 1,
        @agent_name = @agent_name,
        @status = @runstatus

    -- Raise the appropriate error
	if @do_raiserror = 1
	    exec dbo.sp_MSrepl_raiserror 'Snapshot', @agent_name, @runstatus, @comments

    IF @@ERROR <> 0
       RETURN (1)
GO   


raiserror(15339,-1,-1,'sp_MSadd_logreader_history')
go
CREATE PROCEDURE sp_MSadd_logreader_history
@agent_id int,
@runstatus int,
@comments nvarchar(255),
@xact_seqno varbinary(16) = NULL,
@delivery_time int = 0,                 -- Current delivery time (milliseconds)
@delivered_transactions int = 0,        -- Running total of session
@delivered_commands int = 0,            -- Running total of session
@delivery_latency int = 0,              -- Current latency 
@log_error bit = 0,
@perfmon_increment bit = 1,
@update_existing_row bit = 0,
@do_raiserror bit = 1
AS

    DECLARE @current_time datetime
    DECLARE @start_time datetime
    DECLARE @duration int
    DECLARE @average_commands int
    DECLARE @delivery_rate float
    DECLARE @error_id int
    DECLARE @retcode int
    DECLARE @idle int
    DECLARE @succeed int
    DECLARE @startup int
    DECLARE @retry int
    DECLARE @inprogress int
    DECLARE @failure int
    DECLARE @lastrow_timestamp timestamp
    DECLARE @publisher sysname
    DECLARE @publisher_db sysname
    DECLARE @publication sysname
    DECLARE @agent_name nvarchar(100)
    DECLARE @last_delivered_commands int
    DECLARE @last_delivered_transactions int
    DECLARE @latest_delivered_commands int
    DECLARE @latest_delivered_transactions int
    DECLARE @latest_delivery_rate int
    DECLARE @last_delivery_rate int             -- int for perfmon
    DECLARE @last_delivery_latency int
    DECLARE @last_delivery_time int
    DECLARE @avg_delivery_rate float
    DECLARE @avg_delivery_latency int
    DECLARE @total_delivery_time int

    /* 
    ** Status const defined in sqlrepl.h 
    */
    set @startup = 1
    set @succeed = 2
    set @inprogress = 3
    set @idle = 4
    set @retry = 5
    set @failure = 6

    SELECT @current_time = GETDATE()

    -- Update Perfmon counter
    if @perfmon_increment = 1
    begin
        if @runstatus = @startup
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Logreader", 1)
        else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure)
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Logreader", -1)
    end

    /* Get start_time for latest agent run */
    IF @runstatus <> 1  -- Startup status
    BEGIN
        SELECT TOP 1 @start_time = start_time, 
            @lastrow_timestamp = timestamp, 
            @last_delivered_commands = isnull(delivered_commands, 0),
            @last_delivered_transactions = isnull(delivered_transactions, 0),
            @last_delivery_latency = isnull(delivery_latency, 0),
            @last_delivery_time = isnull(delivery_time, 0),
            @last_delivery_rate = isnull(delivery_rate, 0)
        FROM MSlogreader_history (rowlock)
            WHERE agent_id = @agent_id
                ORDER BY timestamp DESC
    END
    ELSE
    BEGIN
        SELECT @start_time = @current_time
        SET @last_delivered_commands = 0
        SET @last_delivered_transactions = 0
        SET @last_delivery_latency = 0
        SET @last_delivery_time = 0
        SET @last_delivery_rate = 0
        SET @last_delivery_latency = 0
    END

    /* Use the current time if no corresponding start_up message logged */
    IF @start_time is NULL
       SELECT @start_time = @current_time

    -- Calculate number of transactions in this history
    set @latest_delivered_commands = @delivered_commands - @last_delivered_commands

    -- Calculate number of commands in this history
    set @latest_delivered_transactions = @delivered_transactions - @last_delivered_transactions

    /* Calculate agent run duration */
    SELECT @duration = DATEDIFF(second, @start_time, @current_time) 

    -- Calculate total delivery_time 
    if @latest_delivered_commands <> 0      -- Work around for Logreader passing in @delivery_time on shutdown.
        SELECT @total_delivery_time = @delivery_time + @last_delivery_time
    else
        SELECT @total_delivery_time = @last_delivery_time

    -- Calculate average delivery_rate of the session
    IF @latest_delivered_commands <> 0 and @total_delivery_time <> 0
    BEGIN
        SELECT @avg_delivery_rate = (@delivered_commands * 1.0)/(@total_delivery_time/1000.0)

        -- Current history delivery rate
        if @delivery_time <> 0
            SELECT @latest_delivery_rate = (@latest_delivered_commands * 1.0)/(@delivery_time/1000.0)
        else 
            SELECT @latest_delivery_rate = 0
    END
    ELSE
    BEGIN
        SELECT @avg_delivery_rate = @last_delivery_rate
        SELECT @latest_delivery_rate = 0
    END

    -- Calculate the average delivery_latency of the session
    if @latest_delivered_commands <> 0      -- Work around for Logreader passing in @delivery_latency on shutdown.
    BEGIN
        IF @delivery_latency <> 0
            IF @last_delivery_latency <> 0
                SELECT @avg_delivery_latency = (@delivery_latency + @last_delivery_latency)/2
            ElSE
                SELECT @avg_delivery_latency = @delivery_latency
        ELSE
            SELECT @avg_delivery_latency = 0
    END
    ELSE
    BEGIN
        SELECT @avg_delivery_latency = @last_delivery_latency

        -- Ignore latency value if no commands
        SELECT @delivery_latency = 0    
    END

    /*
    ** Calculate average number of commands per transaction
    */
    IF @delivered_commands <> 0
       SELECT @average_commands = @delivered_commands/@delivered_transactions
    ELSE

       SELECT @average_commands = 0

    -- Set Perfmon counters
    select @agent_name = name from MSlogreader_agents where id = @agent_id
    if @runstatus = @idle or @runstatus = @inprogress
    begin
        dbcc addinstance ("SQL Replication Logreader", @agent_name)
        dbcc incrementinstance ("SQL Replication Logreader", "Logreader:Delivered Trans/sec", @agent_name, @latest_delivered_transactions)
        dbcc incrementinstance ("SQL Replication Logreader", "Logreader:Delivered Cmds/sec", @agent_name, @latest_delivered_commands)
        dbcc setinstance ("SQL Replication Logreader", "Logreader:Delivery Latency", @agent_name, @delivery_latency)
    end
    else
    begin
        dbcc addinstance ("SQL Replication Logreader", @agent_name)
        dbcc setinstance ("SQL Replication Logreader", "Logreader:Delivery Latency", @agent_name, 0)
    end

    /* 
    ** Set error id to 0 unless the user want to log errors associate with this 
    ** history message.
    */
    SELECT @error_id = 0
    IF @log_error = 1
        -- Ignore errors here. @error_id will be set to 0 in case of errors  
        EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT

    -- Insert idle record or update if history record is already 'idle'
    IF @runstatus = @idle or @update_existing_row = 1
    begin
        -- Attempt to update the last row if it is IDLE
        if (@runstatus = @idle)
        begin
            UPDATE MSlogreader_history SET runstatus = @runstatus, time = @current_time,
            duration = @duration,comments = @comments
            WHERE
            agent_id = @agent_id and
            timestamp = @lastrow_timestamp and
            ( runstatus = @runstatus or 
            (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) )
        end
        else
        begin
            UPDATE MSlogreader_history SET runstatus = @runstatus, start_time = @start_time, 
            time = @current_time,
            duration = @duration, comments = @comments,
            xact_seqno = @xact_seqno,
            delivery_time = @total_delivery_time,
            delivered_transactions = @delivered_transactions,
            delivered_commands = @delivered_commands,
            average_commands = @average_commands,
            delivery_rate = @avg_delivery_rate,
            delivery_latency = @avg_delivery_latency,
            error_id = @error_id
            WHERE
            agent_id = @agent_id and
            timestamp = @lastrow_timestamp and
            ( runstatus = @runstatus or 
            (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) )
        end

        -- Insert idle record if there is not one
        if @@ROWCOUNT = 0
        begin
            -- Use last values because nothing was done
            INSERT INTO MSlogreader_history VALUES (@agent_id, @runstatus, @start_time, @current_time, 
                @duration, @comments,  
                @xact_seqno, @last_delivery_time, @delivered_transactions, @delivered_commands,
                @average_commands, @avg_delivery_rate, @last_delivery_latency, @error_id, NULL)
        end
    end
    else
    begin
        INSERT INTO MSlogreader_history VALUES (@agent_id, @runstatus, @start_time, @current_time, 
            @duration, @comments, 
            @xact_seqno, @total_delivery_time, @delivered_transactions, @delivered_commands,
            @average_commands, @avg_delivery_rate, @avg_delivery_latency, @error_id, NULL)
    end

    -- Get named information 
    select @publisher = srvname, @publisher_db = publisher_db, @publication = publication,
        @agent_name = name from master..sysservers, MSlogreader_agents where
        id = @agent_id and
        publisher_id = srvid

    -- Update global replication agent status table
    exec dbo.sp_MSupdate_replication_status 
        @publisher, 
        @publisher_db,
        @publication = 'ALL',
        @agent_type = 2,
        @agent_name = @agent_name,
        @status = @runstatus

    -- Raise the appropriate error
	if @do_raiserror = 1
	    exec dbo.sp_MSrepl_raiserror 'LogReader', @agent_name, @runstatus, @comments

    IF @@ERROR <> 0
    BEGIN
       RETURN (1)
    END

GO      

raiserror(15339,-1,-1,'sp_MSadd_distribution_history')
go
CREATE PROCEDURE sp_MSadd_distribution_history
@agent_id int,
@runstatus int, 
@comments nvarchar(255),
@xact_seqno binary(16) = 0x00,      -- We use binary(16)to pad it out for the below compare
@delivered_transactions int = 0,        -- Running total for the session
@delivered_commands int = 0,            -- Running total for the session
@delivery_rate float = 0,               -- Last rate (cmds/sec)
@log_error bit = 0,
@perfmon_increment bit = 1,
@xactseq varbinary(16) = NULL,      
@command_id int = NULL,
@update_existing_row bit = 0,
@updateable_row bit = 1,		-- used to override history verbose level to decide
							-- whether the row being added can be updated by another.	
@do_raiserror bit = 1
AS

    set nocount on

    DECLARE @current_time datetime
    DECLARE @start_time datetime
    DECLARE @entry_time datetime
    DECLARE @duration int                   -- milliseconds
    DECLARE @delivery_latency int
    DECLARE @average_commands int
    DECLARE @total_cmds int
    DECLARE @publisher_id smallint
    DECLARE @publisher_db sysname
    DECLARE @publication sysname
    DECLARE @publisher sysname
    DECLARE @subscriber_id smallint
    DECLARE @subscriber sysname
    DECLARE @subscriber_db sysname
    DECLARE @article sysname
    DECLARE @article_id int
    DECLARE @publication_id int
    DECLARE @publisher_database_id int

    DECLARE @agent_name nvarchar(100)
    DEClARE @error_id int 
    DECLARE @startup int
    DECLARE @succeed int
    DECLARE @inprogress int
    DECLARE @retry int
    DECLARE @failure int
    DECLARE @validation_failure int
    DECLARE @validation_success int
    DECLARE @requested_shutdown int
    DECLARE @raiserror_status int
    DECLARE @idle int
    DECLARE @lastrow_timestamp timestamp
    declare @lastrow_xact_seqno binary(16)
    DECLARE @new_delivered_commands int
    DECLARE @new_delivered_transactions int
    DECLARE @retcode int
    DECLARE @last_delivery_rate float
    DECLARE @last_delivery_latency int
    DECLARE @avg_delivery_rate float
    DECLARE @avg_delivery_latency int
    DECLARE @perfmon_delivery_rate int
    DECLARE @existing_row_updateble bit
    DECLARE @this_row_updateable bit

    /* 
    ** Status const defined in sqlrepl.h 
    */
    set @startup = 1
    set @succeed = 2
    set @inprogress = 3
    set @idle = 4
    set @retry = 5
    set @failure = 6
    set @validation_failure = 7
    set @validation_success = 8
    set @requested_shutdown = 9

    -- To prevent cleanup up being messed up by invalid history message, only log
    -- valid history message. 
    if @runstatus > 9 or @runstatus < 1
    begin
        --Invalid history message logged
        RAISERROR (21079, 16, -1, @runstatus)
        return (1)
    end

    select @existing_row_updateble = 1
    select @this_row_updateable = 1

    select @raiserror_status = @runstatus
    if (@runstatus = @validation_failure or @runstatus = @validation_success or @runstatus = @requested_shutdown)
    begin
        select @runstatus = @inprogress
        select @this_row_updateable = 0
    end

	if (@updateable_row = 0)
	begin
		select @this_row_updateable = 0
	end
    
    -- Security Check
    exec @retcode = dbo.sp_MScheck_pull_access
        @agent_id = @agent_id,
        @agent_type = 0 -- distribution agent
    if @@error <> 0 or @retcode <> 0
        return (1)

    SELECT @current_time = GETDATE()

    -- Update Perfmon counter
    if @perfmon_increment = 1
    begin
        if @runstatus = @startup
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Distribution", 1)
        else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure)
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Distribution", -1)
    end

    -- Get agent name, publisher id and publisher_db
    select @agent_name = name, 
        @publisher_database_id = publisher_database_id,
        @publisher_id = publisher_id, @publisher_db = publisher_db,
        @publication =  publication, @subscriber_id = subscriber_id, @subscriber_db = subscriber_db
        from MSdistribution_agents
        where id = @agent_id
    select @publisher = srvname from master..sysservers where srvid = @publisher_id
    select @subscriber = srvname from master..sysservers where srvid = @subscriber_id

    /* Get start_time and xact_seqno for latest agent run */
    IF @runstatus <> 1   
    BEGIN
          
        SELECT TOP 1 
            @lastrow_xact_seqno = xact_seqno,
            @start_time = start_time, 
            @total_cmds = total_delivered_commands,
            @lastrow_timestamp = timestamp, 
            @new_delivered_transactions = @delivered_transactions - delivered_transactions,
            @new_delivered_commands = @delivered_commands - delivered_commands,
            @last_delivery_rate = delivery_rate,
            @last_delivery_latency = delivery_latency,
            @existing_row_updateble = updateable_row
        FROM MSdistribution_history (rowlock)
            WHERE agent_id = @agent_id 
            ORDER BY timestamp  DESC
    END
    ELSE
    BEGIN
        -- At least get running total of commands over all sessions.
        SELECT TOP 1 
            @lastrow_xact_seqno = xact_seqno,
            @total_cmds = total_delivered_commands
            FROM MSdistribution_history  (rowlock)
            WHERE agent_id = @agent_id
            ORDER BY timestamp  DESC
        SELECT @start_time = @current_time
        SELECT @new_delivered_commands = @delivered_commands
        SELECT @new_delivered_transactions = @delivered_transactions
        SELECT @last_delivery_rate = 0
        SELECT @last_delivery_latency = 0
    END

    IF @total_cmds IS NULL
        SELECT @total_cmds = 0

    /* Use the current time if no corresponding start_up message logged */
    IF @start_time is NULL
       SELECT @start_time = @current_time

    /* Calculate agent run duration */
    SELECT @duration = DATEDIFF(second, @start_time, @current_time) 
    
    IF @delivered_commands <> 0
       SELECT @average_commands = @delivered_commands/@delivered_transactions
    ELSE
       SELECT @average_commands = 0

    -- Get the entry time of the last distributed transaction
    if @xact_seqno <> 0x00 and @new_delivered_commands <> 0
        -- SELECT @entry_time = entry_time FROM MSrepl_transactions (READPAST)
        SELECT @entry_time = entry_time FROM MSrepl_transactions 
           WHERE xact_seqno = @xact_seqno and
                publisher_database_id = @publisher_database_id

    -- Calculate the latency of the last distributed transaction
    IF @entry_time IS NOT NULL
       SELECT @delivery_latency = DATEDIFF(millisecond, @entry_time, @current_time)
    ELSE
       SELECT @delivery_latency = 0

    -- Calculate the average delivery latency of the session
    IF @last_delivery_latency = 0
        SET @avg_delivery_latency = @delivery_latency
    ELSE IF @delivery_latency = 0
        SET @avg_delivery_latency = @last_delivery_latency
    ELSE
        SET @avg_delivery_latency = (@delivery_latency + @last_delivery_latency)/2

    -- Calculate average delivery rate of the session

    IF @last_delivery_rate = 0
        SET @avg_delivery_rate = @delivery_rate
    ELSE IF @delivery_rate = 0 or @new_delivered_commands = 0
        SET @avg_delivery_rate = @last_delivery_rate
    ELSE
        SET @avg_delivery_rate = (@delivery_rate + @last_delivery_rate)/2.0     

    /* Calculate grand total of delivered trans across sessions */
    SELECT @total_cmds = @total_cmds + @new_delivered_commands

    -- Set Perfmon counters
    if @runstatus = @idle or @runstatus = @inprogress
    begin
        dbcc addinstance ("SQL Replication Distribution", @agent_name)
        dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Trans/sec", @agent_name, @new_delivered_transactions)
        dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Cmds/sec", @agent_name, @new_delivered_commands)
        dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, @delivery_latency)
    end
    else
    begin /* Reset latency counter */
        dbcc addinstance ("SQL Replication Distribution", @agent_name)
        dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, 0)
    end

    /* 
    ** Set error id to 0 unless the user want to log errors associate with this 
    ** history message.
    */
    SELECT @error_id = 0
    IF @log_error = 1
        -- Ignore errors here. @error_id will be set to 0 in case of errors  
        EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT
    
    -- @xact_seqno may be uninitialized for the first several messages after
    -- the start-up of the distribtion agent. Get the correct value in that case. 
    -- We must do this because distribution cleanup will use the lastest xact_seqno
    -- as cleanup boundary.
    -- Note: @last_xact_seqno might be NULL
    -- Only do this if @xact_seqno is 0, since a smaller xact_seqno might be logged due
    -- to reinited sub for immediate_sync pub.
    -- This will prevent history being messed up by one gabage history entry.
    if @xact_seqno = 0x00 and @lastrow_xact_seqno is not null
        select @xact_seqno = @lastrow_xact_seqno 

    -- Insert idle record or update if history record is already 'idle'
    IF (@existing_row_updateble = 1) and (@runstatus = @idle or @update_existing_row = 1)
    begin

        -- Attempt to update the last row if it is IDLE
        if (@runstatus = @idle)
        begin
            UPDATE MSdistribution_history SET runstatus = @runstatus, time = @current_time,
            duration = @duration, comments = @comments,
            xact_seqno = @xact_seqno, updateable_row = @this_row_updateable
            WHERE
            agent_id = @agent_id and
            timestamp = @lastrow_timestamp and
            ( runstatus = @runstatus or 
            (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) )
        end
        else
        begin
            -- Attempt to update the last row if it is IDLE
            UPDATE MSdistribution_history SET runstatus = @runstatus, start_time = @start_time,
                time = @current_time,
                duration = @duration,
                xact_seqno = @xact_seqno,
                comments = @comments,
                delivered_transactions = @delivered_transactions,
                delivered_commands = @delivered_commands,
                average_commands = @average_commands,
                delivery_rate = @avg_delivery_rate,
                delivery_latency = @avg_delivery_latency,
                total_delivered_commands = @total_cmds,
                current_delivery_rate = @delivery_rate,
                current_delivery_latency = @delivery_latency,
                updateable_row = @this_row_updateable
                WHERE
                agent_id = @agent_id and
                timestamp = @lastrow_timestamp and
                ( runstatus = @runstatus or 
                (@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) )
        end
    
        -- Insert idle record if there is not one
        if @@ROWCOUNT = 0
        begin
            INSERT INTO MSdistribution_history (agent_id, runstatus, start_time, time, duration, comments, xact_seqno,
                delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency,
                total_delivered_commands, error_id, timestamp, current_delivery_rate, current_delivery_latency, updateable_row)
            VALUES (@agent_id, @runstatus, @start_time, @current_time, 
                @duration, @comments, @xact_seqno, @delivered_transactions, 
                @delivered_commands, @average_commands, @avg_delivery_rate,
                @avg_delivery_latency, @total_cmds, @error_id, NULL, @delivery_rate, @delivery_latency, @this_row_updateable)
        end
    end
    else
    begin
        INSERT INTO MSdistribution_history (agent_id, runstatus, start_time, time, duration, comments, xact_seqno,
                delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency,
                total_delivered_commands, error_id, timestamp, current_delivery_rate, current_delivery_latency, updateable_row)
        VALUES (@agent_id, @runstatus, @start_time, @current_time, 
            @duration, @comments, @xact_seqno, @delivered_transactions, 
            @delivered_commands, @average_commands, @avg_delivery_rate,
            @avg_delivery_latency, @total_cmds, @error_id, NULL, @delivery_rate, @delivery_latency, @this_row_updateable)
    end

    -- Update global replication agent status table
    exec dbo.sp_MSupdate_replication_status 
        @publisher, 
        @publisher_db,
        @publication,
        @agent_type = 3,
        @agent_name = @agent_name,
        @status = @runstatus

    if (@raiserror_status = @validation_failure or @raiserror_status = @validation_success)
    begin
        -- Get the "real" publication name (as opposed to 'ALL') and article name
        select @article_id = article_id from MSrepl_commands
        where publisher_database_id = @publisher_database_id
        and xact_seqno = @xactseq
        and command_id = @command_id
    
        select @publication = mp.publication, @publication_id = mp.publication_id
        from MSpublications as mp, MSsubscriptions as ms
        where mp.publisher_id = ms.publisher_id
        and mp.publisher_db = ms.publisher_db
        and mp.publication_id = ms.publication_id
        and ms.publisher_id = @publisher_id
        and ms.publisher_db = @publisher_db
        and ms.subscriber_id = @subscriber_id
        and ms.subscriber_db = @subscriber_db
        and ms.article_id = @article_id

        select @article = article
        from MSarticles
        where article_id = @article_id
        and publisher_id = @publisher_id
        and publisher_db = @publisher_db
        and publication_id = @publication_id
    end

    -- Raise the appropriate error
	if @do_raiserror = 1
	    exec dbo.sp_MSrepl_raiserror 'Distribution', @agent_name, @raiserror_status, @comments, @subscriber=@subscriber, @publication=@publication, @article=@article
    
    IF @@ERROR <> 0
       RETURN (1)
GO   

raiserror(15339,-1,-1,'sp_MSsubscription_cleanup')
GO
CREATE PROCEDURE sp_MSsubscription_cleanup
    @cutoff_time datetime
    as

    declare @max_time datetime
    declare @active tinyint
    declare @inactive tinyint
    declare @subscribed tinyint
    declare @virtual_anonymous smallint
    declare @virtual smallint
	declare @agent_id int
	declare @retcode int

    set nocount on

    select @active = 2
    select @inactive = 0
    select @subscribed = 1
    select @virtual = -1
    select @virtual_anonymous = -2

    select @max_time = dateadd(hour, 1, getdate())

    -- Refer to sp_MSmaximun_cleanup_xact_seqno to understand the logic
    -- in this sp. If you change the logic here, you may need to change
    -- that sp as well.

    -- Deactivate real subscriptions for agents that are working on 
    -- transactions that are older than @retention
    -- update all the subscriptions for those agents, including
    -- subscriptions that are in subscribed state!
    update MSsubscriptions  set status = @inactive where
        agent_id in (
        select a.id from 
            (select s.agent_id, s.subscription_seqno, max(isnull(h.timestamp,0x00))
                from 
                -- s stores the agent id and min subscription_seqno for
                -- active well known subscriptions
                (select s2.agent_id, min(s2.subscription_seqno) from
                    MSsubscriptions s2 
                    where
                    s2.status = @active and
                    -- Only well-known agent
                    s2.subscriber_id >= 0
                    group by s2.agent_id)
                s (agent_id, subscription_seqno)
                left join MSdistribution_history h
                on (h.agent_id = s.agent_id)
                group by s.agent_id, s.subscription_seqno)
            sh (agent_id, subscription_seqno, timestamp)
            left join MSdistribution_history dh 
            on (dh.agent_id = sh.agent_id and dh.timestamp = sh.timestamp)
            -- a is for publisher_database_id
            join MSdistribution_agents a
            on (a.id = sh.agent_id)
            where
            @cutoff_time > = (
                -- Get the entry_time of the first tran that cannot be
                -- cleaned up normally because of this agent.
                -- Use history if it's larger and it is not null (exists)
                case  when dh.xact_seqno >= sh.subscription_seqno 
                then
                    isnull((select top 1 entry_time from MSrepl_transactions t where
                        t.publisher_database_id =   a.publisher_database_id and
                        -- history xact_seqno can be cleaned up
                        t.xact_seqno > isnull(dh.xact_seqno,0x0)
                        order by t.xact_seqno asc), @max_time)
                else
                    isnull((select top 1 entry_time from MSrepl_transactions t where
                        t.publisher_database_id =   a.publisher_database_id and
                        -- sub xact_seqno cannot be cleaned up
                        t.xact_seqno >= sh.subscription_seqno
                        order by t.xact_seqno asc), @max_time)
                end))

    if @@rowcount <> 0
        RAISERROR(21011, 10, -1) 

    -- Dropping all the aonymous agents that are working on
    -- transactions that are older than @retention
    -- No message raised.
	-- Don't drop agents that do not have history (true for new agents).
    -- For each publisher/publisherdb pair do cleanup
    declare hC CURSOR LOCAL FAST_FORWARD FOR 
        select distinct a.id from 
            (select s.agent_id, s.subscription_seqno, max(isnull(h.timestamp,0x00))
                from 
                -- s stores the agent id and min subscription_seqno for
                -- active anonymous subscriptions
                (select a2.id, min(s2.subscription_seqno) from
                    MSsubscriptions s2 
                    join MSdistribution_agents a2
                    on (a2.anonymous_agent_id = s2.agent_id)
                    where
                    s2.status = @active 
                    group by a2.id)
                s (agent_id, subscription_seqno)
                join MSdistribution_history h
                on (h.agent_id = s.agent_id)
                group by s.agent_id, s.subscription_seqno)
            sh (agent_id, subscription_seqno, timestamp)
            join MSdistribution_history dh 
            on (dh.agent_id = sh.agent_id and dh.timestamp = sh.timestamp)
            join MSdistribution_agents a
            -- a is for publisher_database_id
            on (a.id = sh.agent_id)
            where
            @cutoff_time > = (
                -- Get the entry_time of the first tran that cannot be
                -- cleaned up normally because of this agent.
                -- Use history if it's larger and it is not null (exists)
                case  when dh.xact_seqno >= sh.subscription_seqno 
                then
                    isnull((select top 1 entry_time from MSrepl_transactions t where
                        t.publisher_database_id =   a.publisher_database_id and
                        -- history xact_seqno can be cleaned up
                        t.xact_seqno > isnull(dh.xact_seqno,0x0)
                        order by t.xact_seqno asc), @max_time)
                else
                    isnull((select top 1 entry_time from MSrepl_transactions t where
                        t.publisher_database_id =   a.publisher_database_id and
                        -- sub xact_seqno cannot be cleaned up
                        t.xact_seqno >= sh.subscription_seqno
                        order by t.xact_seqno asc), @max_time)
                end)
    for read only
    open hC
    fetch hC into @agent_id
    while (@@fetch_status <> -1)
    begin
		exec @retcode = dbo.sp_MSdrop_distribution_agentid @agent_id
		if @retcode <> 0 or @@error <> 0
			return (1)
	    fetch hC into @agent_id
	end

    -- Deactivating subscriptions virtual subscriptions that are older then @retention.
    update MSsubscriptions  set status = @subscribed
        where
        -- Only change active subscriptions!
        status = @active and 
        subscriber_id = @virtual and
                -- Get the entry_time of the first tran that cannot be
                -- cleaned up normally because of this subscription.
            isnull((select top 1 entry_time from MSrepl_transactions t where
                t.publisher_database_id = MSsubscriptions.publisher_database_id and
                xact_seqno >= MSsubscriptions.subscription_seqno
                order by t.xact_seqno asc), @max_time)
            <= @cutoff_time
    if @@rowcount <> 0
        RAISERROR(21077, 10, -1) 
GO

raiserror(15339,-1,-1,'sp_MSdelete_publisherdb_trans')
GO
CREATE PROCEDURE sp_MSdelete_publisherdb_trans
    @publisher_database_id int,
    @max_xact_seqno varbinary(16),
	@max_cutoff_time datetime,
    @num_transactions int OUTPUT,
    @num_commands int OUTPUT
    as

	set nocount on
    
	declare @snapshot_bit int
    declare @directory_type int
    declare @last_xact_seqno varbinary(16)
    declare @last_log_xact_seqno varbinary(16)
    declare @max_immediate_sync_seqno varbinary(16)
    declare @delcmd nvarchar(524)
    declare @dir nvarchar(512)
    declare @row_count int
    declare @batchsize int
    declare @retcode int            /* Return value of xp_cmdshell */
	declare @has_immediate_sync bit
	declare @xact_seqno varbinary(16)
	declare @command_id int

    select @snapshot_bit = 0x80000000
    select @directory_type = 7
    select @num_transactions = 0
    select @num_commands = 0

	-- If transactions for immediate_sync publications will not be cleanup up until
	-- they are older that max retention, except for snapshot transactions.
	-- Snapshot transactions for immediate_sync publication will be cleanup up if it is
	-- not used by any subscriptions (including virtual and virtual immediate_syncymous
	-- subscriptions. Both will be reset by snapshot agent every time if the 
	-- publication is snapshot type.) The special logic for snapshot transactions
	-- is mostly for snapshop publications. It is to cleaup up the snapshot files 
	-- ASAP and not to wait for max retention.
	-- We don't need to do this for non-immediate_syncymous publications since the snapshot
	-- trans for them will be removed as soon as they are distributed and min '
	-- retention is reached.

	-- Detect if there are immediate_syncymous publications in this publishing db.
	if exists (select * from MSsubscriptions where
			publisher_database_id = @publisher_database_id and
			subscriber_id < 0)
		select @has_immediate_sync = 1
	else
		select @has_immediate_sync = 0

	if @has_immediate_sync = 1
	begin
		-- if @max_immediate_sync_seqno is null, not row will be deleted based on that.
		select @max_immediate_sync_seqno = max(xact_seqno) from MSrepl_transactions where
            publisher_database_id = @publisher_database_id and
			entry_time <= @max_cutoff_time
	end

    -- Note delete commands first since transaction table will be used for
    -- geting @max_xact_seqno (see sp_MSmaximum_cleanup_seqno).
    -- Delete all directories stored in directory command.
	if @has_immediate_sync = 0
		declare  hCdirs  CURSOR LOCAL FAST_FORWARD FOR select CONVERT(nvarchar(512), command),
			xact_seqno, command_id
			from MSrepl_commands where
			publisher_database_id = @publisher_database_id and
			xact_seqno <= @max_xact_seqno and   
			(type & ~@snapshot_bit) = @directory_type
		for read only
	else
		declare  hCdirs  CURSOR LOCAL FAST_FORWARD FOR select CONVERT(nvarchar(512), command),
			xact_seqno, command_id
			from MSrepl_commands c where
			publisher_database_id = @publisher_database_id and
			(type & ~@snapshot_bit) = @directory_type and
			xact_seqno <= @max_xact_seqno and  
			(
			-- Select the row if it is older than max retention.
			xact_seqno <= @max_immediate_sync_seqno or 
			-- Select the row if it is not used by any subscriptions.
			not exists (select * from MSsubscriptions s where
				s.publisher_database_id = @publisher_database_id and
				s.subscription_seqno = c.xact_seqno) OR
			-- Select the row if it is not for immediate_sync publications
			-- Note: directory command have article id 0 so it is not useful
			not exists (select * from MSpublications p where
				p.publication_id = (select top 1 s.publication_id 
					from MSsubscriptions s where
					s.publisher_database_id = @publisher_database_id and
					s.subscription_seqno = c.xact_seqno) and
				p.immediate_sync = 1)
			)
		for read only

    open hCdirs
    fetch hCdirs into @dir, @xact_seqno, @command_id
    while (@@fetch_status <> -1)
    begin
        select @delcmd = 'if exist "' + @dir + '" rmdir /S /Q "' + @dir + '"' 
        exec @retcode = master..xp_cmdshell @delcmd
        /* Abort the operation if the delete fails */
        if (@retcode <> 0 or @@error <> 0)
        begin
            raiserror(20015, 16, -1, @dir)
            return (1)
        end

		delete MSrepl_commands where 
			publisher_database_id = @publisher_database_id and
			xact_seqno = @xact_seqno and
			command_id = @command_id
        -- Update output parameter
        select @num_commands = @num_commands + @@rowcount

	    fetch hCdirs into @dir, @xact_seqno, @command_id
    end
    close hCdirs
    deallocate hCdirs


    -- Delete all commans less than or equal to the @max_xact_seqno
    -- Delete in batch to reduce the transaction size
    set rowcount 2000
    WHILE 1 = 1
    BEGIN
		if @has_immediate_sync = 0
			DELETE MSrepl_commands WITH (PAGLOCK) where
				publisher_database_id = @publisher_database_id and
				xact_seqno <= @max_xact_seqno
		else
			-- Use nolock hint on subscription table to avoid deadlock
			-- with snapshot agent.
			DELETE MSrepl_commands WITH (PAGLOCK) where
				publisher_database_id = @publisher_database_id and
				xact_seqno <= @max_xact_seqno and
				-- Don't delete directory commands. It is deleted ealier.
				-- We have to do this because we use (nolock) hint. we have to make sure
				-- we don't delete dir command when the file is not cleaned up in the code
				-- above.
				(type & ~@snapshot_bit) <> @directory_type and
				(
				-- Select the row if it is older than max retention.
				xact_seqno <= @max_immediate_sync_seqno or 
				-- Select the row if it is not for immediate_sync article
				-- We know the command is for immediate_sync publication if
				-- the snapshot tran include articles that has virtual
				-- subscritptions. (use subscritpion table to avoid join with
				-- article and publication table)
				not exists (select * from MSsubscriptions s (nolock) where
					s.publisher_database_id = @publisher_database_id and
					s.article_id = MSrepl_commands.article_id and
					s.subscriber_id < 0) or
				-- Select the row if it is snapshot tran and 
				-- not used by any subscriptions.
				((type & @snapshot_bit) <> 0 and
				not exists (select * from MSsubscriptions s (nolock) where
					s.publisher_database_id = @publisher_database_id and
					s.subscription_seqno = MSrepl_commands.xact_seqno))
				)

		select @row_count = @@rowcount
        -- Update output parameter
        select @num_commands = @num_commands + @row_count
    
        IF @row_count = 0 -- passed the result set.  We're done
            BREAK
    END

    -- Optimized query to get the max transaction row
    set rowcount 1
    select @last_log_xact_seqno = xact_seqno from MSrepl_transactions
        where
            publisher_database_id = @publisher_database_id and
            xact_id <> 0x0  -- not initial sync transaction
            order by xact_seqno desc

    select @last_xact_seqno = xact_seqno from MSrepl_transactions
        where
            publisher_database_id = @publisher_database_id
            order by xact_seqno desc
    set rowcount 0

    -- Remove all transactions less than or equal to the @max_xact_seqno and leave the 
    -- last transaction row
    -- Note @max_xact_seqno might be null, in this case don't do any thing.
    -- Delete in batchs to reduce the transaction size
    set rowcount 5000
    -- Delete all commans less than or equal to the @max_xact_seqno
    -- Delete  rows to reduce the transaction size
    WHILE 1 = 1
    BEGIN
		if @has_immediate_sync = 0
			delete MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions where
				publisher_database_id = @publisher_database_id and
				xact_seqno <= @max_xact_seqno and
				xact_seqno <> @last_xact_seqno and
				xact_seqno <> @last_log_xact_seqno
		else
			delete MSrepl_transactions WITH (PAGLOCK) from MSrepl_transactions where
				publisher_database_id = @publisher_database_id and
				xact_seqno <= @max_xact_seqno and
				xact_seqno <> @last_xact_seqno and
				xact_seqno <> @last_log_xact_seqno and
				-- use nolock to avoid deadlock
				not exists (select * from MSrepl_commands c (nolock) where
					c.publisher_database_id = @publisher_database_id and
					c.xact_seqno = MSrepl_transactions.xact_seqno)

        select @row_count = @@rowcount

        -- Update output parameter
        select @num_transactions = @num_transactions + @row_count
        if @row_count = 0
            BREAK
    END
    set rowcount 0
GO


raiserror(15339,-1,-1,'sp_MSmaximum_cleanup_seqno')
go
CREATE PROCEDURE sp_MSmaximum_cleanup_seqno
    @publisher_database_id int,
    @min_cutoff_time datetime,
    @max_cleanup_xact_seqno varbinary(16) OUTPUT
    as

    declare @min_sub_xact_seqno varbinary(16)
    declare @min_history_xact_seqno varbinary(16)
    declare @active int
    declare @max_seqno varbinary(16)
    
    -- @max_seqn is to avoid warnging message from server on null values in aggregation.
    select @max_seqno = 0xffffffffffffffffffffffffffffffff

    set nocount on

    select @active = 2

    select  
        -- Get the min history xact_seqno that are large then or equal to starting
        -- sub seqno
        @min_history_xact_seqno = min(seqno.xact_seqno), 
        -- Get the min starting sub xact_seqno without larger history xact_seqno or without
        -- any history at all.
        @min_sub_xact_seqno = min(seqno.subscription_seqno)
        from
            -- seqno stores min sub seqno , min history seqno and choose_sub
            (select 
                -- If history is larger and it is not null
                -- use history.
                case when dh.xact_seqno > = sh.subscription_seqno 
                then dh.xact_seqno
                -- Don't use dh in else
                else @max_seqno
                end,

                -- If history is larger and it is not null
                -- use history.
                case when dh.xact_seqno > = sh.subscription_seqno 
                -- Don't use sh
                then @max_seqno
                else sh.subscription_seqno
                end

                from 
                    (select s.agent_id, s.subscription_seqno, max(isnull(h.timestamp,0x00))
                    from 
                        -- s stores the agent id and min subscription_seqno for
                        -- active subscriptions on non immediate_sync subscriptions
                        (select a.id, min(s2.subscription_seqno) from
                            MSsubscriptions s2 
                            join MSdistribution_agents a
                            on (a.id = s2.agent_id) 
                            where
                            s2.status = @active and
                            /* Note must filter out virtual anonymous agents !!!
								a.subscriber_id <> @virtual_anonymous and */
							-- filter out subscriptions to immediate_sync publications
							not exists (select * from MSpublications p where
								s2.publication_id = p.publication_id and
								p.immediate_sync = 1) and
                            a.publisher_database_id = @publisher_database_id
                            group by a.id)
                        s (agent_id, subscription_seqno)
                        left join MSdistribution_history h
                        on (h.agent_id = s.agent_id)
                        group by s.agent_id, s.subscription_seqno)
                    sh (agent_id, subscription_seqno, timestamp)
                    left join MSdistribution_history dh 
                    on (dh.agent_id = sh.agent_id and dh.timestamp = sh.timestamp))
                    seqno (xact_seqno, subscription_seqno)



/*  This version might be faster.. Need experiments.


    -- Get the min history xact_seqno that are large then or equal to starting
    -- sub seqno
    select @min_history_xact_seqno = min(dh.xact_seqno) from
        -- h stores the agent_ids and last xact_seqno for the publisher_db 
        (select h2.agent_id, max(h2.timestamp) 
            from MSdistribution_history h2
            join MSdistribution_agents a
                on (a.id = h2.agent_id)
            where
                a.publisher_database_id = @publisher_database_id
                group by h2.agent_id)
        h (agent_id, timestamp) 
        -- dh stores timestamp and xact_seqno association
        join MSdistribution_history dh 
            on (dh.agent_id = h.agent_id and dh.timestamp = h.timestamp)
        where   
            -- Only select history xact seqno larger then or equal to
            -- subscription start up seqno.
            -- Excluding agents with no active subscriptions. 
            dh.xact_seqno >=
                (select min(s.subscription_seqno) from MSsubscritions s where
                    -- Including anonymous agents
                    (s.agent_id = a. agent_id or s.agent_id = a.anonymous_agent_id) and
                    s.status = @active)
            
    -- Get the min starting sub xact_seqno without larger history xact_seqno or without
    -- any history at all.
    -- This will happen if the subscription is reinited
    -- excluding virtual anonymous subscriptions.
    select @min_sub_xact_seqno = min(s.subscription_seqno) from
        MSsubscriptions s
        -- left join so that subscription without history will be included. 
        left join ((select h2.agent_id, max(h2.timestamp) 
            from MSdistribution_history h2
            join MSdistribution_agents a
                on (a.id = h2.agent_id)
            where
                a.publisher_database_id = @publisher_database_id
                group by h2.agent_id)
            h (agent_id, timestamp) 
        -- dh stores timestamp and xact_seqno association
        join MSdistribution_history dh 
            on (dh.agent_id = h.agent_id and dh.timestamp = h.timestamp))
            on (s.agent_id = h.agent_id)
        where
        s.status = @active and
        s.subscriber_id <> @virtual_anonymous and
        s.publisher_database_id = @publisher_database_id and
        -- select only subs that has a larger starting seqno than history last seqno
        -- or with no history
        s.subscription_seqno > isnull(dh.xact_seqno, 0x0)
*/  
    
    /* 
    ** Optimized query to get the maximum cleanup xact_seqno
    */
    /* 
    ** If the query below returns nothing, nothing can be deleted.
    ** Reset @max_cleanup_xact_seqno to 0.
    */
    select @max_cleanup_xact_seqno = 0x00
    -- Use top 1 to avoid warning message of "Null in aggregate..." which will make
    -- sqlserver agent job having failing status
    select top 1 @max_cleanup_xact_seqno = xact_seqno
        from MSrepl_transactions
        where
            publisher_database_id = @publisher_database_id and
            (@min_history_xact_seqno IS NULL or
            -- Delete the history xact since it is distributed already.
            xact_seqno <= @min_history_xact_seqno) and
            (@min_sub_xact_seqno IS NULL or
            -- Don't delete the sub xact since it is not distributed yet.
            xact_seqno < @min_sub_xact_seqno) and
            entry_time <= @min_cutoff_time
            order by xact_seqno desc

GO

raiserror(15339,-1,-1,'sp_MSdistribution_delete')
go
CREATE PROCEDURE sp_MSdistribution_delete
    @retention int = 0,
	-- Used for anon publications.
	@max_cutoff_time datetime
    as
    declare @min_cutoff_time datetime
    declare @subscriber sysname
    declare @subscriber_db sysname
    declare @max_cleanup_xact_seqno varbinary(16)   
    declare @num_transactions int
    declare @num_commands int
    declare @start_time datetime
    declare @num_seconds int
    declare @rate int
    declare @retcode int
    declare @publisher_database_id int

    set nocount on

    select @num_transactions = 0
    select @num_commands = 0

    select @start_time = getdate()
    select @min_cutoff_time = dateadd(hour, -@retention, getdate())

    -- For each publisher/publisherdb pair do cleanup
    declare hC CURSOR LOCAL FAST_FORWARD FOR select distinct publisher_database_id
        from MSrepl_transactions
        for read only
    -- With ANSI Defaults ON, the cursor will automatically
    -- be closed on commit.   Since this proc gets called recursively, 
    -- this can happen.  So check before opening. 
    IF CURSOR_STATUS('local','hC') = -1
    open hC

    fetch hC into @publisher_database_id 
    while (@@fetch_status <> -1)
    begin

        -- Find the maximum transaction to delete
        exec @retcode = dbo.sp_MSmaximum_cleanup_seqno @publisher_database_id, @min_cutoff_time, @max_cleanup_xact_seqno OUTPUT
        if @retcode <> 0
            goto FAIL           

        -- Delete transactions and commands
        exec @retcode = dbo.sp_MSdelete_publisherdb_trans @publisher_database_id, 
			@max_cleanup_xact_seqno, @max_cutoff_time,
            @num_transactions OUTPUT, @num_commands OUTPUT
        if @retcode <> 0
            goto FAIL

        IF CURSOR_STATUS('local','hC') = -1
            open hC
        
        fetch hC into @publisher_database_id 
    end
    close hC
    deallocate hC

    select @num_seconds = datediff(second, @start_time, getdate())
    if @num_seconds <> 0 
      select @rate = (@num_transactions+@num_commands)/@num_seconds
    else
      select @rate = 0

   RAISERROR(21010, 10, -1, @num_transactions, @num_commands, @num_seconds, @rate)

   return 0

FAIL:
   close hC
   deallocate hC
   return 1
GO


raiserror(15339,-1,-1,'sp_MSdistribution_cleanup')
GO
CREATE PROCEDURE sp_MSdistribution_cleanup
    @min_distretention int = 0,
    @max_distretention int = 24
    as

    declare @retcode int
    declare @agent_name nvarchar(255)
    declare @agent_type nvarchar(100)
    declare @message nvarchar(255)
	declare @cutoff_time datetime

     -- Check for invalid parameter values 
    if @min_distretention < 0 or @max_distretention < 0
    begin
        RAISERROR(14106, 16, -1)
        return (1)
    end

	-- Note: we need to use the same cut_off time for sp_MSsubscription_cleanup
	-- and sp_MSdistribution_delete since sp_MSsubscription_cleanup need to disable
	-- all the dist agents that are lag behind (their pending trans will be removed)
    select @cutoff_time = dateadd(hour, -@max_distretention, getdate())

    -- Deactive any subscriptions which have been inactive beyond the maximum retention
    exec @retcode = dbo.sp_MSsubscription_cleanup @cutoff_time
    if @retcode <> 0
        goto FAIL
 
    -- Remove transactions and commands
    exec @retcode = dbo.sp_MSdistribution_delete @min_distretention, 
		-- used to cleanup trans for anon publications.
		@cutoff_time
    if @retcode <> 0
        goto FAIL

    return(0)

FAIL:
    -- Raise the Agent Failure error
    set @agent_type  = formatmessage(20543)
    SELECT @agent_name = db_name() + @agent_type
    set @message  = formatmessage(20552)
    exec dbo.sp_MSrepl_raiserror @agent_type, @agent_name, 5, @message
    return (1)  

GO

raiserror(15339,-1,-1,'sp_MShistory_cleanup')
GO
CREATE PROCEDURE sp_MShistory_cleanup
@history_retention int = 24
as
    declare @cutoff_time datetime
    declare @start_time datetime
    declare @num_snapshot_rows int
    declare @num_logreader_rows int
    declare @num_distribution_rows int
    declare @num_replerror_rows int
    declare @num_milliseconds int
    declare @num_seconds float
    declare @seconds_str nvarchar(10)
    declare @rate int
    declare @retcode int
    declare @total_rows int
    declare @num_merge_rows int
    declare @agent_name nvarchar(255)
    declare @agent_type nvarchar(100)
    declare @message nvarchar(255)
    declare @agent_id int
    declare @temp_error int

    set nocount on

    /* Check for invalid parameter values */
    if @history_retention < 0
    BEGIN
        RAISERROR(14106, 16, -1)
        RETURN (1)
    END
    
    -- Get start time for statistics at the end
    select @start_time = getdate()

    select @num_snapshot_rows = 0
    select @num_logreader_rows = 0
    select @num_distribution_rows = 0
    select @num_merge_rows = 0
    select @num_replerror_rows = 0

    -- Get cutoff time
    select @cutoff_time = dateadd(hour, -@history_retention, getdate())

    -- Delete sp_MSsnapshot_history (leave at least one row for monitoring)
    delete MSsnapshot_history where 
        time <= @cutoff_time and
        timestamp not in (select max(timestamp) from MSsnapshot_history 
            group by agent_id)
    select @temp_error = @@error, @num_snapshot_rows = @num_snapshot_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete sp_MSsnapshot_history that no longer has an MSsnapshot_agent entry
    delete from MSsnapshot_history where not exists (select * from MSsnapshot_agents
        where id = agent_id)
    select @temp_error = @@error, @num_snapshot_rows = @num_snapshot_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete sp_MSlogreader_history (leave at least one row for monitoring)
    delete MSlogreader_history where 
        time <= @cutoff_time and
        timestamp not in (select max(timestamp) from MSlogreader_history 
            group by agent_id)
    select @temp_error = @@error, @num_logreader_rows = @num_logreader_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete sp_MSlogreader_history that no longer has an MSlogreader_agent entry
    delete from MSlogreader_history where not exists (select * from MSlogreader_agents
        where id = agent_id)
    select @temp_error = @@error, @num_logreader_rows = @num_logreader_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete sp_MSdistribution_history (leave at least one row for monitoring)
    -- Leave last record ONLY if the agent is not anonymous.  The current logic is to remove all history for anonymous
    -- subscription, the agent definition will also be removed below.
    delete MSdistribution_history where 
        time <= @cutoff_time and
        timestamp not in (select max(timestamp) from MSdistribution_history group by agent_id)
    select @temp_error = @@error, @num_distribution_rows = @num_distribution_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete sp_MSdistribution_history that no longer has an MSdistribution_agent entry
    delete MSdistribution_history from MSdistribution_history dh where not exists (select * from MSdistribution_agents
        where id = dh.agent_id)
    select @temp_error = @@error, @num_distribution_rows = @num_distribution_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete sp_MSmerge_history (leave at least one row for monitoring)
    -- Leave last record ONLY if the agent is not anonymous.  The current logic is to remove all history for anonymous
    -- subscription, the agent definition will also be removed below.
    delete from MSmerge_history where 
        time <= @cutoff_time and
        timestamp not in (select max(timestamp) from MSmerge_history group by agent_id)
    select @temp_error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete sp_MSmerge_history that no longer has an MSmerge_agent entry
    delete from MSmerge_history where not exists (select * from MSmerge_agents where id = agent_id)
    select @temp_error = @@error, @num_merge_rows = @num_merge_rows + @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Delete MSrepl_error entries
    delete from MSrepl_errors where time <= @cutoff_time 
    select @temp_error = @@error, @num_replerror_rows = @@rowcount
    if @temp_error <> 0
        goto FAIL

    -- Calculate statistics for number of rows deleted
    select @num_milliseconds = datediff(millisecond, @start_time, getdate())
    if @num_milliseconds <> 0
        select @num_seconds = @num_milliseconds*1.0/1000
    else 
        select @num_seconds = 0

    select @total_rows = @num_snapshot_rows + @num_logreader_rows + 
        @num_distribution_rows +  @num_merge_rows + @num_replerror_rows

    if @num_seconds <> 0 
        select @rate = @total_rows/@num_seconds
    else
        select @rate = @total_rows

    select @seconds_str = CONVERT(nchar(10), @num_seconds)

    RAISERROR(14108, 10, -1, @num_merge_rows, 'MSmerge_history')
    RAISERROR(14108, 10, -1, @num_snapshot_rows, 'MSsnapshot_history')
    RAISERROR(14108, 10, -1, @num_logreader_rows, 'MSlogreader_history')
    RAISERROR(14108, 10, -1, @num_distribution_rows, 'MSdistribution_history')
    RAISERROR(14108, 10, -1, @num_replerror_rows, 'MSrepl_errors')
    RAISERROR(14149, 10, -1, @total_rows, @seconds_str, @rate)
    
    return 0

FAIL:
    -- Raise the Agent Failure error
    set @agent_type  = formatmessage(20544)
    SELECT @agent_name = db_name() + @agent_type
    set @message  = formatmessage(20553)
    exec dbo.sp_MSrepl_raiserror @agent_type, @agent_name, 5, @message

    return (1)

GO

raiserror(15339,-1,-1,'sp_MSget_repl_version')
GO
CREATE PROCEDURE sp_MSget_repl_version
@major_version int = 0 OUTPUT,
@minor_version int = 0 OUTPUT,
@revision int = 0 OUTPUT

as
SELECT @major_version = major_version,
       @minor_version = minor_version,
       @revision = revision FROM MSrepl_version
GO

raiserror(15339,-1,-1,'sp_MSenum_subscriptions')
GO
create procedure sp_MSenum_subscriptions
@publisher sysname,
@publisher_db sysname,
@publication sysname
as

    declare @subscriber sysname
    declare @subscriber_id smallint
    declare @subscriber_db sysname
    declare @subscriber_name sysname
    declare @type int
    declare @status int
    declare @distribution_agent nvarchar(100)
    declare @publisher_id smallint
    declare @independent_agent bit

    declare @start_time nvarchar(24)
    declare @time nvarchar(24)
    declare @duration int
    declare @comments nvarchar(255)
    declare @delivery_time int
    declare @delivered_transactions int
    declare @delivered_commands int
    declare @average_commands int
    declare @delivery_rate int
    declare @delivery_latency int
    declare @error_id int
    declare @publication_id int
    declare @job_id binary(16)
    declare @agent_id int
    declare @local_job bit
    declare @profile_id int
    declare @current_time datetime
    declare @last_timestamp binary(8)

    set nocount on

    set @current_time = getdate()

    select @publisher_id = srvid from master..sysservers where
       UPPER(srvname) = UPPER(@publisher)

    select @publication_id = publication_id from MSpublications  where 
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication = @publication and
            (publication_type = 0 or publication_type = 1)

    create table #subscriptions (subscriber sysname NOT NULL,  status int NOT NULL, 
        subscriber_db sysname NOT NULL,
        type tinyint NOT NULL, distribution_agent nvarchar(100) NOT NULL, last_action nvarchar(255) NULL, 
        action_time nvarchar(24) NULL, start_time nvarchar(24) NULL, duration int NULL, 
        delivery_rate float NULL,
        delivery_latency int NULL, delivered_transactions int NULL, 
        delivered_commands int NULL,
        delivery_time int NULL, average_commands int NULL, 
        error_id int NULL, 
        job_id binary(16) NULL, local_job bit NULL, profile_id int NOT NULL,
        agent_id int NOT NULL, last_timestamp binary(8) NOT NULL)
    
    -- This is to force all queries to return rows ordered by job_id
	-- Note: There might be dist agents left for cleaning up sub, in this case
	-- they are not in sub table and we don't want to show them
    create unique clustered index ucsubscriptions ON #subscriptions (agent_id)
    declare hC CURSOR LOCAL FAST_FORWARD FOR select id, name, subscriber_id, subscriber_db,
        job_id, local_job, subscription_type, profile_id, subscriber_name
        from MSdistribution_agents a
        where exists (select * from MSsubscriptions s where
			(a.id = s.agent_id or a.anonymous_agent_id = s.agent_id) and
			s.publisher_id = @publisher_id and
			s.publisher_db = @publisher_db and
			-- For 6.x publisher, we don't know the association between the publication
			-- and subscriptions. Show every dist agent under each publication.
			(s.publication_id = @publication_id or s.publication_id = 0 ) and
			(a.subscriber_id >= 0  or a.subscriber_id is NULL))
        for read only

    -- declare hC CURSOR LOCAL FAST_FORWARD FOR select a.id, a.name, a.subscriber_id, ms.subscriber_db,
    --  a.job_id, a.local_job, ms.subscription_type, a.profile_id, a.subscriber_name
    --    from MSdistribution_agents a, master..sysservers s, MSsubscriptions ms
    --    where
    --    a.publisher_id = @publisher_id and
    --    a.publisher_db = @publisher_db and
    --  (a.publication = @publication or a.publication = 'ALL') and
    --  a.subscriber_id >= 0 and
    --  ms.publisher_db = @publisher_db and
    --  ms.publication_id = @publication_id and
    --  ms.subscriber_id = a.subscriber_id and
    --  ms.subscriber_db = a.subscriber_db and
    --  s.srvid = ms.subscriber_id
    --  for read only

    open hC
    fetch hC into  @agent_id, @distribution_agent, @subscriber_id, @subscriber_db,
        @job_id, @local_job, @type, @profile_id, @subscriber_name
    while (@@fetch_status <> -1)
    begin
        /* Stuff in the values for no history case */
        select @status = 0, 
            @start_time = NULL,
            @time = NULL, @duration = NULL, @comments = NULL,
            @delivery_time = NULL, @delivered_transactions = NULL,
            @delivered_commands = NULL, @average_commands = NULL,
            @delivery_rate = NULL, @delivery_latency = NULL,
            @error_id = NULL,
            @last_timestamp = 0x00000000

        -- Get the status of the agent
        select @status = runstatus, 
            @start_time = convert(nvarchar(12), start_time, 112) +
                          substring(convert(nvarchar(24), start_time, 121), 11, 13),
            @time = convert(nvarchar(12), time, 112) +
                    substring(convert(nvarchar(24), time, 121), 11, 13), 
            @duration = DATEDIFF(second, start_time, @current_time), 
            @comments = comments,
            @delivery_time = 0, @delivered_transactions = delivered_transactions,
            @delivered_commands = delivered_commands, @average_commands = average_commands,
            -- Note: return average rate here !!! delivery_rate column is current rate
            @delivery_rate = delivery_rate,
            @delivery_latency = delivery_latency,
            @error_id = error_id, @last_timestamp = timestamp
            from MSdistribution_history
            where
                agent_id = @agent_id and
                timestamp = (select max(timestamp) from MSdistribution_history 
                    where 
                    agent_id = @agent_id)

        -- For anonymous subscriptions, @subscriber_name is not NULL
         if @subscriber_name is NULL
                select @subscriber = srvname from master..sysservers where srvid=@subscriber_id
            else 
                begin
                    select @subscriber = @subscriber_name
                    select @subscriber_db = @subscriber_db + '-' + convert(nvarchar(4), @agent_id)
                end

        insert into #subscriptions values ( @subscriber, @status, @subscriber_db,
            @type, @distribution_agent, @comments, @time, @start_time, @duration,
            @delivery_rate, @delivery_latency, @delivered_transactions, 
            @delivered_commands,  @delivery_time, @average_commands,
            @error_id, @job_id, @local_job, @profile_id, @agent_id, @last_timestamp)

        fetch hC into  @agent_id, @distribution_agent, @subscriber_id, @subscriber_db,
            @job_id, @local_job, @type, @profile_id, @subscriber_name
      end

    select * from #subscriptions order by job_id asc 

    drop table #subscriptions
    close hC
    deallocate hC
go


raiserror(15339,-1,-1,'sp_MSenum_snapshot')
go
create procedure sp_MSenum_snapshot
@name nvarchar(100) = '%',
@show_distdb bit = 0
as
    set nocount on

    declare @publisher sysname
    declare @publisher_db sysname
    declare @publication sysname
    declare @publication_id int
    declare @snapshot_agent nvarchar(100)
    declare @status int
    declare @start_time nvarchar(24)
    declare @time nvarchar(24)
    declare @duration int
    declare @comments nvarchar(255)
    declare @delivered_transactions int
    declare @delivered_commands int
    declare @delivery_rate int
    declare @publisher_id smallint
    declare @error_id int
    declare @job_id binary(16)
    declare @local_job bit
    declare @profile_id int
    declare @agent_id int
    declare @current_time datetime
    declare @last_timestamp binary(8)

    set @current_time = getdate()

    create table #snapshot_agent (name nvarchar(100) NOT NULL, status int NOT NULL,
        publisher sysname NOT NULL, publisher_db sysname NOT NULL, publication sysname NOT NULL,
        start_time nvarchar(24)  NULL, time nvarchar(24)  NULL, duration int NULL, 
        comments nvarchar(255) NULL, delivered_transactions int NULL, delivered_commands int NULL,
        delivery_rate float  NULL, error_id int NULL, job_id binary(16) NULL, local_job bit NOT NULL,
        profile_id int NOT NULL, agent_id int NOT NULL, last_timestamp binary(8) NOT NULL)
  

    declare hC  CURSOR LOCAL FAST_FORWARD FOR
         select srvname, sa.publisher_db, sa.publication, sa.name, sa.publisher_id,
            sa.local_job, sa.job_id, sa.profile_id, sa.id
            from
            MSsnapshot_agents sa, master..sysservers
            where 
            name LIKE @name and
            srvid = sa.publisher_id
        for read only

    OPEN hC
    FETCH hC INTO @publisher, @publisher_db, @publication, @snapshot_agent, @publisher_id,
        @local_job, @job_id, @profile_id, @agent_id
    WHILE (@@fetch_status <> -1)
        begin
     
        -- Get the publication id
        select @publication_id = isnull(publication_id, 0) from MSpublications where
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication = @publication
            
        -- Stuff in the values for no history case 
        select @status = 0, 
            @start_time = NULL,
            @time = NULL, @duration = NULL, @comments = NULL,
            @delivered_commands = NULL, 
            @delivery_rate = NULL, 
            @error_id = NULL,
            @last_timestamp = 0x00000000        
       
        -- Get the status of the agent
        select @status = runstatus, 
            @start_time = convert(nvarchar(12), start_time, 112) + 
                          substring(convert(nvarchar(24), start_time, 121), 11, 13), 
            @time = convert(nvarchar(12), time, 112) + 
                    substring(convert(nvarchar(24), time, 121), 11, 13), 
            @duration = DATEDIFF(second, start_time, @current_time), 
            @comments = comments,
            @delivered_transactions = delivered_transactions,
            @delivered_commands = delivered_commands,
            @delivery_rate = delivery_rate, 
            @error_id = error_id, 
            @last_timestamp = timestamp
            from MSsnapshot_history
            where
            agent_id = @agent_id and
            timestamp = (select top 1 timestamp from MSsnapshot_history where           
                agent_id = @agent_id
                order by timestamp DESC) 
    
        insert into #snapshot_agent values (@snapshot_agent, @status, @publisher,
            @publisher_db, @publication, @start_time, @time, @duration, @comments,
            @delivered_transactions, @delivered_commands, @delivery_rate, @error_id, @job_id, 
            @local_job, @profile_id, @agent_id, @last_timestamp)

        FETCH hC INTO @publisher, @publisher_db, @publication, @snapshot_agent,
            @publisher_id, @local_job, @job_id, @profile_id, @agent_id
        end

    if @show_distdb = 0
        select * from #snapshot_agent 
    else 
        select 'dbname' = DB_NAME(), * from #snapshot_agent

    drop table #snapshot_agent
    close hC
    deallocate hC

GO

raiserror(15339,-1,-1,'sp_MSenum_snapshot_s')
go
create procedure sp_MSenum_snapshot_s
@name nvarchar(100),
@hours int = 0,
@session_type int = 1 /* Return all sessions */
as
    set nocount on

    declare @succeed int
    declare @retry int
    declare @failure int
    declare @min_time datetime
    declare @independent_agent bit
    declare @publisher_id smallint
    declare @publisher_db sysname
    declare @publication sysname

    /* 
    ** Status const defined in sqlrepl.h 
    */
    select @succeed = 2
    select @retry = 5
    select @failure = 6

    SELECT @publisher_id = publisher_id, @publisher_db = publisher_db, 
        @publication = publication
        from
        MSsnapshot_agents 
        where 
        name = @name 

    /* Get date starting point */
    IF @hours = 0 or @hours < 0
    BEGIN
        select @min_time = NULL
    END
    ELSE
    BEGIN
        select @min_time = dateadd(hour, -@hours, getdate())
    END

    select sh1.runstatus, 
        'start_time' = convert(nvarchar(12), sh1.start_time, 112) + 
                       substring(convert(nvarchar(24), sh1.start_time, 121), 11, 13), 
        'time' = convert(nvarchar(12), sh1.time, 112) + 
                 substring(convert(nvarchar(24), sh1.time, 121), 11, 13), 
        sh1.comments, sh1.duration,
        sh1.delivery_rate, sh1.delivered_commands,
        'action_count' = (select count(*) from MSsnapshot_history where
                start_time = sh1.start_time),       
        sh1.error_id
        from MSsnapshot_history sh1, MSsnapshot_agents sa
        where
        sa.publisher_id = @publisher_id and
        sa.publisher_db = @publisher_db and
        sa.publication = @publication and
        sh1.agent_id = sa.id and
        ((@session_type = 1 and 
            (sh1.runstatus = @succeed or
                sh1.runstatus = @retry or
                sh1.timestamp = (select max(timestamp) from MSsnapshot_history sh2 where
                    sh2.agent_id = sh1.agent_id))) or
        sh1.runstatus = @failure) and
        (sh1.time >= @min_time OR @min_time IS NULL)
        order by sh1.timestamp desc
GO


raiserror(15339,-1,-1,'sp_MSenum_snapshot_sd')
go
create procedure sp_MSenum_snapshot_sd
@name nvarchar(100),
@time datetime = NULL
as
    set nocount on

    declare @start_time datetime
    declare @time_up datetime
    declare @publisher_id smallint
    declare @publisher_db sysname
    declare @publication sysname

    IF @time IS NULL
        select @time = GETDATE()
    /* 
    ** If @name is given, get its publisher and subscriber pair
    ** Note: param will be overwritten
    ** If @name is NOT given, use provided param.
    */
    SELECT @publisher_id = pub.publisher_id, @publisher_db = pub.publisher_db, 
        @publication = pub.publication
        from
        MSsnapshot_agents agent, MSpublications pub
        where 
        name LIKE @name and
        agent.publisher_id = pub.publisher_id and
        agent.publication = pub.publication and
        agent.publisher_db = pub.publisher_db
    
    /*
    ** Minute-approximate @time can be used.
    **
    ** Note: The select only return datetime data with minute precisio
    */
    IF  DATEPART(second, @time) = 0 AND
        DATEPART(millisecond, @time) = 0
    BEGIN
        SELECT @time_up = DATEADD(second, +59, @time)
        SELECT @time_up = DATEADD(millisecond, +999, @time)
    END
    ELSE
        SELECT @time_up = @time
        

    select top 1 @start_time = sh.start_time            
         from MSsnapshot_history sh, MSsnapshot_agents sa
        where
        sa.publisher_id = @publisher_id and
        sa.publisher_db = @publisher_db and
        sa.publication  = @publication and
        sh.agent_id = sa.id and
        sh.time <= @time_up 
        order by sh.timestamp DESC

    select sh.runstatus, 
        'time' = convert(nvarchar(12), sh.time, 112) + 
                 substring(convert(nvarchar(24), sh.time, 121), 11, 13), 
        sh.comments, 
        sh.duration, 
        sh.delivery_rate, 
        sh.delivered_commands,
        sa.name, 
        sh.error_id         
        from MSsnapshot_history sh, MSsnapshot_agents sa
        where
        sa.publisher_id = @publisher_id and
        sa.publisher_db = @publisher_db and
        sa.publication = @publication and 
        sh.agent_id = sa.id and
        sh.start_time = @start_time 
        order by sh.timestamp desc
GO


raiserror(15339,-1,-1,'sp_MSenum_logreader')
go
create procedure sp_MSenum_logreader
@name nvarchar(100) = '%',
@show_distdb bit = 0
as
    set nocount on

    declare @publisher sysname
    declare @publisher_db sysname
    declare @logreader_agent nvarchar(100)
    declare @status int
    declare @start_time nvarchar(24)
    declare @time nvarchar(24)
    declare @duration int
    declare @comments nvarchar(255)
    declare @delivery_time int
    declare @delivered_transactions int
    declare @delivered_commands int
    declare @average_commands int
    declare @delivery_rate int
    declare @delivery_latency int
    declare @error_id int
    declare @job_id binary(16)
    declare @local_job bit
    declare @profile_id int
    declare @agent_id int
    declare @current_time datetime
    declare @last_timestamp binary(8)

    set @current_time = getdate()

    create table #logreader_agent (name nvarchar(100) NOT NULL, status int NOT NULL,
        publisher sysname NOT NULL, publisher_db sysname NOT NULL,
        start_time nvarchar(24)  NULL, time nvarchar(24)  NULL, duration int  NULL, 
        comments nvarchar(255) NULL, delivery_time int NULL, 
        delivered_transactions int  NULL, delivered_commands int NULL, 
        average_commands int  NULL, delivery_rate int NULL, 
        delivery_latency int NULL, error_id int NULL, job_id binary(16) NULL,
        local_job bit NULL, profile_id int NOT NULL, agent_id int NOT NULL, last_timestamp binary(8) NOT NULL)  
   
    declare hC  CURSOR LOCAL FAST_FORWARD FOR
         select server.srvname, agent.publisher_db, name, 
            local_job, job_id, agent.profile_id, agent.id
            from
            MSlogreader_agents agent, master..sysservers server
            where 
            name LIKE @name and
            server.srvid = agent.publisher_id
        
        for read only

    OPEN hC
    FETCH hC INTO @publisher, @publisher_db, @logreader_agent, 
        @local_job, @job_id, @profile_id, @agent_id
    WHILE (@@fetch_status <> -1)
        begin

        /* Stuff in the values for no history case */
        select @status = 0, 
            @start_time = NULL,
            @time = NULL, @duration = NULL, @comments = NULL,
            @delivery_time = NULL, @delivered_transactions = NULL,
            @delivered_commands = NULL, @average_commands = NULL,
            @delivery_rate = NULL, @delivery_latency = NULL,
            @error_id = NULL,
            @last_timestamp = 0x00000000

        -- Get the status of the agent
        select @status = lh.runstatus, 
            @start_time = convert(nvarchar(12), start_time, 112) +
                          substring(convert(nvarchar(24), start_time, 121), 11, 13),
            @time = convert(nvarchar(12), time, 112) +
                    substring(convert(nvarchar(24),  time, 121), 11, 13), 
            @duration = DATEDIFF(second, start_time, @current_time), 
            @comments = comments,
            @delivery_time = delivery_time, @delivered_transactions = delivered_transactions,
            @delivered_commands = delivered_commands, @average_commands = average_commands,
            @delivery_rate = delivery_rate, @delivery_latency = delivery_latency,
            @error_id = error_id, @last_timestamp = timestamp
            from MSlogreader_history lh
            where
            lh.agent_id = @agent_id and
            lh.timestamp = (select max(timestamp) from MSlogreader_history
                where
                agent_id = lh.agent_id)

        insert into #logreader_agent values (@logreader_agent, @status, @publisher,
            @publisher_db, @start_time, @time, @duration, @comments,
            @delivery_time, @delivered_transactions, @delivered_commands, @average_commands,
            @delivery_rate, @delivery_latency, @error_id, @job_id, @local_job,
            @profile_id, @agent_id, @last_timestamp)

        FETCH hC INTO @publisher, @publisher_db, @logreader_agent, 
            @local_job, @job_id, @profile_id, @agent_id
        end

    if @show_distdb = 0
        select * from #logreader_agent 
    else 
        select 'dbname' = DB_NAME(), * from #logreader_agent

    drop table #logreader_agent
    close hC
    deallocate hC
GO


raiserror(15339,-1,-1,'sp_MSenum_logreader_s')
go

create procedure sp_MSenum_logreader_s
@name nvarchar(100),
@hours int = 0,
@session_type int = 1 /* Return all sessions */
as
    set nocount on

    declare @succeed int
    declare @retry int
    declare @failure int                                 
    declare @min_time datetime

    if @session_type  not in (1,2)
    begin
        return(1)
    end 

    /* 
    ** Status const defined in sqlrepl.h 
    */
    select @succeed = 2
    select @retry = 5
    select @failure = 6

    /* Get date starting point */
    IF @hours = 0 or @hours < 0
    BEGIN
        select @min_time = NULL
    END
    ELSE
    BEGIN
        select @min_time = dateadd(hour, -@hours, getdate())
    END

    select  runstatus, 
            'start_time' = convert(nvarchar(12), start_time, 112) + 
                           substring(convert(nvarchar(24), start_time, 121), 11, 13), 
            'time' = convert(nvarchar(12), time, 112) +
                     substring(convert(nvarchar(24), time, 121), 11, 13), 
            comments, duration, delivery_rate, delivery_latency,
        delivery_time,  delivered_transactions, delivered_commands, average_commands, 
        'action_count' = (select count(*) from MSlogreader_history where
            start_time = rh.start_time),
        error_id
        from MSlogreader_history rh
        where
        rh.agent_id = (select top 1 id from MSlogreader_agents where name = @name) and
        ((@session_type = 1 and (runstatus = @succeed or
            runstatus = @retry or
            timestamp = (select max(timestamp) from MSlogreader_history rh2 where
                rh2.agent_id = rh.agent_id))) or
        runstatus = @failure) and
        (time >= @min_time OR @min_time IS NULL)
        order by timestamp desc
GO

raiserror(15339,-1,-1,'sp_MSenum_logreader_sd')
go

create procedure sp_MSenum_logreader_sd
@name nvarchar(100),
@time datetime = NULL
as
    set nocount on

    declare @start_time datetime
    declare @time_up datetime

    IF @time IS NULL
        select @time = GETDATE()
    /*
    ** Minute-approximate @time can be used.
    ** Note: The select only return datetime data with minute precision
    */
    IF  DATEPART(second, @time) = 0 AND
        DATEPART(millisecond, @time) = 0
    BEGIN
        SELECT @time_up = DATEADD(second, +59, @time)
        SELECT @time_up = DATEADD(millisecond, +999, @time)
    END
    ELSE
        SELECT @time_up = @time
        

    select  top 1 @start_time = start_time          
         from MSlogreader_history rh
        where
        rh.agent_id = (select top 1 id from MSlogreader_agents where name = @name) and
        time <= @time_up 
        order by timestamp DESC

    select  runstatus, 
            'time' = convert(nvarchar(12), time, 112) +
                     substring(convert(nvarchar(24), time, 121), 11, 13), 
            comments,duration, delivery_rate, delivery_latency,
        delivery_time,  delivered_transactions, delivered_commands, average_commands, 
        error_id        
        from MSlogreader_history rh
        where
        rh.agent_id = (select top 1 id from MSlogreader_agents where name = @name) and
        start_time = @start_time 
        order by timestamp desc
GO


raiserror(15339,-1,-1,'sp_MSenum_distribution')
go
create procedure sp_MSenum_distribution
@name nvarchar(100) = '%',
@show_distdb bit = 0
as
    set nocount on

    declare @publisher sysname
    declare @publisher_id smallint
    declare @publisher_db sysname
    declare @publication sysname
    declare @subscriber sysname
    declare @subscriber_id smallint
    declare @subscriber_db sysname
    declare @subscriber_name sysname
    declare @distribution_agent nvarchar(100)
    declare @status int
    declare @start_time nvarchar(24)
    declare @time nvarchar(24)
    declare @duration int
    declare @comments nvarchar(255)
    declare @delivery_time int
    declare @delivered_transactions int
    declare @delivered_commands int
    declare @average_commands int
    declare @delivery_rate int
    declare @delivery_latency int   
    declare @subscription_type int
    declare @error_id int
    declare @job_id binary(16)
    declare @local_job bit
    declare @agent_id int
    declare @profile_id int
    declare @current_time datetime
    declare @last_timestamp binary(8)


    set @current_time = getdate()

    create table #distribution_agent (name nvarchar(100) NOT NULL, status int NOT NULL,
        publisher sysname NOT NULL, publisher_db sysname NOT NULL, publication sysname NULL,
        subscriber sysname NULL, subscriber_db sysname NULL,subscription_type int NULL, 
        start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, 
        comments nvarchar(255) NULL, delivery_time int NULL, 
        delivered_transactions int NULL, delivered_commands int NULL, 
        average_commands int NULL, delivery_rate int NULL, delivery_latency int NULL,
        error_id int NULL, job_id binary(16) NULL, local_job bit NOT NULL,
        profile_id int NOT NULL, agent_id int NOT NULL, last_timestamp binary(8) NOT NULL)

    /* excluding virtual subscriptions */
    declare hC CURSOR LOCAL FAST_FORWARD FOR
        select id, name, publisher_id, publisher_db, publication, 
            subscriber_id, subscriber_db, subscription_type, local_job, job_id,
            profile_id, subscriber_name
            from
            MSdistribution_agents 
            where 
            name LIKE @name and subscriber_id is NULL or subscriber_id>=0  --No virtual subscription wanted
        for read only


    OPEN hC
    FETCH hC INTO @agent_id, @distribution_agent, @publisher_id, @publisher_db, @publication,
        @subscriber_id, @subscriber_db, @subscription_type, @local_job, @job_id, @profile_id, @subscriber_name
    WHILE (@@fetch_status <> -1)
        begin
        
        /* Stuff in the values for no history case */
        select @status = 0, 
            @start_time = NULL,
            @time = NULL, @duration = NULL, @comments = NULL,
            @delivery_time = NULL, @delivered_transactions = NULL,
            @delivered_commands = NULL, @average_commands = NULL,
            @delivery_rate = NULL, @delivery_latency = NULL,
            @error_id = NULL,
            @last_timestamp = 0x00000000

        select @status = isnull(runstatus, 0),
            @start_time = convert(nvarchar(12), start_time, 112) +
                          substring(convert(nvarchar(24), start_time, 121), 11, 13),
            @time = convert(nvarchar(12), time, 112) +
                    substring(convert(nvarchar(24), time, 121), 11, 13), 
            @duration = DATEDIFF(second, start_time, @current_time), 
            @comments = comments,
            @delivery_time = 0, @delivered_transactions = delivered_transactions,
            @delivered_commands = delivered_commands, @average_commands = average_commands,
            @delivery_rate = delivery_rate,
            @delivery_latency = delivery_latency,
            @error_id = error_id, @last_timestamp = timestamp   
            from MSdistribution_history
            where
            agent_id = @agent_id and
            timestamp = (select max(timestamp) from MSdistribution_history 
                where agent_id = @agent_id)
            
            select @publisher = srvname from master..sysservers where srvid = @publisher_id
        IF @subscriber_name is NULL
                select @subscriber = srvname from master..sysservers where srvid=@subscriber_id 
        ELSE 
            begin
                select @subscription_type = 2
                select @subscriber = @subscriber_name
                select @subscriber_db = @subscriber_db + '-' + convert(nvarchar(4), @agent_id)
            end
            
        insert into #distribution_agent values (@distribution_agent,
            @status, @publisher,
            @publisher_db, @publication, @subscriber, @subscriber_db, @subscription_type, 
            @start_time, @time, @duration, @comments,
            @delivery_time, @delivered_transactions, @delivered_commands, @average_commands,
            @delivery_rate, @delivery_latency, @error_id, @job_id, 
            @local_job, @profile_id, @agent_id, @last_timestamp)

        FETCH hC INTO @agent_id, @distribution_agent, @publisher_id, @publisher_db, @publication,
            @subscriber_id, @subscriber_db, @subscription_type, @local_job, @job_id, 
            @profile_id, @subscriber_name
        end

    if @show_distdb = 0
        select * from #distribution_agent 
    else 
        select 'dbname' = DB_NAME(), * from #distribution_agent

    drop table #distribution_agent
    close hC
    deallocate hC

GO


raiserror(15339,-1,-1,'sp_MSenum_distribution_s')
go
create procedure sp_MSenum_distribution_s
@name nvarchar(100), 
@hours int = 0,
@session_type int = 1 /* Return all sessions */
as
    set nocount on

    declare @succeed int
    declare @retry int
    declare @failure int
    declare @min_time datetime
    declare @agent_id int

    /* 
    ** Status const defined in sqlrepl.h 
    */
    select @succeed = 2
    select @retry = 5
    select @failure = 6

    select @agent_id = id from MSdistribution_agents where
        name = @name


    /* Get date starting point */
    IF @hours = 0 or @hours < 0
    BEGIN
        select @min_time = NULL
    END
    ELSE
    BEGIN
        select @min_time = dateadd(hour, -@hours, getdate())
    END

    select  runstatus, 
        'start_time' = convert(nvarchar(12), start_time, 112) +
                       substring(convert(nvarchar(24), start_time, 121), 11, 13), 
        'time' = convert(nvarchar(12), time, 112) +
                 substring(convert(nvarchar(24), time, 121), 11, 13), 
        comments, duration, 
        delivery_rate,
        delivery_latency,
        0, /*delivery_time */   
        delivered_transactions, delivered_commands, average_commands, 
        'action_count' = (select count(*) from MSdistribution_history where
            start_time = rh.start_time),
        error_id
        from MSdistribution_history rh
        where
        agent_id = @agent_id and
        ((@session_type = 1 and
        (runstatus = @succeed or
        runstatus = @retry or
        timestamp = (select max(timestamp) from MSdistribution_history rh2 where
            rh2.agent_id = @agent_id))) or 
        runstatus = @failure) and
        (time >= @min_time or @min_time IS NULL)
        order by timestamp desc
GO


raiserror(15339,-1,-1,'sp_MSenum_distribution_sd')
go
create procedure sp_MSenum_distribution_sd
@name nvarchar(100),
@time datetime = NULL
as
    set nocount on

    declare @start_time datetime
    declare @time_up datetime
    declare @agent_id int

    IF @time IS NULL
        select @time = GETDATE()
    
    select @agent_id = id from MSdistribution_agents where
        name = @name

    /*
    ** Minute-approximate @time can be used.
    ** Note: The select only return datetime data with minute precision
    */
    IF  DATEPART(second, @time) = 0 AND
        DATEPART(millisecond, @time) = 0
    BEGIN
        SELECT @time_up = DATEADD(second, +59, @time)
        SELECT @time_up = DATEADD(millisecond, +999, @time)
    END
    ELSE
        SELECT @time_up = @time
        

    select top 1 @start_time = start_time           
         from MSdistribution_history rh
        where
        rh.agent_id = @agent_id and
        time <= @time_up
        order by timestamp DESC

    select  runstatus, 
        'time' = convert(nvarchar(12), time, 112) +
                 substring(convert(nvarchar(24), time, 121), 11, 13), 
        comments, duration, 
        delivery_rate,
        delivery_latency,
        /* delivery_time */ 0,  
        delivered_transactions, delivered_commands, average_commands, 
        error_id            
        from MSdistribution_history rh
        where
        rh.agent_id = @agent_id and
        start_time = @start_time
        order by timestamp desc
GO


raiserror(15339,-1,-1,'sp_MSadd_merge_history')
go
CREATE PROCEDURE sp_MSadd_merge_history
@agent_id       int,        
@runstatus      int, 
@comments       nvarchar(255),
@delivery_time  int = 0,                    /* Milliseconds */
@publisher_insertcount  int = 0,
@publisher_updatecount  int = 0,
@publisher_deletecount  int = 0,
@publisher_conflictcount int = 0,
@subscriber_insertcount int = 0,
@subscriber_updatecount int = 0,
@subscriber_deletecount int = 0,
@subscriber_conflictcount int = 0,
@log_error bit = 0,
@perfmon_increment bit = 1,
@update_existing_row bit = 0,
@updateable_row bit = 1, 	-- used to override history verbose level to decide
							-- whether the row being added can be updated by another.	
@do_raiserror bit = 1
AS

    DECLARE @current_time datetime
    DECLARE @start_time datetime
    DECLARE @duration int
    DECLARE @delivery_rate float
    DECLARE @perfmon_delivery_rate int
    DECLARE @perfmon_conflict_count int
    DECLARE @delivered_rows int
    DECLARE @delivery_seconds float
	DECLARE @changes int

    DECLARE @delivery_time_old  int
    DECLARE @delivered_rows_old int
    DECLARE @publisher_insertcount_old  int 
    DECLARE @publisher_updatecount_old  int 
    DECLARE @publisher_deletecount_old  int 
    DECLARE @publisher_conflictcount_old int 
    DECLARE @subscriber_insertcount_old int 
    DECLARE @subscriber_updatecount_old int 
    DECLARE @subscriber_deletecount_old int 
    DECLARE @subscriber_conflictcount_old int 
    DECLARE @publisher_id smallint
    DECLARE @subscriber_id smallint
    DEClARE @error_id int 
    DECLARE @startup int
    DECLARE @succeed int
    DECLARE @retry int
    DECLARE @inprogress int
    DECLARE @failure int
    DECLARE @idle int
    DECLARE @lastrow_timestamp timestamp
    DECLARE @agent_name nvarchar(100)
    DECLARE @publisher sysname
    DECLARE @publisher_db sysname
    DECLARE @publication sysname
    DECLARE @retcode int
	DECLARE @existing_row_updateble bit
    DECLARE @this_row_updateable bit

    /* 
    ** Status const defined in sqlrepl.h 
    */
    set @startup = 1
    set @succeed = 2
    set @inprogress = 3
    set @idle = 4
    set @retry = 5
    set @failure = 6

    select  @delivery_time_old  = 0
    select  @publisher_insertcount_old   = 0
    select  @publisher_updatecount_old   = 0
    select  @publisher_deletecount_old   = 0
    select  @publisher_conflictcount_old  = 0
    select  @subscriber_insertcount_old  = 0
    select  @subscriber_updatecount_old  = 0
    select  @subscriber_deletecount_old  = 0
    select  @subscriber_conflictcount_old  = 0
	select	@existing_row_updateble = 1
    select  @this_row_updateable = 1

	if (@updateable_row = 0)
	begin
		select @this_row_updateable = 0
	end

    -- Security Check
    exec @retcode = dbo.sp_MScheck_pull_access
        @agent_id = @agent_id,
        @agent_type = 1 -- merge agent
    if @@error <> 0 or @retcode <> 0
        return (1)

    SELECT @current_time = GETDATE()

    -- Update Perfmon counter
    if @perfmon_increment = 1
    begin
        if @runstatus = @startup
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Merge", 1)
        else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure)
            dbcc incrementinstance ("SQL Replication Agents", "Running", "Merge", -1)
    end

    SELECT @agent_name = name, @publisher_id = publisher_id, @publisher_db = publisher_db, 
        @publication = publication from MSmerge_agents where id = @agent_id
    select @publisher = srvname from master..sysservers where srvid = @publisher_id

    /* Get start_time for latest agent run */
    IF @runstatus <> 1 --Start status
    BEGIN
        SELECT TOP 1 @start_time = start_time, @lastrow_timestamp = timestamp
            FROM MSmerge_history (rowlock)
        WHERE  agent_id = @agent_id ORDER BY timestamp DESC
    END
    ELSE
       SELECT @start_time = @current_time

    IF @runstatus = @inprogress
        BEGIN
            SELECT TOP 1 @publisher_insertcount_old = publisher_insertcount ,
                     @publisher_updatecount_old = publisher_updatecount, 
                     @publisher_deletecount_old = publisher_deletecount,
                     @publisher_conflictcount_old = publisher_conflictcount,
                     @subscriber_insertcount_old = subscriber_insertcount, 
                     @subscriber_updatecount_old = subscriber_updatecount,
                     @subscriber_deletecount_old = subscriber_deletecount,
                     @subscriber_conflictcount_old = subscriber_conflictcount,
                     @delivery_time_old = delivery_time,
					 @existing_row_updateble = updateable_row
            FROM MSmerge_history (rowlock)
            WHERE  agent_id = @agent_id ORDER BY timestamp DESC
        
            select @delivered_rows_old = @publisher_insertcount_old + 
                             @publisher_updatecount_old +
                             @publisher_deletecount_old +
                             @subscriber_updatecount_old +
                             @subscriber_insertcount_old +
                             @subscriber_deletecount_old 
        END

    /* Use the current time if no corresponding start_up message logged */
    IF @start_time IS NULL
       SELECT @start_time = @current_time

    /* Calculate agent run duration */
    SELECT @duration = DATEDIFF(second, @start_time, @current_time) 
                             
    select @delivered_rows = @publisher_insertcount + 
                             @publisher_updatecount +
                             @publisher_deletecount +
                             @subscriber_updatecount +
                             @subscriber_insertcount +
                             @subscriber_deletecount 

    -- Set Perfmon counters
    if @runstatus = @idle or @runstatus = @inprogress
    begin
		
        dbcc addinstance ("SQL Replication Merge", @agent_name)

		set @changes = @publisher_insertcount + @publisher_updatecount + @publisher_deletecount                             
        dbcc incrementinstance ("SQL Replication Merge", "Downloaded Changes/sec", @agent_name, @changes)

		set @changes = @subscriber_updatecount + @subscriber_insertcount + @subscriber_deletecount 
        dbcc incrementinstance ("SQL Replication Merge", "Uploaded Changes/sec", @agent_name, @changes)

        set @perfmon_conflict_count = @publisher_conflictcount + @subscriber_conflictcount
        dbcc incrementinstance ("SQL Replication Merge", "Conflicts/sec", @agent_name, @perfmon_conflict_count)
    end

    if @runstatus = @inprogress -- if it is in progress, then do incremental change
    begin
        select @publisher_insertcount = @publisher_insertcount_old + @publisher_insertcount
        select      @publisher_updatecount = @publisher_updatecount_old + @publisher_updatecount
        select      @publisher_deletecount = @publisher_deletecount_old + @publisher_deletecount
        select      @publisher_conflictcount = @publisher_conflictcount_old + @publisher_conflictcount
        select      @subscriber_insertcount = @subscriber_insertcount_old + @subscriber_insertcount
        select     @subscriber_updatecount = @subscriber_updatecount_old + @subscriber_updatecount
        select     @subscriber_deletecount = @subscriber_deletecount_old + @subscriber_deletecount
        select     @subscriber_conflictcount = @subscriber_conflictcount_old + @subscriber_conflictcount

        select  @delivery_time = @delivery_time_old + @delivery_time
        select @delivered_rows = @delivered_rows + @delivered_rows_old
    end
    
    /* Calculate delivery_rate */
    IF @delivery_time <> 0 
        select @delivery_seconds = @delivery_time/1000
    ELSE
        select @delivery_seconds = 0

    IF @delivery_seconds <> 0 
       SELECT @delivery_rate = (@delivered_rows * 1.0) / @delivery_seconds
    ELSE
       SELECT @delivery_rate = 0

	/* 
    ** Set error id to 0 unless the user want to log errors associate with this 
    ** history message.
    */
    SELECT @error_id = 0
    IF @log_error = 1
        -- Ignore errors here. @error_id will be set to 0 in case of errors  
        EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT

    -- Insert idle record or update if history record is already 'idle'
    IF (@existing_row_updateble = 1) and (@runstatus = @idle or @update_existing_row = 1)
    begin
        -- Attempt to update the last row if it is IDLE
        UPDATE MSmerge_history SET runstatus = @runstatus, time = @current_time, duration = @duration,
            comments = @comments,
            delivery_time = @delivery_time, 
            delivery_rate = @delivery_rate, 
            publisher_insertcount = @publisher_insertcount,
            publisher_updatecount = @publisher_updatecount,
            publisher_deletecount = @publisher_deletecount,
            publisher_conflictcount = @publisher_conflictcount,
            subscriber_insertcount = @subscriber_insertcount,
            subscriber_updatecount = @subscriber_updatecount,
            subscriber_deletecount = @subscriber_deletecount,
            subscriber_conflictcount = @subscriber_conflictcount,
			updateable_row = @this_row_updateable
            WHERE
            agent_id = @agent_id and
            timestamp = @lastrow_timestamp and
            (runstatus = @runstatus or 
			(@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)))

        -- Insert idle record if there is not one
        if @@ROWCOUNT = 0
            INSERT INTO MSmerge_history
			(
			agent_id, runstatus, start_time, time, duration, comments, delivery_time, delivery_rate, publisher_insertcount,
			publisher_updatecount, publisher_deletecount, publisher_conflictcount, subscriber_insertcount, subscriber_updatecount,
			subscriber_deletecount, subscriber_conflictcount, error_id, timestamp, updateable_row
			)
			VALUES 
			(
			@agent_id, @runstatus, @start_time, @current_time, 
			@duration, @comments,  @delivery_time, @delivery_rate, 
	        @publisher_insertcount, @publisher_updatecount, @publisher_deletecount, @publisher_conflictcount, 
		    @subscriber_insertcount, @subscriber_updatecount, @subscriber_deletecount, @subscriber_conflictcount, 
			@error_id, NULL, @this_row_updateable
			)
    end
    else
    begin
        INSERT INTO MSmerge_history 
		(
			agent_id, runstatus, start_time, time, duration, comments, delivery_time, delivery_rate, publisher_insertcount,
			publisher_updatecount, publisher_deletecount, publisher_conflictcount, subscriber_insertcount, subscriber_updatecount,
			subscriber_deletecount, subscriber_conflictcount, error_id, timestamp, updateable_row
		)
		VALUES 
		(
		@agent_id, @runstatus, @start_time, @current_time, 
        @duration, @comments, @delivery_time, @delivery_rate, 
        @publisher_insertcount, @publisher_updatecount, @publisher_deletecount, @publisher_conflictcount, 
        @subscriber_insertcount, @subscriber_updatecount, @subscriber_deletecount, @subscriber_conflictcount, 
        @error_id, NULL, @this_row_updateable
		)
    end

    -- Update global replication agent status table
    exec dbo.sp_MSupdate_replication_status 
        @publisher, 
        @publisher_db,
        @publication,
        @publication_type = 2,
        @agent_type = 4,
        @agent_name = @agent_name,
        @status = @runstatus

    -- Raise the appropriate error
	if @do_raiserror = 1
	    exec dbo.sp_MSrepl_raiserror 'Merge', @agent_name, @runstatus, @comments

    IF @@ERROR <> 0
        RETURN (1)

    RETURN (0)
GO   

raiserror(15339,-1,-1,'sp_MSenum_merge')
go
create procedure sp_MSenum_merge
@name nvarchar(100) = '%',
@show_distdb bit = 0
as
    set nocount on

    declare @publisher sysname
    declare @publisher_id smallint
    declare @publisher_db sysname
    declare @subscriber sysname
    declare @subscriber_id smallint
    declare @subscriber_db sysname
    declare @subscriber_name sysname
    declare @subscription_type int
    declare @publication sysname
    declare @status int
    declare @start_time nvarchar(24)
    declare @time nvarchar(24)
    declare @duration int
    declare @comments nvarchar(255)
    declare @publisher_insertcount int
    declare @publisher_updatecount int
    declare @publisher_deletecount int
    declare @publisher_conflictcount int
    declare @subscriber_insertcount int
    declare @subscriber_updatecount int
    declare @subscriber_deletecount int
    declare @subscriber_conflictcount int
    declare @delivery_rate int
    declare @agent_name nvarchar(100)
    declare @error_id int
    declare @job_id binary(16)
    declare @local_job bit
    declare @profile_id int
    declare @agent_id int
    declare @current_time datetime
    declare @last_timestamp binary(8)

    set @current_time = getdate()

    create table #merge_agent (name nvarchar(100) NOT NULL, status int NOT NULL,
        publisher sysname NOT NULL, publisher_db sysname NOT NULL, publication sysname NULL,
        subscriber sysname NOT NULL, subscriber_db sysname NOT NULL, subscription_type int NULL,
        start_time nvarchar(24) NULL, time nvarchar(24) NULL, duration int NULL, 
        comments nvarchar(255) NULL, delivery_rate int NULL,
        publisher_insertcount int NULL, publisher_updatecount int NULL, publisher_deletecount int NULL,
        publisher_conficts int NULL, 
        subscriber_insertcount int NULL, subscriber_updatecount int NULL, subscriber_deletecount int NULL,
        subscriber_conficts int NULL, error_id int NULL, job_id binary(16) NULL,
        local_job bit NULL, profile_id int NOT NULL, agent_id int NOT NULL, last_timestamp binary(8) NOT NULL)

    declare hC CURSOR LOCAL FAST_FORWARD FOR
        select p.publisher_id, a.subscriber_id, a.publisher_db, a.subscriber_db, 
            p.publication, a.name, a.local_job, a.job_id, a.profile_id, a.id, a.subscriber_name
            from MSmerge_agents a, MSpublications p
            where 
            a.name LIKE @name and
            a.publisher_id = p.publisher_id and
            a.publisher_db = p.publisher_db and
            a.publication = p.publication

        for read only


    OPEN hC
    FETCH hC INTO @publisher_id, @subscriber_id, @publisher_db, @subscriber_db,
        @publication, @agent_name, @local_job, @job_id, @profile_id, @agent_id, @subscriber_name

    WHILE (@@fetch_status <> -1)
    begin

        /* Initialize the values for no history case */
        select @status = 0,
            @start_time = NULL,
            @time = NULL, 
            @duration = NULL, 
            @comments = NULL,
            @publisher_insertcount = 0,
            @publisher_deletecount = 0,
            @publisher_updatecount = 0,
            @publisher_conflictcount = 0,
            @subscriber_insertcount = 0,
            @subscriber_deletecount = 0,
            @subscriber_updatecount = 0,
            @subscriber_conflictcount = 0,
            @delivery_rate = 0,
            @error_id = NULL, 
            @last_timestamp = 0x00000000

        select @status = isnull(runstatus,0),
            @start_time = convert(nvarchar(12), start_time, 112) +
                          substring(convert(nvarchar(24), start_time, 121), 11, 13),
            @time = convert(nvarchar(12), time, 112) +
                    substring(convert(nvarchar(24), time, 121), 11, 13), 
            @duration = DATEDIFF(second, start_time, @current_time), 
            @comments = comments,
            @publisher_insertcount = publisher_insertcount,
            @publisher_deletecount = publisher_deletecount,
            @publisher_updatecount =  publisher_updatecount,
            @publisher_conflictcount =  publisher_conflictcount,
            @subscriber_insertcount = subscriber_insertcount,
            @subscriber_deletecount = subscriber_deletecount,
            @subscriber_updatecount =  subscriber_updatecount,
            @subscriber_conflictcount =  subscriber_conflictcount,
            -- Note: return average rate here !!! delivery_rate column is current rate
            @delivery_rate = 
                case    when duration <> 0 then 
                            (publisher_insertcount + publisher_updatecount +
                            publisher_deletecount + subscriber_insertcount + 
                            subscriber_updatecount + subscriber_deletecount)/duration
                        when duration = 0 then 0
                end, 
            @error_id = error_id, @last_timestamp = timestamp
            from MSmerge_history
            where
                agent_id = @agent_id and
                timestamp = (select max(timestamp) from MSmerge_history mh2
                    where mh2.agent_id = @agent_id)

        select @publisher = srvname from master..sysservers where srvid = @publisher_id

        -- For non anonymous agents, @subscriber_name is null 
        if @subscriber_name is NULL
            begin
                select @subscriber = srvname from master..sysservers where srvid = @subscriber_id
                if @local_job = 1 select @subscription_type = 0
                    else select @subscription_type = 1
            end
        else 
            begin
                select @subscriber = @subscriber_name
                select @subscriber_db = @subscriber_db + '-' +  convert(nvarchar(4), @agent_id)
                select @subscription_type = 2   -- anonymous type
            end
        
        insert into #merge_agent values (@agent_name, @status, @publisher,
            @publisher_db, @publication, @subscriber, @subscriber_db, @subscription_type,
            @start_time, @time, @duration, @comments, @delivery_rate,
            @publisher_insertcount, @publisher_updatecount, @publisher_deletecount, 
            @publisher_conflictcount,
            @subscriber_insertcount, @subscriber_updatecount, @subscriber_deletecount, 
            @subscriber_conflictcount,
            @error_id, @job_id, @local_job, @profile_id, @agent_id, @last_timestamp)

        FETCH hC INTO @publisher_id, @subscriber_id, @publisher_db, @subscriber_db,
            @publication, @agent_name, @local_job, @job_id, @profile_id,
            @agent_id, @subscriber_name
        end

    if @show_distdb = 0
        select * from #merge_agent 
    else 
        select 'dbname' = DB_NAME(), * from #merge_agent

    drop table #merge_agent
    close hC
    deallocate hC

GO


raiserror(15339,-1,-1,'sp_MSenum_merge_s')
go

create procedure sp_MSenum_merge_s
@name nvarchar(100), 
@hours int = 0,
@session_type int = 1 /* Return all sessions */
as
    set nocount on

    declare @succeed int
    declare @agent_id int
    declare @retry int
    declare @failure int
    declare @min_time datetime

    /* 
    ** Status const defined in sqlrepl.h 
    */
    select @succeed = 2
    select @retry = 5
    select @failure = 6

    select @agent_id = id from MSmerge_agents where name = @name

    /* Get date starting point */
    IF @hours = 0 or @hours < 0
    BEGIN
        select @min_time = NULL
    END
    ELSE
    BEGIN
        select @min_time = dateadd(hour, -@hours, getdate())
    END

    select  runstatus, 
        'start_time' = convert(nvarchar(12), start_time, 112) +
                       substring(convert(nvarchar(24), start_time, 121), 11, 13), 
        'time' = convert(nvarchar(12), time, 112) +
                 substring(convert(nvarchar(24), time, 121), 11, 13), 
        comments, duration, 
        -- Note: return average rate here !!! delivery_rate column is current rate
        case    when duration <> 0 then 
                        (publisher_insertcount + publisher_updatecount +
                        publisher_deletecount + subscriber_insertcount + 
                        subscriber_updatecount + subscriber_deletecount)/duration
                when duration = 0 then 0
        end, 
        publisher_insertcount, publisher_updatecount, publisher_deletecount, publisher_conflictcount,
        subscriber_insertcount, subscriber_updatecount, subscriber_deletecount, subscriber_conflictcount,
        'action_count' = (select count(*) from MSmerge_history where
            start_time = rh.start_time),
        error_id
        from MSmerge_history rh
        where
        agent_id = @agent_id and
        ((@session_type = 1 and
        (runstatus = @succeed or
        runstatus = @retry or
        timestamp = (select max(timestamp) from MSmerge_history rh2 where
            rh2.agent_id = @agent_id))) or 
        runstatus = @failure) and
        (time >= @min_time or @min_time IS NULL)
        order by timestamp desc
GO

raiserror(15339,-1,-1,'sp_MSenum_merge_sd')
go

create procedure sp_MSenum_merge_sd
@name nvarchar(100),
@time datetime = NULL
as
    set nocount on

    declare @start_time datetime
    declare @agent_id int
    declare @time_up datetime

    select @agent_id = id from MSmerge_agents where name=@name
    
    IF @time IS NULL
        select @time = GETDATE()

    /*
    ** Minute-approximate @time can be used.
    ** Note: The select only return datetime data with minute precision
    */
    IF  DATEPART(second, @time) = 0 AND
        DATEPART(millisecond, @time) = 0
    BEGIN
        SELECT @time_up = DATEADD(second, +59, @time)
        SELECT @time_up = DATEADD(millisecond, +999, @time)
    END
    ELSE
        SELECT @time_up = @time
        

    select top 1 @start_time = start_time           
         from MSmerge_history rh
        where
        rh.agent_id = @agent_id and
        time <= @time_up
        order by timestamp DESC

    select  runstatus, 
            'time' = convert(nvarchar(12), time, 112) + 
                     substring(convert(nvarchar(24), time, 121), 11, 13),
            comments, duration, 
        -- Note: return average rate here !!! delivery_rate column is current rate
        case    when duration <> 0 then 
                        (publisher_insertcount + publisher_updatecount +
                        publisher_deletecount + subscriber_insertcount + 
                        subscriber_updatecount + subscriber_deletecount)/duration
                when duration = 0 then 0
        end, 
        publisher_insertcount, publisher_updatecount, publisher_deletecount, publisher_conflictcount,
        subscriber_insertcount, subscriber_updatecount, subscriber_deletecount, subscriber_conflictcount,
        error_id            
        from MSmerge_history rh
        where
        rh.agent_id = @agent_id and
        start_time = @start_time
        order by timestamp desc
GO

raiserror(15339,-1,-1,'sp_MSadd_repl_error')
GO

CREATE PROCEDURE sp_MSadd_repl_error (
@id                 int,
@error_type_id      int,
@source_type_id     int,
@source_name        sysname,
@error_code         sysname,
@error_text         ntext
)
AS
    declare @retcode int

    -- Security Check
    -- require the login to be in cache regardless of the publication id and agent_id.
    -- This means that once a agent get into the distribution db, it
    -- can add any error.   
    exec @retcode = dbo.sp_MScheck_pull_access
    if @@error <> 0 or @retcode <> 0
        return (1)

    INSERT INTO MSrepl_errors VALUES (@id, getdate(), @error_type_id, @source_type_id,
        @source_name, @error_code, @error_text)

    IF @@ERROR <> 0
    BEGIN
       RETURN (1)
    END

    return (0)
GO   


raiserror(15339,-1,-1,'sp_MSadd_repl_alert')
GO

CREATE PROCEDURE sp_MSadd_repl_alert (
@agent_type         int,
@agent_id           int,
@error_id           int,
@alert_error_code   int,
@xact_seqno         varbinary(16),      
@command_id         int,
@publisher          sysname,
@publisher_db       sysname,
@subscriber         sysname,
@subscriber_db      sysname,
@alert_error_text   ntext
)
AS
    SET NOCOUNT ON

    declare @retcode int
    declare @article sysname
    declare @article_id int
    declare @destination_object sysname
    declare @source_object sysname
    declare @publisher_id int
    declare @publication sysname
    declare @publication_id int
    declare @publication_type int
    declare @subscriber_id int
    declare @publisher_database_id int
    declare @agent_type2 int

    -- Security Check
    if @agent_type = 3
        select @agent_type2 = 0 -- distribution
    else if @agent_type = 4
        select @agent_type2 = 1 -- merge

    if @agent_type2 is not null 
    begin
        exec @retcode = dbo.sp_MScheck_pull_access
                @agent_id = @agent_id,
                @agent_type = @agent_type2
        if @@error <> 0 or @retcode <> 0
            return (1)
    end
    else
    begin
        if is_member('db_owner') = 0
        begin
            RAISERROR (14126, 16, -1)
            return(1)
        end
    end

    select @publisher_id = srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher)
    select @subscriber_id = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber)
    
    select @publisher_database_id = id from MSpublisher_databases 
    where publisher_id = @publisher_id and publisher_db = @publisher_db

    select @article_id = article_id from MSrepl_commands
    where publisher_database_id = @publisher_database_id
    and xact_seqno = @xact_seqno
    and command_id = @command_id

    select @publication = mp.publication, @publication_id = mp.publication_id, @publication_type = mp.publication_type
    from MSpublications as mp, MSsubscriptions as ms
    where mp.publisher_id = ms.publisher_id
    and mp.publisher_db = ms.publisher_db
    and mp.publication_id = ms.publication_id
    and ms.publisher_id = @publisher_id
    and ms.publisher_db = @publisher_db
    and ms.subscriber_id = @subscriber_id
    and ms.subscriber_db = @subscriber_db
    and ms.article_id = @article_id

    select @article = article, @destination_object = destination_object, @source_object = source_object 
    from MSarticles
    where article_id = @article_id
        and publisher_id = @publisher_id
        and publisher_db = @publisher_db
        and publication_id = @publication_id

     INSERT INTO msdb.dbo.sysreplicationalerts (status, agent_type , agent_id, error_id, alert_error_code, time, publisher,
                                publisher_db, publication, publication_type, subscriber, subscriber_db,
                                article, destination_object, source_object, alert_error_text)
                VALUES (0, @agent_type, @agent_id, @error_id, @alert_error_code, getdate(), @publisher, 
                        @publisher_db, @publication, @publication_type, @subscriber, @subscriber_db, 
                        @article, @destination_object, @source_object, @alert_error_text)
    
    IF @@ERROR <> 0
    BEGIN
       RETURN (1)
    END

    return (0)
GO   

raiserror(15339,-1,-1,'sp_MSadd_replmergealert')
GO

CREATE PROCEDURE sp_MSadd_replmergealert (
@agent_type         int,
@agent_id           int,
@error_id           int,
@alert_error_code   int,
@publisher          sysname,
@publisher_db       sysname,
@publication        sysname,
@publication_type   int,
@subscriber         sysname,
@subscriber_db      sysname,
@article            sysname,
@destination_object sysname,
@source_object      sysname,
@alert_error_text   ntext
)
AS
    SET NOCOUNT ON

    INSERT INTO msdb.dbo.sysreplicationalerts (status, agent_type , agent_id, error_id, alert_error_code, time, publisher,
                                publisher_db, publication, publication_type, subscriber, subscriber_db,
                                article, destination_object, source_object, alert_error_text)
                VALUES (0, @agent_type, @agent_id, @error_id, @alert_error_code, getdate(), @publisher, 
                        @publisher_db, @publication, @publication_type, @subscriber, @subscriber_db, 
                        @article, @destination_object, @source_object, @alert_error_text)
    
    IF @@ERROR <> 0
    BEGIN
       RETURN (1)
    END

    return (0)
GO   


raiserror(15339,-1,-1,'sp_MSget_repl_error')
GO

create procedure sp_MSget_repl_error (
@id int
)
as
    set nocount on
    select  source_type_id, source_name, error_code, error_text, 
            convert(nvarchar(12), time, 112) + 
            substring(convert(nvarchar(24), time, 121), 11, 13),
            error_type_id
        from MSrepl_errors
        where
            id = @id and
            -- rows with error_type_id are placeholders
            error_type_id IS NOT NULL
        order by time ASC
GO


raiserror(15339,-1,-1,'sp_MSdist_activate_auto_sub')
go
CREATE PROCEDURE sp_MSdist_activate_auto_sub
@publisher_id int,
@publisher_db sysname,
@article_id int

as
    
  declare @automatic tinyint
  declare @active tinyint
  declare @subscribed tinyint
  
   set nocount on

    select @automatic = 1

    select @subscribed = 1
    select @active = 2


    begin transaction MSdist_activate_auto_sub

       update MSsubscriptions set status = @active,
            subscription_time = getdate()
           where
              publisher_id = @publisher_id and
              publisher_db = @publisher_db and
              article_id = @article_id and
              sync_type = @automatic and
              status = @subscribed

       if @@ERROR <> 0
          begin
          if @@trancount > 0
            rollback transaction MSdist_activate_auto_sub
          return (1)
          end

   commit transaction

GO

raiserror(15339,-1,-1,'sp_MSlock_auto_sub')
go
CREATE PROCEDURE sp_MSlock_auto_sub
@publisher_id int,
@publisher_db sysname,
@publication sysname,
@reset bit = 0  /* @reset = 1 is used for Scheduled Snapshot publications by snapshot */

as
    
  /* This sp only work for 7.0 publisher since it use the publication name */  
   set nocount on

    DECLARE @virtual smallint     /* const: virtual subscriber id */
    DECLARE @virtual_anonymous smallint /* const: virtual anonymous subscriber id */
    DECLARE @subscribed tinyint
    DECLARE @automatic tinyint
    DECLARE @publication_id int
    DECLARE @counter int
    DECLARE @independent_agent bit
    DECLARE @active tinyint

    SELECT @virtual = -1
    SELECT @virtual_anonymous = -2
    SELECT @subscribed = 1
    SELECT @active = 2
    SELECT @automatic = 1

    select @publication_id = publication_id , @independent_agent = independent_agent
        from MSpublications 
        where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication 


    /* 
    ** Set exclusive lock on the rows that will be updated to prevent deadlock 
    **      in snapshot agent.
    ** Note: using UPDATE lock may cause deadlock with sp_MSget_repl_commands as following
    ** 1. The distribution agent gets shared lock on MSsubscriptions.
    ** 2. The snapshot agent gets update lock on MSsubscriptions.
    ** 3. The snapshot agent gets exclusive lock on MSrepl_commands (inserting into the table)
    ** 4. The distribution agent waits to get shared lock on MSrepl_commands
    ** 5. The snapshot agent waits to convert update lock to exclusive lock on MSrepl_subscriptions (updating the table).
    */
    --SELECT @counter = COUNT(*) FROM MSsubscriptions (ROWLOCK UPDLOCK)
    -- 1. Avoid defered updates: Don't update fields in the clusted index
    -- 2. Avoid updating fields in the where clause 
    UPDATE MSsubscriptions SET update_mode = update_mode 
      WHERE 
         publisher_id = @publisher_id and
         publisher_db = @publisher_db and
         publication_id = @publication_id and 
         /* virtual subscriptions are automatic sync type */
         sync_type = @automatic and 
         (status = @subscribed or 
         subscriber_id = @virtual or
         subscriber_id = @virtual_anonymous or
         @reset = 1) 
GO

raiserror(15339,-1,-1,'sp_MSget_new_xact_seqno')
go
CREATE PROCEDURE sp_MSget_new_xact_seqno
@publisher_id int,
@publisher_db sysname,
@len    tinyint

AS
   declare @new_xact_seqno varbinary(16)
   declare @old_xact_seqno varbinary(16)
   declare @tag int
   declare @datalen tinyint
   declare @publisher_database_id int

   set nocount on 
   select @old_xact_seqno = NULL

    -- Get publisher database id.
    SELECT @publisher_database_id = id from MSpublisher_databases where publisher_id = @publisher_id and 
        publisher_db = @publisher_db

   set rowcount 1
   select @old_xact_seqno = rt.xact_seqno
      from
      -- Prevent inserts
      MSrepl_transactions rt (HOLDLOCK PAGLOCK UPDLOCK)
      where 
         rt.publisher_database_id = @publisher_database_id 
         order by xact_seqno desc
   set rowcount 0

   
   if @old_xact_seqno IS NULL
   begin
        /* if nothing got selected, lock the entire table 
        ** Don't return meta data !!
        */
       select @old_xact_seqno = rt.xact_seqno
          from
          MSrepl_transactions rt (HOLDLOCK TABLOCKX)
          where 0 = 1

        select @old_xact_seqno = subscription_seqno from MSsubscriptions 
        where
            publisher_database_id = @publisher_database_id and
            subscription_seqno = (select MAX(subscription_seqno) from MSsubscriptions rs2
            where
                rs2.publisher_database_id = @publisher_database_id)
   end

    select @datalen = datalength(@old_xact_seqno) 
    /* Plus one to the tag */
    if @datalen = @len
    begin
        select @tag = convert( int, convert( binary(4), substring( convert( nvarchar, @old_xact_seqno ), @datalen/2 - 2 + 1,2 ) ) )
        
        if @tag = 0xffffffff
        begin
            raiserror(21018, 16, -1)
            return(1)
        end
        /* avoid arithmatic overflow */
        if @tag = 0x7fffffff
            select @tag = 0x80000000
        else 
            select @tag = @tag + 1

        select @new_xact_seqno = 
        convert( varbinary, substring( convert(nvarchar, @old_xact_seqno), 1, @datalen/2-2)) +
        convert( VARBINARY(4), @tag )

    end
    /* Add a tag */
    else
    begin
        if @datalen + 4 <> @len
        begin
            -- We are in trouble if we reach here.
            return(1)
        end
        select @tag = 1
        select @new_xact_seqno = 
            @old_xact_seqno + CONVERT(VARBINARY(4), @tag) 
    end

   select @new_xact_seqno
GO



raiserror(15339,-1,-1,'sp_MSchange_publication')
go
CREATE PROCEDURE sp_MSchange_publication
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@property sysname,
@value nvarchar(255)
as

    set nocount on
    
    declare @publisher_id smallint
    declare @publication_type int
    declare @retcode int
    declare @max_distretention int
    declare @retention_value int
    DECLARE @cmd    nvarchar(255)
    DECLARE @cmd2   nvarchar(255)
    DECLARE @cmd3   nvarchar(255)



    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    /* Charater properties  */

    begin tran

    IF LOWER(@property) ='description'
    BEGIN
        UPDATE MSpublications SET description = @value
            WHERE   publisher_id = @publisher_id AND
                    publisher_db = @publisher_db AND
                    publication = @publication
        IF @@ERROR <> 0 
            goto UNDO
    END
    ELSE IF LOWER(@property) IN ('snapshot_agent')
    BEGIN
        -- local job name is internal and should not be changed
        if exists (select * from MSsnapshot_agents where
            publisher_id = @publisher_id and
            publication = @publication and
            publisher_db = @publisher_db and
            local_job = 1)
        begin
            raiserror(21030, 16, -1)
            goto UNDO
        end

        -- Drop and recreate
        exec @retcode = dbo.sp_MSdrop_snapshot_agent
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication

        IF @@ERROR <> 0 or @retcode <> 0
            goto UNDO

        exec @retcode = dbo.sp_MSadd_snapshot_agent
            @name = @value,
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication,
            @local_job = 0

        IF @@ERROR <> 0 or @retcode <> 0
            goto UNDO
    END
    ELSE IF LOWER(@property) IN ('logreader_agent')
    BEGIN
        -- local job name is internal and should not be changed
        if exists (select * from MSlogreader_agents where
            publisher_id = @publisher_id and
            publication = @publication and
            publisher_db = @publisher_db and
            local_job = 1)
        begin
            raiserror(21030, 16, -1)
            goto UNDO
        end

        -- Drop and recreate
        exec @retcode = dbo.sp_MSdrop_logreader_agent
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication

        IF @@ERROR <> 0 or @retcode <> 0
            goto UNDO

        exec @retcode = dbo.sp_MSadd_logreader_agent
            @name = @value,
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication,
            @local_job = 0

        IF @@ERROR <> 0 or @retcode <> 0
            goto UNDO
    END
    ELSE if LOWER(@property) IN ('retention')
    BEGIN
    	select @retention_value = convert(int, @value)
		select @publication_type = publication_type
			from MSpublications
			WHERE   publisher_id = @publisher_id AND
                    publisher_db = @publisher_db AND
                    publication = @publication
        /*
        ** For non-merge publication, retention value can not be larger than max_distretention value
        */
        IF @publication_type<>2 
        BEGIN
        	EXEC @retcode = sp_helpdistributor @max_distretention = @max_distretention OUTPUT
    		if @retcode <>0 		
    			goto UNDO
   			if @retention_value > @max_distretention
			begin
        		RAISERROR(21085, 16, -1) 
        		goto UNDO
        	end
        END
        UPDATE MSpublications set retention=@retention_value 
        WHERE   publisher_id = @publisher_id AND
                    publisher_db = @publisher_db AND
                    publication = @publication
        if @@ERROR<>0
        		goto UNDO
    END
    ELSE
    BEGIN
        SELECT @cmd = ''
        SELECT @cmd = @cmd + 'UPDATE MSpublications '
        SELECT @cmd = @cmd + '   SET ' + LOWER(@property) + ' = '
        /* @value can be 255 nchars, so don't append it */
        SELECT @cmd2 = ' WHERE publisher_id = ' + STR(@publisher_id)
        SELECT @cmd2 =  @cmd2 + ' AND publisher_db = ' + '''' + @publisher_db + ''''
        SELECT @cmd2 =  @cmd2 + ' AND publication = ' + '''' + @publication + ''''
        EXECUTE (@cmd + @value + @cmd2)
        IF @@ERROR <> 0 
            goto UNDO
    END

    COMMIT TRAN

    RETURN(0)

UNDO:
    IF @@TRANCOUNT = 1
        ROLLBACK TRAN
    ELSE
        COMMIT TRAN   
    RETURN (1)
go


raiserror(15339,-1,-1,'sp_MSadd_article')
go
CREATE PROCEDURE sp_MSadd_article
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@article sysname,
@article_id int = NULL,
@destination_object sysname = NULL,
@source_object sysname = NULL,
@description nvarchar(255) = NULL,
@source_owner   sysname = NULL
as

    set nocount on

    declare @publisher_id smallint
    declare @publication_id int
    declare @retcode int
    declare @thirdparty_flag bit
    declare @immediate_sync bit
    declare @allow_anonymous bit
    declare @subscription_seqno binary(16)
    declare @status tinyint
    declare @subscribed tinyint
    declare @active tinyint

    select @subscribed = 1  
    select @active = 2
        
    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Get publication id
    select @publication_id = publication_id,  
        @thirdparty_flag = thirdparty_flag,
        @immediate_sync = immediate_sync, @allow_anonymous = allow_anonymous 
        from MSpublications where
        publisher_id = @publisher_id and 
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is NULL
    begin
        raiserror (20026, 11, -1, @publication)
        return (1)
    end

    -- Make sure article does not already exist
    if exists (select * from MSarticles where publication_id = @publication_id and
        publisher_id = @publisher_id and publisher_db = @publisher_db and article = @article)
    begin
        if @thirdparty_flag = 1
        begin
            raiserror (14030, 16, -1, @article, @publication)
            return (1)
        end
        else
        begin
            exec @retcode = dbo.sp_MSdrop_article
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @article = @article
            if @retcode <> 0 or @@error <> 0
            begin
                return (1)
            end
        end
    end

	-- If it is a third party publication - check if a subscription exists
	-- for the publication already. If it does then we cannot add any
	-- articles - the existing subscriptions will have to dropped first and then
	-- resubscribed
	if (@thirdparty_flag = 1)
	begin
		if exists (select * from MSsubscriptions where 
				publisher_id = @publisher_id and 
				publisher_db = @publisher_db and 
				publication_id = @publication_id)
		begin
			raiserror(21131, 16, -1, @publication)
			return (1)
		end
	end

    begin tran
    save tran MSadd_article

	-- Generate new article id when one is not provided by a 
	-- third party publisher or merge publication
	if @article_id is NULL
	begin
		if (@thirdparty_flag = 1)
		begin
			-- Generate unique id per publisher
			select @article_id = max(article_id) + 1 from MSarticles where 
				publisher_id = @publisher_id
		end
		else
		begin
			-- 6.5 behavior : retain for compatibility
			-- NOTE: article_id is incremented per publisher/publisher_db/publication
			select @article_id = max(article_id) + 1 from MSarticles where 
				publisher_id = @publisher_id and
				publisher_db = @publisher_db and
				publication_id = @publication_id
		end

		if @article_id is null
			select @article_id = 1
	end

    insert into MSarticles values (@publisher_id, @publisher_db, @publication_id,
        @article, @article_id, @destination_object, @source_owner, @source_object, @description)
    if @@error <> 0
    begin
        if @@trancount > 0
        begin
            rollback tran MSadd_article
            commit tran
        end
        return (1)
    end

    -- For third party publications create immediate sync and anonymous virtual subscription
    -- with 'subscribed' status and then change anonymous virtual to 'active' status
    -- SQL Server publications will do this via RPC calls to sp_MSadd_subscription
    if @thirdparty_flag = 1 
    begin
        select @subscription_seqno = 0x00  
        if @immediate_sync = 1
        begin
            if @allow_anonymous = 1
                select @status = @active
            else
                select @status = @subscribed
            exec @retcode = dbo.sp_MSadd_subscription
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @article_id = @article_id,
                @subscriber = NULL,                 -- virtual subscription
                @status = @status,              
                @subscription_seqno = @subscription_seqno,
                @sync_type = 1  -- virtual subscriptions are automatic sync type 
            if @retcode <> 0 or @@error <> 0
            begin
                if @@trancount > 0
                begin
                    rollback tran MSadd_article
                    commit tran
                end
                return (1)
            end
        end
    end

    commit tran
go

raiserror(15339,-1,-1,'sp_MSchange_article')
go
CREATE PROCEDURE sp_MSchange_article
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@article sysname,
@article_id int,
@property nvarchar(20) = NULL,
@value nvarchar(255) = NULL
AS
	DECLARE @retcode int
	DECLARE @publisher_id smallint
	DECLARE @publication_id int

	-- Get publisher id

    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Get publication id

    select @publication_id = publication_id
        from MSpublications where
        publisher_id = @publisher_id and 
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is NULL
    begin
        raiserror (20026, 11, -1, @publication)
        return (1)
    end

	if @property = N'description'     
	begin
		update MSarticles set description = @value
		where publisher_id = @publisher_id
		and publisher_db = @publisher_db
		and publication_id = @publication_id
		and article = @article
		and article_id = @article_id
		if @@error <> 0
		begin
			return 1
		end
	end
	else if @property in( N'dest_table', N'dest_object' )
	begin
		update MSarticles set destination_object = @value
		where publisher_id = @publisher_id
		and publisher_db = @publisher_db
		and publication_id = @publication_id
		and article = @article
		and article_id = @article_id
		if @@error <> 0
		begin
			return 1
		end
	end
	else
	begin
		return 1
	end
go


raiserror(15339,-1,-1,'sp_MShelp_publication')
go   
CREATE PROCEDURE sp_MShelp_publication 
@publisher sysname,
@publisher_db sysname = '%',
@publication sysname = '%'

as

    set nocount on

    if @publisher_db != '%' and @publication != '%' and not exists (select * from MSpublications where
        publisher_id = (select srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher)) and
        publisher_db like @publisher_db and
        publication like @publication)
    begin
        raiserror(20026, 16, -1, @publication)
        return(1)
    end

    select p.publisher_db, p.publication, p.publication_id, p.publication_type, 
        --thirdparty_flag, 
        independent_agent,
        immediate_sync, allow_push, allow_pull, allow_anonymous, 'snapshot_agent' = s.name,
        'logreader_agent' = l.name, description, vendor_name
        from MSpublications p  
        LEFT OUTER JOIN MSsnapshot_agents s
        ON
        s.publisher_id = p.publisher_id and
        s.publisher_db = p.publisher_db and
        s.publication = p.publication
        LEFT OUTER JOIN MSlogreader_agents l
        ON
        (l.publisher_id = p.publisher_id and
        l.publisher_db = p.publisher_db and
        p.publication_type = 0 and              -- Only Transactional Publication has a  Log Reader
        (l.publication = p.publication OR
         thirdparty_flag = 0))
        where 
        p.publisher_id = (select srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher)) and
        p.publisher_db like @publisher_db and
        p.publication like @publication 
go

raiserror(15339,-1,-1,'sp_MShelp_article')
go
CREATE PROCEDURE sp_MShelp_article 
@publisher sysname,
@publisher_db sysname,
@publication sysname,        
@article sysname = '%'
as

    set nocount on

    select article, article_id, source_object, description, source_owner from MSarticles a where 
        a.publisher_id = (select srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher)) and 
        a.publisher_db = @publisher_db and
        a.publication_id = (select publication_id from MSpublications where 
            publisher_id = a.publisher_id and
            publisher_db = a.publisher_db and
            publication = @publication) and
        a.article like @article
go

raiserror(15339,-1,-1,'sp_MShelp_subscription')
go
CREATE PROCEDURE sp_MShelp_subscription 
@publisher sysname,
@publisher_db sysname,
@publication sysname,        
@subscriber sysname = '%',              
@subscriber_db sysname = '%'
as
set nocount on

    select distinct ss.srvname + ':' + s.subscriber_db, ss.srvname, s.subscriber_db, s.subscription_type, 
        sync_type, status, agent.name from 
        MSsubscriptions s, master..sysservers ss, MSdistribution_agents agent where 
        s.publisher_id = (select srvid from master..sysservers where UPPER(srvname) = UPPER(@publisher)) and 
        s.publisher_db = @publisher_db and
        s.publication_id = (select publication_id from MSpublications where 
            publisher_id = s.publisher_id and
            publisher_db = s.publisher_db and
            publication = @publication) and
        s.subscriber_db like @subscriber_db and
        s.subscriber_id = ss.srvid and
        ((@subscriber = N'%') or (ss.srvname = @subscriber)) and
        s.subscriber_id >= 0 and                    -- ignore virtual subscriptions
        s.agent_id = agent.id
go

raiserror(15339,-1,-1,'sp_MSadd_subscription_3rd')
go
CREATE PROCEDURE sp_MSadd_subscription_3rd
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,       
@subscriber_db sysname = NULL,
@status tinyint,                    -- 0 = inactive, 1 = subscribed, 2 = active 
@subscription_type tinyint = 0,     -- 0 = push, 1 = pull, 2 = anonymous 
@sync_type tinyint = 2,             -- 0 = none  1 = automatic snaphot  2 = no intial snapshot

@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,

@distribution_jobid binary(8) = NULL OUTPUT

as
    set nocount on

    declare @retcode int
    declare @publication_id int
    declare @article_id int
    declare @virtual_id smallint
    declare @publisher_id smallint
    declare @immediate_sync bit
    declare @allow_push bit
    declare @allow_pull bit
    declare @allow_anonymous bit
    declare @subscription_seqno binary(16)

    select @virtual_id = -1

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Get publication information
    select @publication_id = publication_id, @immediate_sync = immediate_sync,
        @allow_push = allow_push, @allow_pull = allow_pull, @allow_anonymous = allow_anonymous
        from MSpublications where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is null
    begin
        raiserror(14016, 16, -1, @publication)
        return (1)
    end

    -- Check if publication supports subscription type
    if @subscription_type = 0 and @allow_push != 1
    begin
        raiserror(20034, 16, -1, @publication, 'push')
        return (1)
    end
    else if @subscription_type = 1 and @allow_pull != 1
    begin
        raiserror(20034, 16, -1, @publication, 'pull')
        return (1)
    end
    else if @subscription_type = 2 and @allow_anonymous != 1
    begin
        raiserror(20034, 16, -1, @publication, 'anonymous')
        return (1)
    end

    begin tran
    save tran MSadd_subscription_3rd

    -- Add a subscription for each article in the publication
    declare hCMSadd_subscription_3rd CURSOR LOCAL FAST_FORWARD FOR 
        select article_id from MSarticles a where
            a.publisher_id = @publisher_id and
            a.publisher_db = @publisher_db and
            a.publication_id = @publication_id
        for read only

    open hCMSadd_subscription_3rd
    fetch hCMSadd_subscription_3rd into @article_id
        
    while (@@fetch_status <> -1)
    begin
        if @immediate_sync = 1
            select @subscription_seqno = subscription_seqno from MSsubscriptions where
                publisher_id = @publisher_id and
                publisher_db = @publisher_db and
                article_id = @article_id and
                subscriber_id = @virtual_id 
        else
            select @subscription_seqno = 0x00 --BUG need to convert

        exec @retcode = dbo.sp_MSadd_subscription
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication,        -- Must provide this
            @article_id = @article_id,
            @subscriber = @subscriber,       
            @subscriber_db = @subscriber_db,
            @status = @status,
            @subscription_seqno = @subscription_seqno,
            @subscription_type = @subscription_type,
            @sync_type = @sync_type,

            @frequency_type = @frequency_type,
            @frequency_interval = @frequency_interval,
            @frequency_relative_interval = @frequency_relative_interval,
            @frequency_recurrence_factor = @frequency_recurrence_factor,
            @frequency_subday = @frequency_subday,
            @frequency_subday_interval = @frequency_subday_interval,
            @active_start_time_of_day = @active_start_time_of_day,
            @active_end_time_of_day = @active_end_time_of_day,
            @active_start_date = @active_start_date,
            @active_end_date = @active_end_date,

            @distribution_jobid = @distribution_jobid output
        if @retcode != 0 or @@error != 0
        begin
            close hCMSadd_subscription_3rd
            deallocate hCMSadd_subscription_3rd
            if @@trancount > 0
            begin
                rollback tran MSadd_subscription_3rd
                commit tran
            end
            return(1)
        end

        fetch hCMSadd_subscription_3rd into @article_id
    end

    close hCMSadd_subscription_3rd
    deallocate hCMSadd_subscription_3rd

    -- If article_id is null, there were no articles defined for the publication
    if @article_id is NULL
    begin
        raiserror(14009, 16, -1, @publication)
        if @@trancount > 0
        begin
            rollback tran MSadd_subscription_3rd
            commit tran
        end
        return (1)
    end

    commit tran

go

raiserror(15339,-1,-1,'sp_MSdrop_subscription_3rd')
go
CREATE PROCEDURE sp_MSdrop_subscription_3rd
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,       
@subscriber_db sysname = NULL

as
    set nocount on

    declare @retcode int
    declare @article_id int
    declare @publisher_id smallint
    declare @publication_id int

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Get publication information
    select @publication_id = publication_id from MSpublications where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is null
    begin
        raiserror(14016, 16, -1, @publication)
        return (1)
    end

    begin tran
    save tran MSdrop_subscription_3rd

    -- Drop the subscription for each article in the publication
    declare hCMSdrop_subscription_3rd CURSOR LOCAL FAST_FORWARD FOR 
        select article_id from MSarticles a where
            a.publisher_id = @publisher_id and
            a.publisher_db = @publisher_db and
            a.publication_id = @publication_id
        for read only

    open hCMSdrop_subscription_3rd
    fetch hCMSdrop_subscription_3rd into @article_id
        
    while (@@fetch_status <> -1)
    begin

        exec @retcode = dbo.sp_MSdrop_subscription
            @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication,        -- Must provide this
            @article_id = @article_id,
            @subscriber = @subscriber,       
            @subscriber_db = @subscriber_db
        if @retcode != 0 or @@error != 0
        begin
            close hCMSdrop_subscription_3rd
            deallocate hCMSdrop_subscription_3rd
            if @@trancount > 0
            begin
                rollback tran MSdrop_subscription_3rd
                commit tran
            end
            return (1)
        end

        fetch hCMSdrop_subscription_3rd into @article_id
    end

    close hCMSdrop_subscription_3rd
    deallocate hCMSdrop_subscription_3rd
    commit tran
go

raiserror(15339,-1,-1,'sp_MSactivate_subscriptions')
go

CREATE PROCEDURE sp_MSactivate_subscriptions
@publisher_id int,
@publisher_db sysname,
@publication_id int, 
@xact_seqno varbinary(16),
@reset bit = 0      /* @reset = 1 is used for Scheduled Snapshot publications by snapshot */
AS

    declare @article_id int
    declare @retcode int

    declare hCseqno_3rd CURSOR LOCAL FAST_FORWARD FOR 
        select article_id from MSarticles a where
            a.publisher_id = @publisher_id and
            a.publisher_db = @publisher_db and
            a.publication_id = @publication_id
        for read only

    open hCseqno_3rd
    fetch hCseqno_3rd into @article_id
        
    while (@@fetch_status <> -1)
    begin
        select @article_id
        select @xact_seqno
        exec @retcode = dbo.sp_MSset_snapshot_xact_seqno
            @publisher_id = @publisher_id,
            @publisher_db = @publisher_db,
            @article_id = @article_id,
            @xact_seqno = @xact_seqno,
            @reset = @reset
        if @retcode != 0 or @@error != 0
        begin
            close hCseqno_3rd
            deallocate hCseqno_3rd
            return(1)
        end

        -- Activate the subscriptions
        exec @retcode = dbo.sp_MSdist_activate_auto_sub
            @publisher_id = @publisher_id,
            @publisher_db = @publisher_db,
            @article_id = @article_id
        if @retcode != 0 or @@error != 0
        begin
            close hCseqno_3rd
            deallocate hCseqno_3rd
            return(1)
        end

        fetch hCseqno_3rd into @article_id
    end

    close hCseqno_3rd
    deallocate hCseqno_3rd
GO
raiserror(15339,-1,-1,'sp_MSdrop_merge_subscription')
GO

CREATE PROCEDURE sp_MSdrop_merge_subscription
@publisher          sysname,
@publisher_db       sysname,
@publication        sysname,
@subscriber         sysname,
@subscriber_db      sysname,
@subscription_type  nvarchar(15) = 'push'           /* Subscription type - push, pull, both */ 

as

    set nocount on

    declare @publisher_id smallint
    declare @subscriber_id smallint
    declare @retcode int
    declare @publication_id int
    declare @job_id binary(16)
    declare @thirdparty_flag bit
    declare @id                 int
    declare @keep_for_last_run  bit

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Check if subscriber exists
    select @subscriber_id = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber) 
    if @subscriber_id is NULL
    begin
        raiserror (20032, 16, -1, @subscriber, @publisher) 
        return (1)
    end

    -- Check if the publication exists
    select @publication_id = publication_id,
        @thirdparty_flag = thirdparty_flag
        from MSpublications where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is NULL
    begin
        raiserror (20026, 16, -1, @publication) 
       return (1)
    end

    -- Check that subscription exists
    if not exists (select * from MSmerge_subscriptions where 
        publisher_id = @publisher_id and 
        publisher_db = @publisher_db and 
        publication_id = @publication_id and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db)
    begin
        if @thirdparty_flag = 1
        begin
            --UNDONE : Add this back again when we add pull subscriptions metedata at the distributor
            --raiserror (14050, 10, -1)
            return(1)
        end
        else
            return (0)
    end

    begin tran
    save transaction MSdrop_merge_subscription

    -- Delete the subscription 
    -- For anonymous type, delete virtual anonymous subscription also
    -- if deleting the  virtual subscription 
    -- (since there can be only one subscriber_id per article, subscriber_db doesn't matter)
    delete from MSmerge_subscriptions where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication_id = @publication_id and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db
    if @@error <> 0
    begin
        goto FAILURE
    end

    /*
    ** Get agentid to check history record
    */
    select @id=id from MSmerge_agents where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db

    /*
    ** If the subscription has not yet been synced, there is no need for subscriber side cleanup 
    ** therefore no need for the last agent run.
    */
    if exists (select * from MSmerge_history where agent_id = @id) and @subscription_type='push'
        select @keep_for_last_run = 0 -- cleanup code is not activated.
    else 
        select @keep_for_last_run = 0
    
    /*
    ** Delete Merge agent and meta data, if it exists
    */
    EXECUTE @retcode = dbo.sp_MSdrop_merge_agent 
        @publisher = @publisher,
        @publisher_db = @publisher_db,
        @publication = @publication,
        @subscriber = @subscriber,
        @subscriber_db = @subscriber_db,
        @keep_for_last_run = @keep_for_last_run
    if @@error <> 0 or @retcode <> 0
    begin
        goto FAILURE
    end

    commit transaction
    return 0
FAILURE:
    if @@trancount > 0
    begin   
        ROLLBACK TRANSACTION MSdrop_merge_subscription
        COMMIT TRANSACTION
    end
    return 1
        
GO

raiserror(15339,-1,-1,'sp_MSadd_merge_subscription')
GO
CREATE PROCEDURE sp_MSadd_merge_subscription
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,       
@subscriber_db sysname,
@subscription_type tinyint = 0,     -- 0 = push, 1 = pull
@sync_type tinyint = 1,             -- 0 = none  1 = automatic snaphot  2 = no intial snapshot
@status tinyint = 1,                -- 0 = inactive, 1 = subscribed, 2 = active 
@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@optional_command_line nvarchar(4000) = NULL,
@agent_name nvarchar(100) = NULL,
@merge_jobid binary(16) = NULL OUTPUT
as

    set nocount on

    declare @publisher_id smallint
    declare @subscriber_id smallint
    declare @publication_id int
    declare @retcode int

    -- default values 
    declare @flushfrequency int 
    declare @frequencytype int
    declare @frequencyinterval int 
    declare @frequencyrelativeinterval int
    declare @frequencyrecurrencefactor int 
    declare @frequencysubday int 
    declare @frequencysubdayinterval int
    declare @activestarttimeofday int
    declare @activeendtimeofday int
    declare @activestartdate int 
    declare @activeenddate int 
    declare @push int
    declare @local_job bit
    declare @thirdparty_flag bit

    select @push = 0

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Get the publication information 
    select @publication_id = publication_id,
        @thirdparty_flag = thirdparty_flag from 
        MSpublications where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is NULL
    begin
        raiserror (20026, 11, -1, @publication)
        return (1)
    end

    -- Get subscriber info
    select @subscriber_id = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber)

    -- Make sure subscription does not already exist
    if exists (select * from MSmerge_subscriptions where 
        publisher_id = @publisher_id and 
        publisher_db = @publisher_db and 
        publication_id = @publication_id and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db)
    begin
        if @thirdparty_flag = 1
        begin
            raiserror (14058, 16, -1)
            return(1)
        end
        else
        begin
            exec @retcode = dbo.sp_MSdrop_merge_subscription 
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @subscriber = @subscriber,
                @subscriber_db = @subscriber_db,
                @subscription_type  = @subscription_type
            if @retcode <> 0 or @@error <> 0
            begin
                return (1)
            end
        end
    end

    --Get default task parameter values from MSsubscriber_info 
    --BUG currently using the same values used for transactional publications
    -- BUG FIXED
    
    select @frequencytype = frequency_type,
        @frequencyinterval = frequency_interval,
        @frequencyrelativeinterval = frequency_relative_interval,
        @frequencyrecurrencefactor = frequency_recurrence_factor,
        @frequencysubday = frequency_subday,
        @frequencysubdayinterval = frequency_subday_interval,
        @activestarttimeofday = active_start_time_of_day,
        @activeendtimeofday = active_end_time_of_day,
        @activestartdate = active_start_date,
        @activeenddate = active_end_date
    from MSsubscriber_schedule 
    where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 1

    if @frequency_type is null
        select @frequency_type = @frequencytype

    if @frequency_interval  is null
        select  @frequency_interval = @frequencyinterval

    if @frequency_relative_interval is null
        select  @frequency_relative_interval = @frequencyrelativeinterval

    if @frequency_recurrence_factor is null
        select  @frequency_recurrence_factor = @frequencyrecurrencefactor

    if @frequency_subday is null
        select  @frequency_subday = @frequencysubday

    if @frequency_subday_interval is null
        select  @frequency_subday_interval = @frequencysubdayinterval

    if @active_start_time_of_day is null
        select  @active_start_time_of_day = @activestarttimeofday

    if @active_end_time_of_day is null
        select  @active_end_time_of_day = @activeendtimeofday

    if @active_start_date is null
        select  @active_start_date = @activestartdate

    if @active_end_date is null
        select  @active_end_date = @activeenddate

    begin transaction 

    -- If push and agent name is not passed in, create local job.
    if @subscription_type = @push AND @agent_name is NULL
        select @local_job = 1
    else
        select @local_job = 0

    -- Create Merge Agent
    exec @retcode = dbo.sp_MSadd_merge_agent
        @name = @agent_name, 
        @publisher = @publisher,
        @publisher_db = @publisher_db,
        @publication = @publication,
        @subscriber = @subscriber,
        @subscriber_db = @subscriber_db,
        @local_job = @local_job,
        @frequency_type  = @frequency_type,                         
        @frequency_interval = @frequency_interval, 
        @frequency_relative_interval = @frequency_relative_interval, 
        @frequency_recurrence_factor = @frequency_recurrence_factor, 
        @frequency_subday = @frequency_subday, 
        @frequency_subday_interval = @frequency_subday_interval,
        @active_start_time_of_day = @active_start_time_of_day, 
        @active_end_time_of_day = @active_end_time_of_day,         
        @active_start_date = @active_start_date, 
        @active_end_date = @active_end_date,
        @optional_command_line = @optional_command_line,
        @merge_jobid = @merge_jobid OUTPUT

    if @retcode <> 0 or @@error <> 0
    begin
        goto FAILURE
    end
    
    insert into MSmerge_subscriptions values (@publisher_id, @publisher_db, @publication_id,
        @subscriber_id, @subscriber_db, @subscription_type, @sync_type, @status, 
        getdate())  --BUG need to store GUID?
    if @@error <> 0
    begin
        goto FAILURE
    end

    commit transaction
    return (0)

FAILURE:
    /* UNDONE : This code is specific to 6.X nested transaction semantics */
    if @@TRANCOUNT = 1 
        ROLLBACK TRANSACTION 
    else
        COMMIT TRANSACTION 
    RETURN (1)
    
GO

raiserror(15339,-1,-1,'sp_MSenum_merge_subscriptions')
GO
create procedure sp_MSenum_merge_subscriptions
@publisher sysname,
@publisher_db sysname,
@publication sysname

as

    declare @subscriber sysname
    declare @subscriber_db sysname
    declare @subscriber_name sysname
    declare @type int
    declare @status int
    declare @agent_name nvarchar(100)
    declare @subscriber_id smallint
    declare @publisher_id smallint
    declare @start_time nvarchar(24)
    declare @time nvarchar(24)
    declare @duration int
    declare @comments nvarchar(255)
    declare @delivery_rate float
    declare @error_id int
    declare @publication_id int
    declare @publisher_insertcount int
    declare @publisher_updatecount int
    declare @publisher_deletecount int
    declare @publisher_conflictcount int
    declare @subscriber_insertcount int
    declare @subscriber_updatecount int
    declare @subscriber_deletecount int
    declare @subscriber_conflictcount int
    declare @job_id binary(16)
    declare @local_job bit
    declare @profile_id int
    declare @agent_id int
    declare @current_time datetime
    declare @last_timestamp binary(8)

    set nocount on

    set @current_time = getdate()

    select @publisher_id = srvid from master..sysservers where
       UPPER(srvname) = UPPER(@publisher)

    select @publication_id = publication_id from MSpublications  where 
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication = @publication and
            publication_type = 2 -- Merge 

    create table #merge_subscriptions (subscriber sysname NOT NULL,  status int NOT NULL, 
        subscriber_db sysname NOT NULL, type int NOT NULL, agent_name nvarchar(100) NOT NULL, last_action nvarchar(255) NULL, 
        action_time nvarchar(24) NULL, start_time nvarchar(24) NULL, duration int NULL, 
        delivery_rate float NULL,
        publisher_insertcount int NULL, publisher_updatecount int NULL, publisher_deletecount int NULL,
        publisher_conficts int NULL, 
        subscriber_insertcount int NULL, subscriber_updatecount int NULL, subscriber_deletecount int NULL,
        subscriber_conficts int NULL, error_id int NULL, job_id binary(16) NULL,
        local_job bit NULL, profile_id int NOT NULL, 
        agent_id int NOT NULL, last_timestamp binary(8) NOT NULL)
    
    -- This is to force all queries to return rows ordered by job_id
    create unique clustered index ucmerge_subscriptions ON #merge_subscriptions (agent_id)

    declare hC CURSOR LOCAL FAST_FORWARD FOR select subscriber_id, subscriber_db, name, job_id, local_job, profile_id, id, subscriber_name
                                from MSmerge_agents 
                                 where publisher_id = @publisher_id and publisher_db = @publisher_db and publication = @publication 
                            for read only
    open hC
    fetch hC into  @subscriber_id, @subscriber_db, @agent_name, @job_id, @local_job, @profile_id, @agent_id, @subscriber_name
    while (@@fetch_status <> -1)
    begin

        if @subscriber_name is not NULL 
            begin
                select @subscriber = @subscriber_name
                select @subscriber_db = @subscriber_db + '-' + convert(nvarchar(4), @agent_id)
                select @type = 2   --anonymous subscription
            end
        else
            begin
                select @subscriber = srvname from master..sysservers where srvid=@subscriber_id
                select @type = subscription_type from MSmerge_subscriptions 
                    where publisher_id = @publisher_id and
                        publisher_db = @publisher_db and
                        publication_id = @publication_id and
                        subscriber_id = @subscriber_id and
                        subscriber_db = @subscriber_db
            end
            
        -- Get the status of the agent
        select @status = 0 --BUG the below isnullis not working
        select  @start_time = NULL,
            @time = NULL, 
            @duration = NULL, 
            @comments = NULL,
            @publisher_insertcount = NULL,
            @publisher_deletecount = NULL,
            @publisher_updatecount =  NULL,
            @publisher_conflictcount =  NULL,
            @subscriber_insertcount = NULL,
            @subscriber_deletecount = NULL,
            @subscriber_updatecount =  NULL,
            @subscriber_conflictcount =  NULL,
            @delivery_rate = NULL, 
            @error_id = NULL,
            @last_timestamp = 0x00000000
                
        select @status = isnull(runstatus,0),
            @start_time = convert(nvarchar(12), start_time, 112) +
                          substring(convert(nvarchar(24), start_time, 121), 11, 13),
            @time = convert(nvarchar(12), time, 112) +
                    substring(convert(nvarchar(24), time, 121), 11, 13), 
            @duration = DATEDIFF(second, start_time, @current_time), 
            @comments = comments,
            @publisher_insertcount = publisher_insertcount,
            @publisher_deletecount = publisher_deletecount,
            @publisher_updatecount =  publisher_updatecount,
            @publisher_conflictcount =  publisher_conflictcount,
            @subscriber_insertcount = subscriber_insertcount,
            @subscriber_deletecount = subscriber_deletecount,
            @subscriber_updatecount =  subscriber_updatecount,
            @subscriber_conflictcount =  subscriber_conflictcount,
            -- Note: return average rate here !!! delivery_rate column is current rate
            @delivery_rate = 
                case    when duration <> 0 then 
                            (publisher_insertcount + publisher_updatecount +
                            publisher_deletecount + subscriber_insertcount + 
                            subscriber_updatecount + subscriber_deletecount)/duration
                        when duration = 0 then 0
                end, 
            @error_id = error_id, @last_timestamp = timestamp
            from MSmerge_history
            where
                agent_id = @agent_id and
                timestamp = (select max(timestamp) from MSmerge_history 
                    where agent_id = @agent_id)
/* Not currently working Build 351
                timestamp = (select top 1 timestamp from MSmerge_history 
                    where agent_id = @agent_id
                    order by timestamp DESC) 
*/
        
            insert into #merge_subscriptions values ( @subscriber, @status, @subscriber_db,
                @type, @agent_name, @comments, @time, @start_time, @duration,
                @delivery_rate, 
                @publisher_insertcount, @publisher_updatecount, @publisher_deletecount, 
                @publisher_conflictcount,
                @subscriber_insertcount, @subscriber_updatecount, @subscriber_deletecount, 
                @subscriber_conflictcount,
                @error_id, @job_id, @local_job, @profile_id, @agent_id, @last_timestamp)
        

        fetch hC into  @subscriber_id, @subscriber_db, @agent_name, @job_id, @local_job, @profile_id, @agent_id, @subscriber_name
      end

    select * from #merge_subscriptions order by job_id asc

    drop table #merge_subscriptions
    close hC
    deallocate hC

go

raiserror(15339,-1,-1,'sp_update_agent_profile')
GO

-- Update the profile for an agent
CREATE PROCEDURE sp_update_agent_profile (
    @agent_type     int,
    @agent_id       int,
    @profile_id int
)
AS
    SET NOCOUNT ON

    DECLARE @proc               nvarchar(255)

    DECLARE @snapshot_type          int
    DECLARE @logreader_type         int
    DECLARE @distribution_type      int
    DECLARE @merge_type         int

    SELECT @snapshot_type = 1
    SELECT @logreader_type = 2
    SELECT @distribution_type = 3
    SELECT @merge_type = 4

    IF @agent_type NOT IN (@snapshot_type, @logreader_type, @distribution_type, @merge_type)
        RETURN (1) 

    /* The profile must be defined for the agent type in MSagent_profiles table */
    IF NOT EXISTS ( select * from msdb..MSagent_profiles
            where profile_id = @profile_id
            and agent_type = @agent_type )
        RETURN (1)

    SELECT @proc = 'UPDATE ' +
        CASE @agent_type
           WHEN @snapshot_type THEN 'MSsnapshot_agents'
           WHEN @logreader_type THEN 'MSlogreader_agents'
           WHEN @distribution_type THEN 'MSdistribution_agents'
           WHEN @merge_type THEN 'MSmerge_agents'
        END
        + ' SET profile_id = ' + convert(nvarchar(10), @profile_id)
        + ' WHERE id = ' + convert(nvarchar(10), @agent_id)

    EXECUTE (@proc)

    IF @@ERROR <> 0 
        RETURN (1)
GO


raiserror(15339,-1,-1,'sp_MSprofile_in_use')
GO

CREATE PROCEDURE sp_MSprofile_in_use (
    @tablename          nvarchar(255),
    @profile_id int
)
AS
    DECLARE @usage_count int

    IF @tablename IS NULL OR @profile_id IS NULL
        return 1 ;

    IF @tablename = 'MSsnapshot_agents'
        SELECT @usage_count = count(*) FROM MSsnapshot_agents
        WHERE profile_id = @profile_id
    ELSE IF @tablename = 'MSlogreader_agents'
            SELECT @usage_count = count(*) FROM MSlogreader_agents
            WHERE profile_id = @profile_id
        ELSE IF @tablename = 'MSdistribution_agents'
                SELECT @usage_count = count(*) FROM MSdistribution_agents
                WHERE profile_id = @profile_id
            ELSE IF @tablename = 'MSmerge_agents'
                    SELECT @usage_count = count(*) FROM MSmerge_agents 
                    WHERE profile_id = @profile_id
                ELSE 
                    SELECT @usage_count = 0

    IF @usage_count = 0
        RETURN -1
    ELSE
        RETURN 0
GO


raiserror(15339,-1,-1,'sp_MSreset_subscription')
GO
CREATE PROCEDURE sp_MSreset_subscription (
    @publisher sysname,
    @publisher_db sysname,
    @publication sysname,  
    @subscriber sysname,
    @subscriber_db sysname,
    @subscription_type int  -- have to have it to identify a distribution agent.
) AS


    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode            int
    DECLARE @publisher_id       smallint
    DECLARE @subscriber_id      smallint
    DECLARE @virtual            smallint
	declare @publication_id		int
	declare @immediate_sync		bit


    /*
    ** Initializations
    */
    select @virtual = -1

    select @publisher_id = srvid from master..sysservers where 
        UPPER(srvname) = UPPER(@publisher)

    select @subscriber_id = srvid from master..sysservers where 
        UPPER(srvname) = UPPER(@subscriber) 

	select @publication_id = publication_id, @immediate_sync = immediate_sync
		from MSpublications where
        publisher_id = @publisher_id AND
        publisher_db = @publisher_db AND
        publication = @publication

    if @subscriber is NULL
        select @subscriber_id = @virtual

	-- No need to have 2 updates in one transaction.
	if @immediate_sync = 1
	begin
		UPDATE MSdistribution_agents SET subscription_guid = newid()
			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

		IF @@ERROR <> 0
			GOTO UNDO
	end

	UPDATE MSsubscriptions set subscription_time = getdate() 
        WHERE
        publisher_id = @publisher_id AND
        publisher_db = @publisher_db AND
        publication_id = @publication_id and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db and
        subscription_type = @subscription_type

    IF @@ERROR <> 0
        GOTO UNDO

    RETURN(0)

UNDO:
    return(1)
GO

raiserror(15339,-1,-1,'sp_MSget_subscription_guid')
GO
CREATE PROCEDURE sp_MSget_subscription_guid
@agent_id int

as
   set nocount on
    
   select a1.subscription_guid from MSdistribution_agents a1
    where
    -- for non anonymous agents
    (a1.virtual_agent_id is null and a1.id = @agent_id) or
    (   -- for anonymous agents
		a1.id = (select virtual_agent_id from MSdistribution_agents a2 where
			a2.id = @agent_id)) -- virtual account
            
GO


raiserror(15339,-1,-1,'sp_MShelp_profile')
GO

CREATE PROCEDURE sp_MShelp_profile (        
    @agent_id   int,
    @agent_type int,
    @profile_name sysname = NULL
)
as
    declare @profile_id int
    declare @snapshot_type int
    declare @logreader_type int
    declare @distribution_type int
    declare @merge_type int

    select @snapshot_type = 1
    select @logreader_type = 2
    select @distribution_type = 3
    select @merge_type = 4

    select @profile_id = NULL

    if (@profile_name is not null) and (rtrim(ltrim(@profile_name)) <> '')
    begin
        select @profile_id = profile_id from msdb..MSagent_profiles where
            agent_type = @agent_type and profile_name = @profile_name

		/* raise error if profile not found */
		if (@profile_id is null)
		begin
			raiserror(21123, 16, -1, @profile_name)
			return (1)
		end
    end
    
    -- if profile name not specified, use default.
    if (@profile_id is null)
    begin
        if @agent_type = @snapshot_type 
        begin
            if @agent_id = 0
                select @profile_id = profile_id from msdb..MSagent_profiles where
                    agent_type = @agent_type and def_profile = 1
            else
                select @profile_id = profile_id from MSsnapshot_agents
                    where id = @agent_id
        end
        else if @agent_type = @logreader_type
        begin
            if @agent_id = 0
                select @profile_id = profile_id from msdb..MSagent_profiles where
                    agent_type = @agent_type and def_profile = 1
            else
                select @profile_id = profile_id from MSlogreader_agents
                    where id = @agent_id
        end
        else if @agent_type = @distribution_type
        begin
            if @agent_id = 0
                select @profile_id = profile_id from msdb..MSagent_profiles where
                    agent_type = @agent_type and def_profile = 1
            else
                select @profile_id = profile_id from MSdistribution_agents
                    where id = @agent_id
        end
        else if @agent_type = @merge_type
        begin
            if @agent_id = 0
                select @profile_id = profile_id from msdb..MSagent_profiles where
                    agent_type = @agent_type and def_profile = 1
            else
                select @profile_id = profile_id from MSmerge_agents
                    where id = @agent_id
        end
    end
    
    select profile_id, parameter_name, value 
        from msdb..MSagent_parameters
        where profile_id = @profile_id
GO

raiserror(15339,-1,-1,'sp_MShelp_snapshot_agentid')
GO

CREATE PROCEDURE sp_MShelp_snapshot_agentid (
    @publisher_id       smallint,
    @publisher_db       sysname,
    @publication        sysname,
    @job_id             binary(16) = NULL
)
AS
    set nocount on
    declare @retcode int
    declare @publisher sysname
    declare @description nvarchar(255)

    -- Check if agent exists, if not and there is an 6.x tasks then create one
    if @publication is not null and @publication <> '' and not exists (select * from MSsnapshot_agents where
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication = @publication)
    begin
        -- Do it only if the agent name is valid. It will be the case if
        -- the agent is launched by SQL Server Agent
        if exists (select * from msdb..sysjobs_view where
            job_id = @job_id)
        begin
            select @publisher = srvname from master..sysservers where srvid = @publisher_id
            begin tran
            exec @retcode = dbo.sp_MSadd_snapshot_agent
                @publisher = @publisher, 
                @publisher_db = @publisher_db,
                @publication = @publication,
                @local_job = 1,
                @job_existing = 1,
                @snapshot_jobid = @job_id
            if @@ERROR<> 0 or @retcode <> 0
                goto UNDO

            -- Add a publication definition so it shows up in monitoring procs
            set @description = formatmessage(20555)
            exec @retcode = dbo.sp_MSadd_publication
                @publisher = @publisher,
                @publisher_db = @publisher_db,
                @publication = @publication,
                @publication_type = 1,              -- Make all 6.x pubs transactional
                @description = @description     -- 6.x publication description

            if @@ERROR<> 0 or @retcode <> 0
                goto UNDO
            commit tran
        end
    end

    select id, name from MSsnapshot_agents  where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication

    return(0)

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

raiserror(15339,-1,-1,'sp_MShelp_logreader_agentid')
GO

CREATE PROCEDURE sp_MShelp_logreader_agentid (
    @publisher_id       smallint,
    @publisher_db       sysname
)
AS
    set nocount on
    declare @retcode int
    declare @publisher sysname
    declare @job_id binary(16)
    declare @qv_package	varchar(20)
    declare @desk_top		int
    declare @license_value int
    declare @qv_value_package int

	select @qv_package = '845129433'
	select @license_value = 0 
	select @desk_top = 3
			
	exec @qv_value_package = master..xp_qv @qv_package
	if @@ERROR<>0 
		begin
			raiserror(20089, 16, -1)
			return (1)
		end
	if @qv_value_package = 1		 --- 1 means desktop
		select @license_value = @desk_top   -- to be consistent with all other compenents.
	
    -- Check if agent exists, if not and there is an 6.x tasks then create one
    if not exists (select * from MSlogreader_agents where
            publisher_id = @publisher_id and
            publisher_db = @publisher_db)
    begin
        select @publisher = srvname from master..sysservers where srvid = @publisher_id

        -- Do it only if the agent name is valid. It will be the case if
        -- the agent is launched by SQL Server Agent
        select @job_id = id.job_id from msdb..systasks_view v,
            msdb..systaskids id
            where 
            v.server = @publisher and           
            v.databasename = @publisher_db and
            v.subsystem = 'LogReader' and
            v.id = id.task_id

        if @job_id is not NULL
        begin
            exec @retcode = dbo.sp_MSadd_logreader_agent
                @publisher = @publisher, 
                @publisher_db = @publisher_db,
                -- 'ALL' is Used in sp_addpublication as well
                @publication = 'ALL',
                @local_job = 1,
                @job_existing = 1,
                @job_id = @job_id
            if @@ERROR<> 0 or @retcode <> 0
                return(1)
        end
    end
  
    select id, name, @license_value from MSlogreader_agents where 
        publisher_id = @publisher_id and 
        publisher_db = @publisher_db

    return(0)

GO

/*
** This procedure is to add an agent row in MSmerge_agents for an anonymous subscription,
** if it is not already there. If it is, return the agentid and agent name for monitoring
** purpose.
*/
raiserror(15339,-1,-1,'sp_MSadd_merge_anonymous_agent')
GO

CREATE PROCEDURE sp_MSadd_merge_anonymous_agent (
    @publisher_id       smallint,
    @publisher_db       sysname,
    @publication        sysname,
    @subscriber_db      sysname,
    @subscriber_name    sysname,
    @subid              uniqueidentifier, 
    @first_anonymous	int		-- 0 means this is the first time for this anonymous agent being ran.
)
AS
    declare @min_valid_day  datetime
    declare @merge_type     int
    declare @profile_id     int
    declare @subscriber_id  smallint
    declare @agent_name     sysname
    declare @agent_id       int
    declare @retcode        int
    declare @publication_id int
    declare @not_exist      bit
    declare @last_status	int
	declare @last_history	datetime
	declare @merge_jobid	uniqueidentifier
	declare @by_pass		bit
    declare @retention		int
    declare @success		int
	declare @expired		int
	declare @dropped		int
	declare @allow_anonymous bit

	select @dropped = 0
	select @expired = 0
	select @success = 2
	select @by_pass = 0

/*
** This stored procedure does not really add a job at distribution database;
** if add a row in MSmerge_agent table for anonymous subscription for the 
** purpose of history logging
*/

    -- Check to see if the publication is valid and allow anonymous
    select @publication_id = publication_id, @allow_anonymous = allow_anonymous, @retention = retention from 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

	if @allow_anonymous = 0
    begin
    	RAISERROR (21084, 16, -1, @publication)
        return 1
    end

	if @subscriber_name is null
		select @subscriber_name = N''

    if @retention is NULL or @retention =0
    	select @by_pass = 1

    -- Security check
    exec @retcode = dbo.sp_MScheck_pull_access
        @publication_id = @publication_id
    if @retcode <> 0 or @@error <> 0
        return (1)

    select @not_exist = 0
    SELECT @merge_type = 4
    select @subscriber_id = 0  -- For anonymous subscribers, ID is always 0 

    SELECT @profile_id = profile_id
    FROM msdb..MSagent_profiles
    WHERE agent_type = @merge_type
        AND def_profile = 1

    IF @profile_id IS NULL
        RETURN (1)

	/*
	** This is to handle Jet only
	*/
    IF @subid = '00000000-0000-0000-0000-000000000000'
    begin
    	select @subid = anonymous_subid from MSmerge_agents 
    			where publisher_id=@publisher_id and 
    				  publisher_db = @publisher_db and 
    				  publication = @publication and 
    				  subscriber_id = @subscriber_id 
    				  and subscriber_db = @subscriber_db
    	if @subid = '00000000-0000-0000-0000-000000000000'
    		select @subid = newid()
    	else
    		select @first_anonymous = 1   -- for Jet, schemaversion should not be 0 in this path.
    end
         
    IF NOT EXISTS (select * from MSmerge_agents where anonymous_subid=@subid)
    begin

    	if @first_anonymous <= 0   --only add agent entry for initial subscription only. 
    	begin
        	select @not_exist = 1

        	-- Generate a job GUID for remote agents. This will be used by the UI to uniquely
        	-- identify rows returned by the enums
        	set @merge_jobid = newid();

        	insert into MSmerge_agents (name, publisher_id, publisher_db, publication, 
            		    subscriber_id, subscriber_db, anonymous_subid, job_id, profile_id, subscriber_name)
	            	VALUES (convert(nvarchar(40), @subid), @publisher_id, @publisher_db, @publication, 
    	            	    @subscriber_id, @subscriber_db, @subid, @merge_jobid, @profile_id, @subscriber_name)
    	end
    	else
    		select @dropped  =1
    end
    
    select @agent_id = id, @agent_name = name from MSmerge_agents 
        where anonymous_subid=@subid  -- subid guarantees uniqueness
    
	if @by_pass = 0 --by pass the checking if retention is NULL or 0
	begin
    	select @min_valid_day = dateadd(day, -@retention, getdate())
    	
    	select Top 1 @last_status = runstatus, @last_history = time from MSmerge_history where agent_id = @agent_id
        			order by time DESC
        if @last_status = 6 and EXISTS (select * from MSmerge_history where agent_id = @agent_id and runstatus = 2) 
        		select Top 1 @last_history = time from MSmerge_history where agent_id = @agent_id and runstatus = 2
        				order by time DESC	
    	/*
    	** This anonymous subscription is gone for too long to be efficiently reconciled. Either reinitialization or
    	** re-deployment of this subscription is needed. Merge agent will fail.
    	*/
    	if @last_history < @min_valid_day and @first_anonymous <> 0	--do not check for re-initialized replicas.
        			select @expired = 1
   end

    select @agent_id, @agent_name, @expired where @dropped = 0 --return empty result set 
        
GO


raiserror(15339,-1,-1,'sp_MShelp_merge_agentid')
GO

CREATE PROCEDURE sp_MShelp_merge_agentid (
    @publisher_id       smallint,
    @publisher_db       sysname,
    @publication        sysname,
    @subscriber_id      smallint,
    @subscriber_db      sysname
)
AS
	declare @expired		int
    declare @agent_id 		int
    declare @name 			sysname
    declare @retention 		int
    declare @last_status	int
    declare @last_history	datetime
    declare @min_valid_day 	datetime
    declare @reinited 		int
    declare @status 		int
    declare @success		int
    declare @publication_id	int

    select @expired = 0
    select @reinited = 4
    select @success = 2

	if not EXISTS (select * from MSpublications 
		where publisher_id=@publisher_id 
		and publisher_db = @publisher_db
		and publication = @publication
		and publication_type = 2) -- merge publication is gone
	begin
		select 1, @publication, 1, 0 --third column = 1 means publication is gone, making the other values meanningless.
		return (1)
	end

   	select @publication_id = publication_id 
		from MSpublications
		where publisher_id = @publisher_id and
			  publisher_db = @publisher_db and
			  publication = @publication

	select @status = status from MSmerge_subscriptions where
		publisher_id = @publisher_id and 
        publisher_db = @publisher_db and 
        publication_id = @publication_id and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db

    select @agent_id = id, @name = name from MSmerge_agents 
        where publisher_id = @publisher_id 
        and publisher_db = @publisher_db
        and publication = @publication
        and subscriber_id = @subscriber_id
        and subscriber_db = @subscriber_db

    -- Security check. Do it here to let the agent fail at the beginning
    if @agent_id is not null
    begin
        exec dbo.sp_MScheck_pull_access @agent_id = @agent_id, @agent_type = 0 -- distribution agent
        select @retention = retention 
			from MSpublications 
			where publisher_id=@publisher_id and publisher_db=@publisher_db and publication=@publication 

		if @retention is not NULL and @retention > 0
		begin
			select @min_valid_day = dateadd(day, @retention * (-1), getdate()) 
			select Top 1 @last_status = runstatus, @last_history = time 
        			from MSmerge_history where agent_id = @agent_id
        				order by time DESC
        	if @last_status = 6 and EXISTS (select * from MSmerge_history where  agent_id = @agent_id and runstatus = 2) 
        		select Top 1 @last_history = time from MSmerge_history where agent_id = @agent_id and runstatus = 2
        						order by time DESC	
			if @last_history < @min_valid_day and @status <> @reinited
				select @expired = 1
		end
	end

select @agent_id, @name, 0, @expired where @agent_id is not NULL
GO

raiserror(15339,-1,-1,'sp_MSdistpublisher_cleanup')
GO

CREATE PROCEDURE sp_MSdistpublisher_cleanup 
@publisher sysname
as

    set nocount on
    declare @publisher_id smallint
    declare @job_id binary(16)
    declare @retcode int
    
    -- Delete agents
    -- Get the publisher id
    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end
    
    -- Dropping local jobs
    -- Use union in 'select'. Insensitive cursor will be used automatically.
    DECLARE hCagents CURSOR LOCAL FAST_FORWARD FOR
            SELECT job_id 
                FROM MSsnapshot_agents
                WHERE publisher_id = @publisher_id and local_job = 1
        UNION
            SELECT job_id
                FROM MSlogreader_agents
                WHERE publisher_id = @publisher_id and local_job = 1
        UNION
            SELECT job_id 
                FROM MSdistribution_agents
                WHERE publisher_id = @publisher_id and local_job = 1
        UNION
            SELECT job_id
                FROM MSmerge_agents
                WHERE publisher_id = @publisher_id  and local_job = 1
        FOR READ ONLY

    OPEN hCagents
    FETCH hCagents INTO @job_id
   
    WHILE (@@fetch_status <> -1)
    BEGIN
        IF EXISTS (SELECT * FROM msdb..sysjobs_view WHERE job_id = @job_id)
        BEGIN
            exec @retcode = msdb.dbo.sp_delete_job @job_id = @job_id
            if @retcode <> 0 or @@error <> 0
                return(1)
        END
        FETCH hCagents INTO @job_id 
    end

    -- Clean up the tables, including
    --  4 Agent tables
    --  2 subscription tables
    --  article table
    --  publication table
    --  2 subscriber table
    -- The order is to avoid breaking monitoring


    delete MSpublisher_databases where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSpublications where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSarticles where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSsubscriptions where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSmerge_subscriptions where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSsnapshot_agents where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 
    
    delete MSlogreader_agents where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSdistribution_agents where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSmerge_agents where publisher_id = @publisher_id
    if @@error <> 0 
        return (1) 

    delete MSsubscriber_info where UPPER(publisher) = UPPER(@publisher)
    if @@error <> 0 
        return (1) 

    delete MSsubscriber_schedule where UPPER(publisher) = UPPER(@publisher)
    if @@error <> 0 
        return (1) 

    delete MSpublication_access where not exists (select * from MSpublications p where
		p.publication_id = MSpublication_access.publication_id)
    if @@error <> 0 
        return (1) 

    delete MSrepl_originators where 
        not exists (select * from MSpublisher_databases p
            where p.id = MSrepl_originators.publisher_database_id )
    if @@error <> 0 
        return (1) 

    -- Force a refresh of the replication status temp table.
    if (select object_id('tempdb.dbo.MSreplication_agent_status')) is not NULL
        drop table tempdb.dbo.MSreplication_agent_status
GO

raiserror(15339,-1,-1,'sp_MSenum_replication_status')
GO

create proc sp_MSenum_replication_status
as

    select 'publisher' = srvname, 'publisher_db' = sa.publisher_db, 
        'publication' = sa.publication, 'publication_type' = sa.publication_type,
        'agent_type' = 1, 'status' = runstatus, 'agent_name' = sa.name from 
        MSsnapshot_history sh1, master..sysservers, MSsnapshot_agents sa
        where 
        sh1.timestamp = (select max(timestamp) from MSsnapshot_history sh2 where
            sh2.agent_id = sa.id) and
        srvid = sa.publisher_id 
    UNION
    select srvname, sa.publisher_db, sa.publication, sa.publication_type, 
        1, 0, sa.name from 
        master..sysservers, MSsnapshot_agents sa
        where 
        srvid = sa.publisher_id and
        not exists (select * from MSsnapshot_history sh where sh.agent_id = sa.id)
    UNION
    select srvname, la.publisher_db, 'ALL', 0, 2, runstatus, la.name from
        MSlogreader_history lh1, MSlogreader_agents la, master..sysservers
        where timestamp = (select max(timestamp) from 
            MSlogreader_history lh2 where
            lh2.agent_id = la.id) and
            srvid = la.publisher_id
    UNION
    select srvname, la.publisher_db, 'ALL', 0, 2, 0, la.name from
        MSlogreader_agents la, master..sysservers
        where
            srvid = la.publisher_id and
            not exists (select * from MSlogreader_history lh where lh.agent_id = la.id)
    UNION
    select srvname, da.publisher_db, da.publication, p.publication_type, 3, runstatus, da.name from 
            MSdistribution_history dh1, master..sysservers, 
            MSdistribution_agents da, MSpublications p, MSsubscriptions s
            where timestamp = (select max(timestamp) from 
                MSdistribution_history dh2 where
                dh2.agent_id = da.id) and
                srvid = da.publisher_id and
                s.agent_id = da.id and
                p.publication_id = s.publication_id
    UNION
    select srvname, da.publisher_db, da.publication, p.publication_type, 3, 0, da.name from 
            master..sysservers, 
            MSdistribution_agents da,
            MSpublications p,
            MSsubscriptions s
            where 
                srvid = da.publisher_id and
                not exists (select * from MSdistribution_history dh where dh.agent_id = da.id) and
                s.agent_id = da.id and
                p.publication_id = s.publication_id
    UNION	-- Load distribution dummy rows
    select srvname, da.publisher_db, p.publication, p.publication_type, 3, runstatus, da.name from 
            MSdistribution_history dh1, master..sysservers, 
            MSdistribution_agents da, MSpublications p, MSsubscriptions s
            where timestamp = (select max(timestamp) from 
                MSdistribution_history dh2 where
                dh2.agent_id = da.id) and
                srvid = da.publisher_id and
                s.agent_id = da.id and
                p.publication_id = s.publication_id
    UNION -- Load distrbution dummy_rows
    select srvname, da.publisher_db, p.publication, p.publication_type, 3, 0, da.name from 
            master..sysservers, 
            MSdistribution_agents da,
            MSpublications p,
            MSsubscriptions s
            where 
                srvid = da.publisher_id and
                not exists (select * from MSdistribution_history dh where dh.agent_id = da.id) and
                s.agent_id = da.id and
                p.publication_id = s.publication_id
    UNION
    select srvname, ma.publisher_db, ma.publication, 2, 4, runstatus, ma.name from 
        MSmerge_history mh1, master..sysservers, MSmerge_agents ma
        where timestamp = (select max(timestamp) from
            MSmerge_history mh2 where
            mh2.agent_id = ma.id) and
            srvid = ma.publisher_id 

    UNION
    select srvname, ma.publisher_db, ma.publication, 2, 4, 0, ma.name from 
        master..sysservers, MSmerge_agents ma
        where 
            srvid = ma.publisher_id and
            not exists (select * from MSmerge_history mh where mh.agent_id = ma.id)


    order by publisher, publisher_db, publication

go

raiserror(15339,-1,-1,'sp_MSagent_stethoscope')
GO
CREATE PROCEDURE sp_MSagent_stethoscope
@heartbeat_interval int = 10  --minutes
as
    declare @current_time datetime
    declare @agent_name nvarchar(100)
    declare @agent_id int
    declare @job_id binary(16)
    declare @start_time datetime
    declare @duration int
    declare @comments nvarchar(255)
    declare @publisher_id smallint
    declare @publisher sysname
    declare @publisher_db sysname
    declare @heartbeat_failure bit

    set nocount on

    set @heartbeat_failure = 0
    set @current_time = getdate()

    -- Construct suspect message
    set @comments = formatmessage(20554, @heartbeat_interval)

    -- If a running snapshot agent has not logged a history message within the specified
    -- heartbeat_interval then raise a agent suspect error
    declare hC_snapshot_suspect CURSOR LOCAL FAST_FORWARD for 
        select sh1.agent_id, sh1.start_time from MSsnapshot_history sh1 where
            (sh1.runstatus = 1 or sh1.runstatus = 3 or sh1.runstatus = 4) and
            dateadd(minute, @heartbeat_interval, sh1.time) < @current_time and
            sh1.timestamp = (select max(timestamp) from MSsnapshot_history where
                agent_id= sh1.agent_id)
        for read only

    open hC_snapshot_suspect
    fetch hC_snapshot_suspect into @agent_id, @start_time
    while (@@fetch_status <> -1)
    begin

        set @heartbeat_failure = 1

        -- Get the agent name
        select @agent_name = name, @job_id = job_id from MSsnapshot_agents where id = @agent_id

        -- Log a "No action" message on behalf of the agent
        exec dbo.sp_MSadd_snapshot_history
            @agent_id = @agent_id,
            @runstatus = 6,     -- Failure status
            @comments = @comments,
			@do_raiserror = 0

        fetch hC_snapshot_suspect into @agent_id, @start_time
    end
    close hC_snapshot_suspect
    deallocate hC_snapshot_suspect


    -- If a running logreader agent has not logged a history message within the specified
    -- heartbeat_interval then raise a agent suspect error
    declare hC_logreader_suspect CURSOR LOCAL FAST_FORWARD for 
        select la.id, sh1.start_time from MSlogreader_agents la, MSlogreader_history sh1 where
            (sh1.runstatus = 1 or sh1.runstatus = 3 or sh1.runstatus = 4) and
            dateadd(minute, @heartbeat_interval, sh1.time) < @current_time and
            sh1.timestamp = (select max(timestamp) from MSlogreader_history where
                agent_id= sh1.agent_id) and
            la.id = sh1.agent_id
        for read only

    open hC_logreader_suspect
    fetch hC_logreader_suspect into @agent_id, @start_time
    while (@@fetch_status <> -1)
    begin
        set @heartbeat_failure = 1

        -- Get the agent name
        select @agent_name = name, @job_id = job_id from MSlogreader_agents where id = @agent_id

        -- Log a "No action" message on behalf of the agent
        select @publisher_id = publisher_id, @publisher_db = publisher_db from MSlogreader_agents where id = @agent_id
        select @publisher = srvname from master..sysservers where srvid = @publisher_id
        exec dbo.sp_MSadd_logreader_history
            @agent_id = @agent_id,
            @runstatus = 6,     -- Failure status
            @comments = @comments,
			@do_raiserror = 0

        fetch hC_logreader_suspect into @agent_id, @start_time
    end
    close hC_logreader_suspect
    deallocate hC_logreader_suspect

    -- If a running distribution agent has not logged a history message within the specified
    -- heartbeat_interval then raise a agent suspect error
    declare hC_distribution_suspect CURSOR LOCAL FAST_FORWARD for 
        select sh1.agent_id, sh1.start_time from MSdistribution_history sh1 where
            (sh1.runstatus = 1 or sh1.runstatus = 3 or sh1.runstatus = 4) and
            dateadd(minute, @heartbeat_interval, sh1.time) < @current_time and
            sh1.timestamp = (select max(timestamp) from MSdistribution_history where
                agent_id= sh1.agent_id)
        for read only

    open hC_distribution_suspect
    fetch hC_distribution_suspect into @agent_id, @start_time
    while (@@fetch_status <> -1)
    begin
        set @heartbeat_failure = 1

        -- Get the agent name
        select @agent_name = name, @job_id = job_id from MSdistribution_agents where id = @agent_id

        -- Log a "No action" message on behalf of the agent
        exec dbo.sp_MSadd_distribution_history
            @agent_id = @agent_id,
            @runstatus = 6,     -- Failure status
            @comments = @comments,
			@do_raiserror = 0

        fetch hC_distribution_suspect into @agent_id, @start_time
    end
    close hC_distribution_suspect
    deallocate hC_distribution_suspect

    -- If a running merge agent has not logged a history message within the specified
    -- heartbeat_interval then raise a agent suspect error
    declare hC_merge_suspect CURSOR LOCAL FAST_FORWARD for 
        select sh1.agent_id, sh1.start_time from MSmerge_history sh1 where
            (sh1.runstatus = 1 or sh1.runstatus = 3 or sh1.runstatus = 4) and
            dateadd(minute, @heartbeat_interval, sh1.time) < @current_time and
            sh1.timestamp = (select max(timestamp) from MSmerge_history where
                agent_id= sh1.agent_id)
        for read only

    open hC_merge_suspect
    fetch hC_merge_suspect into @agent_id, @start_time
    while (@@fetch_status <> -1)
    begin
        set @heartbeat_failure = 1

        -- Get the agent name
        select @agent_name = name, @job_id = job_id from MSmerge_agents where id = @agent_id

        -- Log a "No action" message on behalf of the agent
        exec dbo.sp_MSadd_merge_history
            @agent_id = @agent_id,
            @runstatus = 6,     -- Failure status
            @comments = @comments,
			@do_raiserror = 0

        fetch hC_merge_suspect into @agent_id, @start_time
    end
    close hC_merge_suspect
    deallocate hC_merge_suspect

    -- Log all is fine message
    if @heartbeat_failure = 0
        -- "Detected heartbeat for all running Replication Agents"
		set @comments = formatmessage(20556)    
	else
        -- "Could not detected heartbeat for all running Replication Agents"
		set @comments = formatmessage(20580)    

    raiserror (@comments, 10, -1)
GO
raiserror(15339,-1,-1,'sp_MSlock_distribution_agent')
GO

CREATE PROCEDURE sp_MSlock_distribution_agent (
    @id int,
    @mode int = 1 -- 0: shared  1: exclusive
) AS


    SET         NOCOUNT ON
    DECLARE     @active tinyint
    declare     @count  int
    select @active = 2
    if @mode = 0
        select @count = count(*) from MSsubscriptions (ROWLOCK REPEATABLEREAD) where agent_id = @id and status = @active
    else
        select @count = count(*) from MSsubscriptions (ROWLOCK UPDLOCK) where agent_id = @id and status = @active
GO

raiserror(15339,-1,-1,'sp_MSdetect_nonlogged_shutdown')
GO
create proc sp_MSdetect_nonlogged_shutdown
@subsystem nvarchar(60),
@agent_id int
as
    declare @job_id binary(16)
    declare @agent_name nvarchar(100)
    declare @message nvarchar(1024)
    declare @retcode int
    declare @runstatus int

    -- Detect if the agent was shutdown without a logged reason
    if UPPER(@subsystem) = 'SNAPSHOT'
    begin
        if exists (select runstatus from MSsnapshot_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC       runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSsnapshot_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSsnapshot_agents where id = @agent_id
            end
    end
    else if UPPER(@subsystem) = 'LOGREADER'
    begin
        if exists (select runstatus from MSlogreader_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC           runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSlogreader_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSlogreader_agents where id = @agent_id
            end
    end
    else if UPPER(@subsystem) = 'DISTRIBUTION'
    begin
        if exists (select runstatus from MSdistribution_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC           runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSdistribution_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSdistribution_agents where id = @agent_id
            end
    end
    else if UPPER(@subsystem) = 'MERGE'
    begin
        if exists (select runstatus from MSmerge_history where 
            agent_id = @agent_id and
            runstatus <> 2 and 
--CAC           runstatus <> 5 and 
            runstatus <> 6 and
            timestamp = (select max(timestamp) from MSmerge_history where agent_id = @agent_id))
            begin
                select @job_id = job_id, @agent_name = name from MSmerge_agents where id = @agent_id
            end
    end

    -- If no job_id assume shutdown was logged properly
    if @job_id is null
        return 0

    -- Get last message from SQL Agent History table
    create table #JobHistory (
        instance_id int NOT NULL, 
        job_id uniqueidentifier NOT NULL,
        job_name nvarchar(100) NOT NULL,
        step_id int NOT NULL,
        step_name nvarchar(100) NOT NULL, 
        sql_message_id int NOT NULL,
        sql_severity int NOT NULL,
        message nvarchar(1024) NOT NULL,
        run_status int NOT NULL,
        run_date int NOT NULL,
        run_time int NOT NULL,
        run_duration int NOT NULL,
        operator_emailed sysname NULL,
        operator_netsent sysname NULL,
        operator_paged sysname NULL,
        retries_attempted int NOT NULL,
        server sysname NOT NULL
    )
    if @@error <> 0
        return 1

    -- Insert last history for step_id 2 (Agent running)
    set rowcount 1
    insert into #JobHistory exec msdb.dbo.sp_help_jobhistory @job_id = @job_id, @step_id = 2, 
        @mode = 'FULL'          

    -- Get the last history
    select @message = message, @runstatus = run_status from #JobHistory

    -- Reset rowcount
    set rowcount 0

    -- Map SQL Agent runstatus to Replication runstatus
    set @runstatus = 
    case @runstatus
        when 0 then 6   -- Fail mapping
        when 1 then 2   -- Success mapping
        when 2 then 5   -- Retry mapping
        when 3 then 2   -- Shutdown mapping
        when 4 then 3   -- Inprogress mapping
        when 5 then 0   -- Unknown is mapped to never run
    end

    -- If no message, provide a default message
    if @message is null
        select @message = formatmessage(20557, @agent_name)

    if UPPER(@subsystem) = 'SNAPSHOT'
        exec @retcode = dbo.sp_MSadd_snapshot_history @agent_id = @agent_id, @runstatus = @runstatus,
                @comments = @message
    else if UPPER(@subsystem) = 'LOGREADER'
        exec @retcode = dbo.sp_MSadd_logreader_history @agent_id = @agent_id, @runstatus = @runstatus,
                @comments = @message
    else if UPPER(@subsystem) = 'DISTRIBUTION'
        exec @retcode = dbo.sp_MSadd_distribution_history @agent_id = @agent_id, @runstatus = @runstatus,
                @comments = @message
    else if UPPER(@subsystem) = 'MERGE'
        exec @retcode = dbo.sp_MSadd_merge_history @agent_id = @agent_id, @runstatus = @runstatus,
                @comments = @message

    if @@error <> 0 or @retcode <> 0
        return 1

    drop table #JobHistory

GO

raiserror(15339,-1,-1,'sp_MSpublication_access')
GO

CREATE PROCEDURE sp_MSpublication_access (
    @publisher sysname,
    @publisher_db sysname = NULL,
    @publication sysname = NULL,
    @login sysname = NULL,
    @operation nvarchar(20), -- Can be add/drop/check/help/get_publications/get_logins
    @has_access bit = 0 output, -- Used only in check
    @skip bit = 0
        ) AS
-- This sp can be called repeatedly.
    
    declare @publisher_id smallint
    declare @retcode int
    declare @publication_id int
    declare @isntname bit
    declare @server_access bit
	declare @privilege nchar(21)

    -- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Check to see if the login exists and if the login is NT login
    select @isntname = isntname from master..syslogins where
        sid = suser_sid(@login) and
		hasaccess = 1

    -- Get the publication id 
    select @publication_id = publication_id from MSpublications where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication

    -- Publication does not exist
    if @publication_id is null and
		@operation in ('add','drop','check','help')
    begin
        raiserror (20026, 16, -1, @publication)
        return (1)
    end

    if @operation = 'add'
    begin
        -- Login does not have access
        if @isntname is null
        begin
            if @skip = 0
            begin
                raiserror(21048, 16, -1, @login, @@servername) 
                return (1)
            end
            else
                return (0)
        end

        -- Add login to the access list if it does not exist
        if not exists (select * from MSpublication_access where
            publication_id = @publication_id and
            login = @login)
        begin
            insert MSpublication_access (publication_id, login)
                values (@publication_id,@login)
            if  @@error <> 0
                return(1)
        end
    end
    else if @operation = 'drop'
    begin
		if @isntname is null and not exists (select * from MSpublication_access where
                publication_id = @publication_id and
                login = @login) 
		begin
			raiserror(15007,10,-1,@login)
			return 1
		end
        -- Do check existense when dropping since the login might be dropped
        -- outside replication already.
        delete MSpublication_access where
                publication_id = @publication_id and
                login = @login
        if  @@error <> 0
            return(1)
    end
    else if @operation = 'check'
    begin
		-- Cover the case when
		-- the NT user is not in syslogins
		if @isntname is null and suser_sid(@login) is not null
			select @isntname = 1

        -- If the login is valid NT login, call set user
        if @isntname = 1
        begin
            setuser @login
            if @@error <> 0
                return(1)
        end
        if exists (select * from MSpublication_access l where
            publication_id = @publication_id and
            (l.login = @login or (@isntname = 1 and exists (select * from master..syslogins
                where sid = suser_sid(l.login) and isntgroup = 1 and is_member(l.login) = 1))))
            set @has_access = 1
        else
            set @has_access = 0
        if @isntname = 1
        begin
            setuser
            if @@error <> 0
                return(1)
        end        
    end
    else if @operation = 'help'
    begin
        select login from MSpublication_access where
                    publication_id = @publication_id and
                    login like @login
    end
    else if @operation = 'get_publications'
    begin
		-- Avoid setuser failure and cover the case of that 
		-- the NT user is not in syslogins
		if @isntname is null and suser_sid(@login) is not null
		begin
			exec @retcode = master.dbo.xp_logininfo
				@login, N'all', @privilege output
			if @privilege is not null
				select @isntname = 1
		end

        -- If the login is valid NT login, call set user
        if @isntname = 1
        begin
            setuser @login
            -- If the login is invalid, don't return any result.
            if @@error <> 0
                return(0)
        end
        select p.publisher_db, p.publication from MSpublication_access l,
            MSpublications p where
            -- Get all the non default publications that have the login in the access list
            (
            -- If login match or there is an NT group in the access list and @login is integrated
            (l.login = @login or ( @isntname = 1 and exists (select * from master..syslogins
                where sid = suser_sid(l.login) and isntgroup = 1 and is_member(l.login) = 1))) and
            l.publication_id = p.publication_id and
			p.publisher_id = @publisher_id
            ) 
        if @isntname = 1
        begin
            setuser
            if @@error <> 0
                return(1)
        end        
    end
    else if @operation = 'get_logins'
    begin
        select loginname from master..syslogins where
			hasaccess = 1
    end
GO

raiserror(15339,-1,-1,'sp_MScheck_pull_access')
GO

CREATE PROCEDURE sp_MScheck_pull_access (
    @agent_id int = 0,
    @agent_type int = 0, -- 0 is tran; 1 is merge
    @publication_id int = 0
        ) AS

    declare @retcode int

    -- sysadmin or db_owner have access
    if is_srvrolemember('sysadmin') = 1 or 
        is_member('db_owner') = 1
        return 0

    -- Need login_time to uniquely identify a connection.
    declare @login_time datetime
    select @login_time = login_time from master..sysprocesses where spid = @@spid

    -- For merge change to use publication_id
    if @agent_id <> 0 and @agent_type = 1
    begin
        select @publication_id = p.publication_id from
                MSmerge_agents a, MSpublications p where
                a.id = @agent_id and
                a.publisher_id = p.publisher_id and
                a.publisher_db = p.publisher_db and
                a.publication = p.publication
        select @agent_id = 0
    end

    -- In cache, return success
    if exists (select * from tempdb.dbo.MSdistributor_access where 
        spid = @@spid and
        login_time = @login_time and
        db_id = db_id() and
        (publication_id = @publication_id and
        agent_id = @agent_id and
        agent_type = @agent_type) or
        -- All 0s is used by sp_MSadd_repl_error, which just require the 
        -- login to be in cache regardless of the publication id and agent_id.
        -- This means that once a agent get into the distribution db, it
        -- can add any error.
        (@publication_id = 0 and @agent_id = 0 and @agent_type = 0))
        return (0)

    -- Cover sp_MSadd_repl_error case
    if @publication_id = 0 and @agent_id = 0 and @agent_type = 0
    begin
        RAISERROR (14126, 11, -1)
        return (1)
    end

    declare @isntname bit
    declare @offensive_pub_id int

    -- Check to see if the login is NT login
    select @isntname = isntname from master..syslogins where
        sid = suser_sid()
    -- If the login does not exists, check to see if the login is a NT login that
    -- has access to the server.
    if @isntname is null
    begin
        -- If it is an NT login
        if suser_sid() is not null
            select @isntname = 1
        -- If it is not an NT login
        else
            select @isntname = 0
    end

    -- Check security based on agent_id
    -- We know it is distribution agent. See above.
    if @agent_id <> 0 
    begin
        select top 1 @offensive_pub_id = s.publication_id from MSsubscriptions s where 
            s.agent_id = @agent_id and
            not exists (select * from MSpublication_access l where
                l.publication_id = s.publication_id and
			-- Current login has no access
            (suser_sid(l.login) = suser_sid() or (@isntname = 1 and exists 
                (select * from master..syslogins where 
                    sid = suser_sid(l.login) and isntgroup = 1 and is_member(l.login) = 1))))
        
        if @offensive_pub_id is not null
            goto NO_ACCESS
    end
    -- Check security based on publication_id
    else
    begin
        declare @publisher_id smallint
    
        if not exists (select * from MSpublication_access l where
            l.publication_id = @publication_id and
            (suser_sid(l.login) = suser_sid() or (@isntname = 1 and exists 
                (select * from master..syslogins where 
                    sid = suser_sid(l.login) and isntgroup = 1 and is_member(l.login) = 1))))
        begin
            select @offensive_pub_id = @publication_id
            goto NO_ACCESS
        end
    end

    -- If we are here, we know that the connection has access and is not in the cache
    -- add it in to the cache.
        
    -- Clear the cache to keep it small.
    exec @retcode = dbo.sp_MSflush_access_cache
    if @retcode <> 0 or @@error <> 0
        return (1)
        

    insert tempdb.dbo.MSdistributor_access
        (spid, db_id, agent_id, agent_type, publication_id, login_time) values
        (@@spid, db_id(), @agent_id, @agent_type, @publication_id, @login_time)

    if @@error <> 0
        return (1)

    return (0)

NO_ACCESS:

    -- We don't have access if we reach here, return error
    declare @login sysname
    select @login = suser_sname(suser_sid())
    declare @publication sysname
    select @publication = publication from MSpublications where publication_id = @offensive_pub_id
    raiserror(21049, 16, -1, @login, @publication);
    return(1)

GO

raiserror(15339,-1,-1,'sp_MSdrop_6x_publication')
GO

-- This stored procedure is called by sp_droptask when 6.x publications are dropped.
create procedure sp_MSdrop_6x_publication
@job_id UNIQUEIDENTIFIER
as
    declare @publisher sysname
    declare @publisher_db sysname
    declare @publication sysname
    declare @retcode int

    -- Get publication information
    select @publisher = srvname, @publisher_db = publisher_db, @publication = publication from 
        MSsnapshot_agents, master..sysservers where
        job_id = @job_id and
        srvid = publisher_id

    -- Remove the publication and snapshot agent
    exec @retcode = dbo.sp_MSdrop_publication
        @publisher = @publisher,
        @publisher_db = @publisher_db,
        @publication = @publication 
    if @@ERROR <> 0 or @retcode <> 0
        return 1
GO

raiserror(15339,-1,-1,'sp_MShelp_distribution_agentid')
GO

CREATE PROCEDURE 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

as

    set nocount on

	declare @last_status int
	declare @expired int
	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 @retention int
    declare @success int
	declare @last_sync datetime
    select @anonymous = 2
    select @success = 2
    select @expired = 0
    
    select  top 1
        @third_party_flag = thirdparty_flag,
        @independent_agent = independent_agent,
        @retention = retention
        from MSpublications where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        (publication = @publication or @publication is null)

    if @subscription_type = @anonymous
    begin
        exec @retcode = dbo.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
        if @@error <> 0 or @retcode <> 0
            return (1)

        select @sub_agent_id = anonymous_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

        select @sub_agent_id = @agent_id
    end

    -- Note: can not fail if publication not exists but agent exists
    -- It is an upgrade case. 
    if @third_party_flag is null and @agent_id is null
    begin
        -- The publication(s) does not exist
        RAISERROR (21073, 16, -1)
        return(1)
    end

    if @agent_id is null
    begin
        -- If the specified publication name is in MSpublications table then
        -- the publication is configured to use a non-independent distribution
        -- agent. Raise a different error if this is the case
        if @independent_agent = 0 and lower(@publication) <> N'all' and
            @publication is not null and 
            exists (select * from MSdistribution_agents 
                    where publication = N'ALL' and 
                          publisher_id = @publisher_id and
                          publisher_db = @publisher_db and
                          subscription_type = @subscription_type and
                          subscriber_id = @subscriber_id and
                          subscriber_db = @subscriber_db)  
        begin
            RAISERROR (21133, 16, -1, @publication)
        end
        else
        begin
            -- Invalid subscription
            RAISERROR (21056, 16, -1, @publication)
        end
        return(1)
    end

    -- It is an upgrade case. 
    if @third_party_flag is null 
        select @third_party_flag = 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())
 				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 MSsubscriptions s where
        agent_id = @sub_agent_id

    /* xact_seqno for snapshot trans are longer for native publishers*/
    if @third_party_flag = 0
    begin
        if @xact_seqno_length = 8 or @xact_seqno_length = 10
            select @xact_seqno_length = @xact_seqno_length + 4
    end

    -- Security check. Do it here to let the agent fail at the beginning
    exec @retcode = dbo.sp_MScheck_pull_access
        @agent_id = @sub_agent_id,
        @agent_type = 0 -- distribution agent
    if @@error <> 0 or @retcode <> 0
        return (1)

    select 
        'xact_seqno_length' = @xact_seqno_length,
        'agent_id' = @agent_id,
        'agent_name' = name,
        'anonymous subid' = anonymous_subid,
        'expired ' = @expired
        from MSdistribution_agents where id = @agent_id

GO

raiserror(15339,-1,-1,'sp_MSmarkreinit')
GO
CREATE PROCEDURE sp_MSmarkreinit
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@subscriber sysname,       
@subscriber_db sysname,
@reset_reinit	int
AS
	set nocount on

    declare @publisher_id smallint
    declare @subscriber_id smallint
    declare @publication_id int
    declare @retcode int
	declare @reinited int
	declare @unreinited int
	declare @status int
 
	select @reinited = 4  -- status=4 means this subscription has been reinited
	select @unreinited = 1 -- status=1 means this subscription not reinited

	if @reset_reinit = 1
		select @status = @reinited
	else
		select @status = @unreinited
	
-- Check if publisher is a defined as a distribution publisher in the current database
    exec @retcode = dbo.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
    if @retcode <> 0
    begin
        return(1)
    end

    -- Get the publication information 
    select @publication_id = publication_id
		from MSpublications where 
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication
    if @publication_id is NULL
    begin
        raiserror (20026, 11, -1, @publication)
        return (1)
    end

    -- Get subscriber info
    select @subscriber_id = srvid from master..sysservers where UPPER(srvname) = UPPER(@subscriber)

	update MSmerge_subscriptions set status = @status where
		publisher_id = @publisher_id and 
        publisher_db = @publisher_db and 
        publication_id = @publication_id and
        subscriber_id = @subscriber_id and
        subscriber_db = @subscriber_db
    if @@error<>0 
    	return (1)
GO    	



raiserror(15339,-1,-1,'sp_MSreinit_subscription')
GO
CREATE PROCEDURE sp_MSreinit_subscription
@publisher_name sysname,
@publisher_db sysname,
@publication sysname = 'all',
@subscriber_name    sysname = 'all',
@subscriber_db sysname = 'all'
as
    set nocount on
    declare @proc nvarchar(2048)
    declare @retcode int

    if @publisher_name IS NULL or NOT EXISTS (select * from master..sysservers as ss, MSpublications as msp
        where lower(ss.srvname) = lower(@publisher_name) and msp.publisher_id = ss.srvid)
    begin
        return (1)
    end

    if @publisher_db IS NULL
        select @publisher_db = ''

    select @proc = @publisher_name + '.' + @publisher_db + '.dbo.sp_reinitsubscription '
    exec @retcode = @proc @publication, 'all', @subscriber_name, @subscriber_db

    return (@retcode)
go

   -- View for delivered and undelivered commands.   RHS  6-4-98
   -- Since the view is likely to change, just recreate it each time.
   IF EXISTS (SELECT * FROM sysobjects where name='MSdistribution_status' and type='V')
    DROP VIEW dbo.MSdistribution_status

    /****************************************************************************/
    raiserror('Creating view MSdistribution_status', 0,1)
        /****************************************************************************/    
go

CREATE VIEW MSdistribution_status (article_id,agent_id,UndelivCmdsInDistDB,DelivCmdsInDistDB)
as
-- Note that this view does not account for (i.e. exclude from counts) commands that do not need to be delivered 
-- because of loopback or syncronous updating subscribers, nor subscriptions never activated.
-- It also may not be exact due to use of NOLOCK - so that it does not cause blocking or deadlock issues.
SELECT t.article_id,s.agent_id,
'UndelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno > h.maxseq THEN 1 ELSE 0 END),
'DelivCmdsInDistDB'=SUM(CASE WHEN xact_seqno <=  h.maxseq THEN 1 ELSE 0 END)
FROM	(SELECT	article_id,publisher_database_id, xact_seqno
	FROM MSrepl_commands (NOLOCK) ) as t
JOIN (SELECT agent_id,article_id,publisher_database_id FROM MSsubscriptions (NOLOCK) ) AS s 
ON (t.article_id = s.article_id AND t.publisher_database_id=s.publisher_database_id )
JOIN (SELECT agent_id,'maxseq'= isnull(max(xact_seqno),0x0) FROM MSdistribution_history (NOLOCK) GROUP BY agent_id) as h
ON (h.agent_id=s.agent_id)
GROUP BY t.article_id,s.agent_id
go
    
EXEC dbo.sp_MS_marksystemobject 'MSdistribution_status'
-- As this view can add considerable overhead when queried, it intentionally is not granted public access by default.
-- A site may so grant it if it wants to of course>
GRANT SELECT ON MSdistribution_status to dbo
go


raiserror(15339,-1,-1,'sp_browsereplcmds')
GO

create procedure sp_browsereplcmds 
@xact_seqno_start nchar(22) = NULL, 
@xact_seqno_end nchar(22) = NULL,
@originator_id int = NULL,
@publisher_database_id int = NULL,
@article_id int = NULL,
@command_id int = NULL
as
declare @query nvarchar( 4000 )
declare @dbname sysname

if( @command_id is not null )
begin
    if( @xact_seqno_start is null or @xact_seqno_end is null or @originator_id is null or @publisher_database_id is null or @article_id is null )
    begin
        raiserror( 21110, 16, -1 )
        return 1
    end
    else if @xact_seqno_start != @xact_seqno_end 
    begin
        raiserror( 21109, 16, -1 )
        return 1
    end
end

if @xact_seqno_start is null
begin
    select @xact_seqno_start = N'0x00000000000000000000'
end
if @xact_seqno_end is null
begin
    select @xact_seqno_end = N'0xFFFFFFFFFFFFFFFFFFFF'
end

select @query = N'select xact_seqno, convert( int, originator_id ), convert( int, publisher_database_id ), convert(int, article_id), convert( int, type ) & 32767, convert( int, partial_command ), command from MSrepl_commands '

if @command_id is not null
begin
    select @query = @query + N'where xact_seqno = ' + @xact_seqno_start  
end
else
begin
    select @query = @query + N'where xact_seqno >= ' + @xact_seqno_start + N' and xact_seqno <= ' + @xact_seqno_end 
end

if @originator_id is not null
begin
    select @query = @query + N' and originator_id = ' + convert( nvarchar, @originator_id )
end

if @publisher_database_id is not null
begin
    select @query = @query + N' and publisher_database_id = ' + convert( nvarchar, @publisher_database_id )
end

if @article_id is not null
begin
    select @query = @query + N' and article_id = ' + convert( nvarchar, @article_id )
end

if @command_id is not null
begin
    select @query = @query + N' and command_id >= ' + convert( nvarchar, @command_id )
    select @query = @query + N' and command_id <= ( select min( command_id ) from MSrepl_commands '
    select @query = @query + N' where xact_seqno = ' + @xact_seqno_start  
    select @query = @query + N' and originator_id = ' + convert( nvarchar, @originator_id )
    select @query = @query + N' and publisher_database_id = ' + convert( nvarchar, @publisher_database_id )
    select @query = @query + N' and article_id = ' + convert( nvarchar, @article_id ) 
    select @query = @query + N' and command_id >= ' + convert( nvarchar, @command_id )
    select @query = @query + N' and partial_command = 0 )'  
end

select @query = @query + N' order by originator_id, publisher_database_id, xact_seqno, article_id, command_id asc'

select @dbname = db_name()

exec master..xp_printstatements @query, @dbname
go



raiserror(15339,-1,-1,'sp_dumpparamcmd')
GO

create procedure sp_dumpparamcmd
@originator_id int,
@publisher_database_id int,
@article_id int,
@xact_seqno nchar(22)
as
declare @query nvarchar(2048)
declare @dbname sysname

select @query = N'select convert(int, partial_command) , command from MSrepl_commands '
select @query = @query + 'where type = 30 and xact_seqno = ' + @xact_seqno
select @query = @query + N' and originator_id = ' + convert( nvarchar, @originator_id )
select @query = @query + N' and publisher_database_id = ' + convert( nvarchar, @publisher_database_id )
select @query = @query + N' and article_id = ' + convert( nvarchar, @article_id )
select @query = @query + N' order by command_id asc'
select @dbname = db_name()

exec master..xp_displayparamstmt @query, @dbname, 1
go


/****************************************************************************/
print ''
print 'Adding user ''guest''.'
print ''
/****************************************************************************/
if not exists (select * from sysusers where
	name = N'guest' and
	hasdbaccess = 1)
	EXEC  dbo.sp_adduser 'guest'


grant execute on dbo.sp_MSdistribution_counters to public
GO 
grant execute on dbo.sp_MShelp_profile to public
GO
 
/* 
Stored procedures called by dist publisher.
distributor_admin will be added to db_owner role. Members of
db_owner have permission on all the stored procedures. 

-- Stored procedure used by remote publisher RPC
grant execute on dbo.sp_MSadd_publication to db_owner
grant execute on dbo.sp_MSchange_publication to db_owner
grant execute on dbo.sp_MSdrop_publication to db_owner
grant execute on dbo.sp_MSadd_article to db_owner
grant execute on dbo.sp_MSchange_article to db_owner
grant execute on dbo.sp_MSdrop_article to db_owner
grant execute on dbo.sp_MSadd_subscriber_info to db_owner
grant execute on dbo.sp_MSupdate_subscriber_info to db_owner
grant execute on dbo.sp_MSadd_subscriber_schedule to db_owner
grant execute on dbo.sp_MSupdate_subscriber_schedule to db_owner
grant execute on dbo.sp_MShelp_subscriber_info to db_owner
grant execute on dbo.sp_MSdrop_subscriber_info to db_owner
grant execute on dbo.sp_MSadd_snapshot_agent to db_owner
grant execute on dbo.sp_MSdrop_snapshot_agent to db_owner
grant execute on dbo.sp_MSadd_logreader_agent to db_owner
grant execute on dbo.sp_MSdrop_logreader_agent to db_owner
grant execute on dbo.sp_MSreset_subscription to db_owner
 
-- Stored procedure used by snapshot and logreader agents
grant execute on dbo.sp_MSadd_repl_command to db_owner
grant execute on dbo.sp_MSadd_repl_commands10 to db_owner
grant execute on dbo.sp_MSadd_repl_commands27 to db_owner
grant execute on dbo.sp_MSadd_repl_commands27hp to db_owner
grant execute on dbo.sp_MSadd_repl_commands27hp6x to db_owner
grant execute on dbo.sp_MSget_last_transaction to db_owner
grant execute on dbo.sp_MSset_snapshot_xact_seqno to db_owner
grant execute on dbo.sp_MSadd_snapshot_history to db_owner
grant execute on dbo.sp_MSadd_logreader_history to db_owner
grant execute on dbo.sp_MSdist_activate_auto_sub to db_owner
grant execute on dbo.sp_MSlock_auto_sub to db_owner
grant execute on dbo.sp_MSget_new_xact_seqno to db_owner
grant execute on dbo.sp_MSactivate_subscriptions to db_owner
grant execute on dbo.sp_MShelp_snapshot_agentid to db_owner
grant execute on dbo.sp_MShelp_logreader_agentid to db_owner
grant execute on dbo.sp_MSadd_subscription to public
grant execute on dbo.sp_MSupdate_subscription to public
grant execute on dbo.sp_MSdrop_subscription to public
grant execute on dbo.sp_MSadd_merge_subscription to public
grant execute on dbo.sp_MSdrop_merge_subscription to public

*/



-- Stored procedures used by dist and merge agents. They are granted to public

grant execute on dbo.sp_MSmarkreinit to public
go

-- Procedures to retrieve actual data. Need security check inside
grant execute on dbo.sp_MSget_repl_commands to public
GO
grant execute on dbo.sp_MSget_repl_cmds_anonymous to public
GO

-- Procedures to modify meta data. Need security check inside.
grant execute on dbo.sp_MSadd_distribution_history to public
GO
grant execute on dbo.sp_MSadd_repl_error to public
GO
grant execute on dbo.sp_MSadd_repl_alert to public
GO
grant execute on dbo.sp_MSadd_replmergealert to public
GO
grant execute on dbo.sp_MSadd_merge_history to public
GO
grant execute on dbo.sp_MSadd_anonymous_agent to public
GO
grant execute on dbo.sp_MSadd_merge_anonymous_agent to public
GO

-- Procedures to retrieve meta data. No security check inside.
grant execute on dbo.sp_MSget_subscription_guid to public
GO
grant execute on dbo.sp_MSanonymous_status to public
GO
grant execute on dbo.sp_MSsubscription_status to public
GO
grant execute on dbo.sp_MShelp_merge_agentid to public
GO
grant execute on dbo.sp_MScheckretention to public
GO
grant execute on dbo.sp_MShelp_distribution_agentid to public
GO


declare @dbname sysname
select  @dbname = db_name()
execute('dump transaction ' +@dbname+ ' with no_log')
go
checkpoint

go

exec dbo.sp_MS_upd_sysobj_category 2  --Now do catalog updates.

go

EXEC dbo.sp_configure 'allow updates', 0
GO

reconfigure with override
GO
-- - ----
