ate float
			,@perfmon_delivery_rate int
			,@perfmon_conflict_count int
			,@delivered_rows int
			,@changes int
			,@delivery_time_old  int
			,@delivered_rows_old int
			,@publisher_insertcount_old  int 
			,@publisher_updatecount_old  int 
			,@publisher_deletecount_old  int 
			,@publisher_conflictcount_old int 
			,@subscriber_insertcount_old int 
			,@subscriber_updatecount_old int 
			,@subscriber_deletecount_old int 
			,@subscriber_conflictcount_old int 
			,@publisher_id smallint
			,@subscriber_id smallint
			,@error_id int 
			,@startup int
			,@succeed int
			,@retry int
			,@inprogress int
			,@failure int
			,@idle int
			,@lastrow_timestamp timestamp
			,@agent_name nvarchar(100)
			,@publisher sysname
			,@publisher_db sysname
			,@publication sysname
			,@retcode int
			,@existing_row_updateble bit
			,@this_row_updateable bit
			,@agentclassname sysname
			,@lastrunstatus int

	-- 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)

	/* 
	** Status const defined in sqlrepl.h 
	*/
	select @startup = 1
			,@succeed = 2
			,@inprogress = 3
			,@idle = 4
			,@retry = 5
			,@failure = 6
			,@delivery_time_old  = 0
			,@publisher_insertcount_old   = 0
			,@publisher_updatecount_old   = 0
			,@publisher_deletecount_old   = 0
			,@publisher_conflictcount_old  = 0
			,@subscriber_insertcount_old  = 0
			,@subscriber_updatecount_old  = 0
			,@subscriber_deletecount_old  = 0
			,@subscriber_conflictcount_old  = 0
			,@existing_row_updateble = 1
			,@this_row_updateable = 1

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

    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

	IF @runstatus = @inprogress or @runstatus = @idle
		BEGIN
            SELECT TOP 1 @start_time = start_time, 
					@lastrow_timestamp = timestamp,
					@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
	ELSE IF @runstatus <> 1 --1 is the Start status
		BEGIN
			SELECT TOP 1 @start_time = start_time, 
						@lastrow_timestamp = timestamp,
						@lastrunstatus = runstatus
			FROM MSmerge_history (rowlock)
			WHERE  agent_id = @agent_id ORDER BY timestamp DESC

			if (@lastrunstatus = @succeed or @lastrunstatus = @failure or @lastrunstatus = @retry)
			begin
				select @start_time = @current_time
			end
		END
    ELSE
		BEGIN
			SELECT @start_time = @current_time
		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) 

	if (@start_time = @current_time and (@runstatus = @succeed or @runstatus = @failure or @runstatus = @retry))
    begin
		declare @logintime datetime
		select @logintime = NULL

		select @logintime = (select top 1 login_time from master.dbo.sysprocesses (NOLOCK) where spid = @@spid)

		set @start_time = @logintime
		if (@logintime is not null)
		begin
			select @duration = DATEDIFF(second, @logintime, @current_time)
		end
	end
                             
    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", @agent_name, @changes)

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

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

    if @runstatus = @inprogress  or @runstatus = @idle			-- 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
    
    IF @duration <> 0 
       SELECT @delivery_rate = (@delivered_rows * 1.0) / @duration
    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
	BEGIN
        -- Ignore errors here. @error_id will be set to 0 in case of errors  
        EXEC dbo.sp_MSget_new_errorid @error_id OUTPUT
	END

    -- 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,
			error_id = case @error_id when 0 then error_id else @error_id end    
            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

	-- Refresh repl monitor
	declare @agent_type int
	set @agent_type = dbo.fn_MSmask_agent_type (@agent_id, 4)

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

	-- Raise the appropriate error
	if @do_raiserror = 1
	begin
		select @agentclassname = formatmessage(14554)
		exec dbo.sp_MSrepl_raiserror agentclassname, @agent_name, @runstatus, @comments
	end

    IF @@ERROR <> 0
        RETURN (1)

    RETURN (0)
END

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSenum_merge'')

')
    if @@error <> 0 return 1
    exec('create procedure sp_MSenum_merge
@name nvarchar(100) = ''%'',
@show_distdb bit = 0,
@exclude_anonymous 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 @last_timestamp binary(8)
    declare @offload_enabled bit
    declare @offload_server  sysname
		,@subscriber_type tinyint


    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,
        offload_enabled bit NOT NULL, offload_server sysname NULL,
		subscriber_type tinyint 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, 
            offload_enabled, offload_server
            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 and
			(@exclude_anonymous = 0 or a.subscriber_name is null)

        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, 
        @offload_enabled, @offload_server

    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 = duration, 
            @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
				select @subscriber_type = type from MSsubscriber_info where
					UPPER(publisher) = UPPER(@publisher) and
					UPPER(subscriber) = UPPER(@subscriber)
            end
        else 
            begin
                select @subscriber = @subscriber_name
                select @subscriber_db = @subscriber_db + ''-'' +  convert(nvarchar(12), @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,
            @offload_enabled, @offload_server, @subscriber_type)

        FETCH hC INTO @publisher_id, @subscriber_id, @publisher_db, @subscriber_db,
            @publication, @agent_name, @local_job, @job_id, @profile_id,
            @agent_id, @subscriber_name, @offload_enabled, @offload_server
        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


')
    if @@error <> 0 return 1
    exec('

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

')
    if @@error <> 0 return 1
    exec('
create procedure sp_MSenum_merge_s
@name nvarchar(100), 
@hours int = 0, /* @hours < 0 will return TOP 100 */
@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
    BEGIN
        select top 100 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 and agent_id = @agent_id),
            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)
            order by timestamp desc
    END
    ELSE
    BEGIN
        IF @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 and agent_id = @agent_id),
            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
    END


')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSenum_merge_sd'')

')
    if @@error <> 0 return 1
    exec('
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

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSenableagentoffload'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MSenableagentoffload (
    @job_id             VARBINARY(16),
    @offloadserver      sysname = NULL
    ) AS

    SET NOCOUNT ON

    /*
    **  Declarations
    */

    DECLARE @agent_id          INT
    DECLARE @subscription_type INT
    DECLARE @agenttype         NVARCHAR(20)
    DECLARE @agent_table       sysname 
    DECLARE @offloadserverwasnull INT
    DECLARE @retcode           INT
    
    SELECT @agent_id = NULL
    SELECT @subscription_type = 0
    SELECT @agenttype = NULL    
    SELECT @agent_table = RTRIM(@@SERVERNAME) + ''.'' + db_name() + N''.dbo.''

    -- get the agent type from sysjobsteps table (subsystem) 
    SELECT @agenttype = LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) 
      FROM msdb..sysjobsteps 
     WHERE job_id = @job_id
       AND LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) IN (N''distribution'', N''merge'') 
    
    -- validate the agent type, it has to be non null
    IF @agenttype IS NULL
    BEGIN
        RAISERROR(21134, 16, -1)
        RETURN 1
    END

    -- Make sure that the given offload server does not contain
    -- invalid characters
    EXEC @retcode = sp_MSreplcheckoffloadserver @offloadserver
    IF @retcode <> 0 OR @@error <> 0
    BEGIN
        RETURN (1)
    END

    -- validate the existence of the job_id in the agent table and get 
    -- the value of the subscription type and agentid at the same time
    IF @agenttype = N''distribution''
    BEGIN
        SELECT @subscription_type = subscription_type,
               @agent_id = id  
          FROM dbo.MSdistribution_agents
         WHERE job_id = @job_id
        SELECT @agent_table = @agent_table + ''MSdistribution_agents''
    END
    ELSE 
    BEGIN
        SELECT @subscription_type = ms.subscription_type,
               @agent_id = ma.id
          FROM dbo.MSmerge_agents ma 
    INNER JOIN dbo.MSpublications mp 
            ON (ma.publisher_id = mp.publisher_id) 
           AND (ma.publisher_db = mp.publisher_db) 
           AND (ma.publication = mp.publication)
    INNER JOIN dbo.MSmerge_subscriptions ms
            ON (ma.publisher_id = ms.publisher_id)
           AND (ma.publisher_db = ms.publisher_db)
           AND (mp.publication_id = ms.publication_id)
           AND (ma.subscriber_id = ms.subscriber_id)
           AND (ma.subscriber_db = ms.subscriber_db)
     WHERE @job_id = ma.job_id
        SELECT @agent_table = @agent_table + ''MSmerge_agents''
    END

    IF @agent_id IS NULL
    BEGIN
        RAISERROR(21135, 16, -1, @agent_table)
        RETURN 1
    END 
    ELSE IF @@ROWCOUNT > 1
    BEGIN
        RAISERROR(21136, 16, -1, @agent_table)
        RETURN 1
    END

    -- raise error if subscription type is not push
    IF @subscription_type <> 0
    BEGIN
        RAISERROR(21137, 16, -1)
        RETURN 1
    END

    -- raise error if offload server name is the same as the
    -- distributor''s name
    IF LOWER(@@SERVERNAME) = LOWER(@offloadserver)
    BEGIN
        RAISERROR(21138, 16, -1)
        RETURN 1
    END

    -- if the offload server name is NULL, try to obtain the offload server
    -- name from the agent table
    IF (@offloadserver IS NULL) OR (@offloadserver = N'''')
    BEGIN
        SELECT @offloadserverwasnull = 1
        IF LOWER(@agenttype collate SQL_Latin1_General_CP1_CS_AS) = N''distribution''
        BEGIN
            SELECT @offloadserver = offload_server 
              FROM dbo.MSdistribution_agents
             WHERE id = @agent_id
        END
        ELSE
        BEGIN
            SELECT @offloadserver = offload_server
              FROM dbo.MSmerge_agents
             WHERE id = @agent_id
        END
    END 
    ELSE
    BEGIN
        SELECT @offloadserverwasnull = 0
    END

    -- if offload server name is still NULL, use the subscriber as the target 
    -- server
    IF (@offloadserver IS NULL) OR (@offloadserver = N'''')
    BEGIN
        IF LOWER(@agenttype collate SQL_Latin1_General_CP1_CS_AS) = N''distribution''
        BEGIN
            SELECT @offloadserver = ss.srvname 
              FROM master.dbo.sysservers ss 
        INNER JOIN dbo.MSdistribution_agents da 
                ON ss.srvid = da.subscriber_id
             WHERE @agent_id = da.id
        END
        ELSE
        BEGIN
            SELECT @offloadserver = ss.srvname
              FROM master.dbo.sysservers ss 
        INNER JOIN dbo.MSmerge_agents ma
                ON ss.srvid = ma.subscriber_id
             WHERE @agent_id = ma.id
        END
    END

    IF (@offloadserver IS NULL) OR (@offloadserver = N'''') 
    BEGIN
        RAISERROR(21139, 16, -1)
        RETURN 1
    END
    ELSE IF LOWER(@offloadserver) = LOWER(@@SERVERNAME) 
    BEGIN
        IF @offloadserverwasnull = 1
        BEGIN
            RAISERROR(21261, 16, -1)
        END
        ELSE
        BEGIN
            RAISERROR(21140, 16, -1)
        END
        RETURN 1
    END

    BEGIN TRANSACTION enable_offload
    
    -- Update the agent table with the new offload settings
    IF LOWER(@agenttype collate SQL_Latin1_General_CP1_CS_AS) = N''distribution''
    BEGIN
        UPDATE MSdistribution_agents
           SET offload_enabled = 1,
               offload_server = @offloadserver
         WHERE id = @agent_id
        IF @@ERROR <> 0
            GOTO Failure
    END
    ELSE
    BEGIN
        UPDATE MSmerge_agents
           SET offload_enabled = 1,
               offload_server = @offloadserver
         WHERE id = @agent_id
        IF @@ERROR <> 0
            GOTO Failure
    END    

    -- Add the offload parameter
    EXEC sp_MSaddoffloadparameter @offloadserver = @offloadserver,
                                  @job_id = @job_id,
                                  @agenttype = @agenttype
    
    IF @@ERROR <> 0
        GOTO Failure
    COMMIT TRANSACTION enable_offload
    RETURN 0
Failure:
    ROLLBACK TRANSACTION enable_offload
    RETURN 1

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSdisableagentoffload'')

')
    if @@error <> 0 return 1
    exec('CREATE PROCEDURE sp_MSdisableagentoffload (
    @job_id         VARBINARY(16),
    @offloadserver  sysname = NULL
    ) AS
    
    SET NOCOUNT ON
    
    DECLARE @agent_id    INT
    DECLARE @agenttype   NVARCHAR(20)

    SELECT @agent_id = NULL
    SELECT @agenttype = NULL

    -- Obtain the agent type from the sysjobssteps table
    SELECT @agenttype = LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) 
      FROM msdb..sysjobsteps
     WHERE job_id = @job_id
       AND LOWER(subsystem collate SQL_Latin1_General_CP1_CS_AS) IN (N''merge'', N''distribution'')

    -- validate the agent type, it has to be either ''merge'' or ''distribution''
    IF @agenttype IS NULL
    BEGIN
        RAISERROR(21134, 16, -1)
        RETURN 1
    END

    -- don''t check the subscription type 
    BEGIN TRANSACTION disable_offload
    
    -- Update the agent table with the new offload settings
    IF @agenttype = N''distribution''
    BEGIN
        IF @offloadserver IS NULL -- Leave the existing offload server alone
        BEGIN
            UPDATE MSdistribution_agents
               SET offload_enabled = 0
             WHERE job_id = @job_id
            IF @@ERROR <> 0
                GOTO Failure
        END
        ELSE
        BEGIN
            UPDATE MSdistribution_agents
               SET offload_enabled = 0,
                   offload_server = @offloadserver
             WHERE job_id = @job_id            
            IF @@ERROR <> 0
                GOTO Failure
        END    


    END
    ELSE
    BEGIN
        IF @offloadserver IS NULL -- Leave the existing offload server alone
        BEGIN
            UPDATE MSmerge_agents
               SET offload_enabled = 0
             WHERE job_id = @job_id
            IF @@ERROR <> 0
                GOTO Failure
        END
        ELSE
        BEGIN
            UPDATE MSmerge_agents
               SET offload_enabled = 0,
                   offload_server = @offloadserver
             WHERE job_id = @job_id
            IF @@ERROR <> 0
                GOTO Failure
        END
    END    

    -- Call stored procedure to remove -offload parameter from the 
    -- agent command line

    EXEC sp_MSremoveoffloadparameter @job_id = @job_id, 
                                     @agenttype = @agenttype
    IF @@ERROR <> 0
        GOTO Failure
    COMMIT TRANSACTION disable_offload

    RETURN 0
Failure:

    ROLLBACK TRANSACTION disable_offload
    RETURN (1)


')
    if @@error <> 0 return 1
    exec('    
raiserror(15339,-1,-1,''sp_MSadd_repl_error'')

')
    if @@error <> 0 return 1
    exec('
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
	declare @xact_seqno varbinary(16)
	declare @command_id 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)

	select @xact_seqno = xact_seqno, @command_id = command_id from MSrepl_errors where
		id = @id and xact_seqno is not null
    
	INSERT INTO MSrepl_errors VALUES (@id, getdate(), @error_type_id, @source_type_id,
        @source_name, @error_code, @error_text, @xact_seqno, @command_id)

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

    return (0)

')
    if @@error <> 0 return 1
    exec('

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

')
    if @@error <> 0 return 1
    exec('
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)

')
    if @@error <> 0 return 1
    exec('
raiserror(15339,-1,-1,''sp_MSadd_replmergealert'')

')
    if @@error <> 0 return 1
    exec('
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)

')
    if @@error <> 0 return 1
    exec('

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

')
    if @@error <> 0 return 1
    exec('
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, 
			''has_xact_seqno'' = case when xact_seqno is null or xact_seqno = 0x0
				then convert(bit, 0) else convert(bit, 1) end,
			xact_seqno, command_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

')
    if @@error <> 0 return 1
    exec('

raiserror(15339,-1,-1,''sp_MSdist_activate_auto_sub'')

')
    if @@error <> 0 return 1
    exec('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 @a