C# and I

May 2, 2012

sp_MSadd_merge_history (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSadd_merge_history(int @agent_id
, int @runstatus
, nvarchar @comments
, int @delivery_time
, int @download_inserts
, int @download_updates
, int @download_deletes
, int @download_conflicts
, int @upload_inserts
, int @upload_updates
, int @upload_deletes
, int @upload_conflicts
, bit @log_error
, bit @perfmon_increment
, bit @update_existing_row
, bit @updateable_row
, bit @do_raiserror
, bit @called_by_nonlogged_shutdown_detection_agent
, int @session_id_override)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_merge_history   
(  
    @agent_id       int,          
    @runstatus      int,   
    @comments       nvarchar(1000),  
    @delivery_time  int = 0,                    --  Milliseconds  --   
    @download_inserts  int = 0,  
    @download_updates  int = 0,  
    @download_deletes  int = 0,  
    @download_conflicts int = 0,  
    @upload_inserts int = 0,  
    @upload_updates int = 0,  
    @upload_deletes int = 0,  
    @upload_conflicts 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,  
    @called_by_nonlogged_shutdown_detection_agent bit = 0,  
    @session_id_override int = NULL  
)  
AS  
BEGIN  
    declare @start_time datetime  
    declare @end_time datetime  
    declare @inserts int  
    declare @updates int  
    declare @deletes int  
    declare @conflicts int  
    declare @total int  
    declare @percentage decimal(10,2)  
    DECLARE @current_time datetime  
            ,@duration int  
            ,@delivery_rate float  
            ,@perfmon_delivery_rate int  
            ,@perfmon_conflict_count int  
            ,@delivered_rows int  
            ,@changes int  
            ,@delivery_time_old  int  
            ,@delivered_rows_old int  
            ,@download_inserts_old  int   
            ,@download_updates_old  int   
            ,@download_deletes_old  int   
            ,@download_conflicts_old int   
            ,@upload_inserts_old int   
            ,@upload_updates_old int   
            ,@upload_deletes_old int   
            ,@upload_conflicts_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  
            ,@lastssrow_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  
            ,@spid_login_time datetime  
              
    --  Security Check  
    exec @retcode = sys.sp_MScheck_pull_access  
            @agent_id = @agent_id,  
            @agent_type = 1 --  merge agent  
    if @@error <> 0 or @retcode <> 0  
            return (1)  
  
    select @start_time = getdate()          
    select @percentage= NULL  
    -- select @spid_login_time = login_time from sys.dm_exec_sessions where session_id = @@spid  
    select @spid_login_time = NULL  
  
    if @session_id_override is null  
    begin  
        --  The only time session_id is fetched  
        declare @binctx binary(128)  
        declare @current_session_id int, @session_id int  
        SELECT @binctx = isnull(context_info(),0x00)  
        set @current_session_id=CAST( @binctx AS int )  
        if @current_session_id=0  
        begin  
        declare @logintime1 datetime  
        select @logintime1 = @start_time  
          
        insert into dbo.MSmerge_sessions(agent_id, start_time, runstatus, spid_login_time, spid)   
                values(@agent_id, @logintime1, 1, @spid_login_time, @@spid)  
        select @current_session_id = @@IDENTITY  
        if @@ERROR<>0  
            return (0)  
            SET @binctx = CAST( @current_session_id AS binary(128) )  
            SET CONTEXT_INFO @binctx       
        end  
        set @session_id=@current_session_id  
    end  
    else  
        set @session_id = @session_id_override  
      
    select @start_time = start_time from dbo.MSmerge_sessions  
            where session_id = @session_id    --  no need for top 1 because of unique clustered index on session_id  
      
    --  calculate duration for summary  
    set @end_time = getdate()  
    select @duration=DATEDIFF(second, @start_time, @end_time)  
  
    --  This section is added during Yukon. >>  
          
    --    
    --  Status const defined in sqlrepl.h   
     --   
    select @startup = 1  
            ,@succeed = 2  
            ,@inprogress = 3  
            ,@idle = 4  
            ,@retry = 5  
            ,@failure = 6  
            ,@delivery_time_old  = 0  
            ,@download_inserts_old   = 0  
            ,@download_updates_old   = 0  
            ,@download_deletes_old   = 0  
            ,@download_conflicts_old  = 0  
            ,@upload_inserts_old  = 0  
            ,@upload_updates_old  = 0  
            ,@upload_deletes_old  = 0  
            ,@upload_conflicts_old  = 0  
            ,@existing_row_updateble = 0  
            ,@this_row_updateable = 0  
  
    if (@updateable_row = 1)  
    begin  
        select @this_row_updateable = 1  
    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 dbo.MSmerge_agents where id = @agent_id  
    SELECT @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id  
  
    IF @runstatus = @inprogress or @runstatus = @idle  
        BEGIN  
            SELECT TOP 1 @lastrow_timestamp = timestamp,  
                    @existing_row_updateble = updateable_row  
            FROM dbo.MSmerge_history with (rowlock)  
            WHERE  agent_id = @agent_id ORDER BY timestamp DESC  
  
            SELECT TOP 1 @lastssrow_timestamp = timestamp,  
                            @start_time = start_time,   
                    @download_inserts_old = download_inserts ,  
                    @download_updates_old = download_updates,   
                    @download_deletes_old = download_deletes,  
                    @download_conflicts_old = download_conflicts,  
                    @upload_inserts_old = upload_inserts,   
                    @upload_updates_old = upload_updates,  
                    @upload_deletes_old = upload_deletes,  
                    @upload_conflicts_old = upload_conflicts,  
                    @delivery_time_old = delivery_time  
            FROM dbo.MSmerge_sessions with (rowlock)  
            WHERE  session_id=@session_id  
          
            select @delivered_rows_old = @download_inserts_old +   
                             @download_updates_old +  
                             @download_deletes_old +  
                             @upload_updates_old +  
                             @upload_inserts_old +  
                             @upload_deletes_old   
        END  
    ELSE IF @runstatus <> 1 -- 1 is the Start status  
        BEGIN  
            SELECT TOP 1 @lastrow_timestamp = timestamp  
            FROM dbo.MSmerge_history with (rowlock)  
            WHERE  agent_id = @agent_id ORDER BY timestamp DESC  
  
            SELECT TOP 1 @lastssrow_timestamp = timestamp,  
                        @start_time = start_time,  
                        @lastrunstatus = runstatus  
            FROM dbo.MSmerge_sessions with (rowlock)  
            WHERE session_id=@session_id  
  
            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 start_time from dbo.MSmerge_sessions with (NOLOCK) where session_id=@session_id)  
        set @start_time = @logintime  
        if (@logintime is not null)  
        begin  
            select @duration = DATEDIFF(second, @logintime, @current_time)   
        end  
    end  
                               
    select @delivered_rows = @download_inserts +   
                             @download_updates +  
                             @download_deletes +  
                             @upload_updates +  
                             @upload_inserts +  
                             @upload_deletes   
  
    --  Set Perfmon counters  
    if @runstatus = @idle or @runstatus = @inprogress  
    begin  
        dbcc addinstance ("SQL Replication Merge", @agent_name)  
  
        set @changes = @download_inserts + @download_updates + @download_deletes                               
        dbcc incrementinstance ("SQL Replication Merge", "Downloaded Changes", @agent_name, @changes)  
  
        set @changes = @upload_updates + @upload_inserts + @upload_deletes   
        dbcc incrementinstance ("SQL Replication Merge", "Uploaded Changes", @agent_name, @changes)  
  
        set @perfmon_conflict_count = @download_conflicts + @upload_conflicts  
        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 @download_inserts = @download_inserts_old + @download_inserts  
        select      @download_updates = @download_updates_old + @download_updates  
        select      @download_deletes = @download_deletes_old + @download_deletes  
        select      @download_conflicts = @download_conflicts_old + @download_conflicts  
        select      @upload_inserts = @upload_inserts_old + @upload_inserts  
        select     @upload_updates = @upload_updates_old + @upload_updates  
        select     @upload_deletes = @upload_deletes_old + @upload_deletes  
        select     @upload_conflicts = @upload_conflicts_old + @upload_conflicts  
  
        select  @delivery_time = @delivery_time_old + @delivery_time  
        select @delivered_rows = @delivered_rows + @delivered_rows_old  
    end  
      
    IF @duration <> 0 and @duration is not null  
       SELECT @delivery_rate = (@delivered_rows * 1.0) / @duration  
    ELSE  
       SELECT @delivery_rate = 0.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 or (@runstatus = @failure and (@called_by_nonlogged_shutdown_detection_agent = 1 or @session_id_override is not null))  
    BEGIN  
        --  Ignore errors here. @error_id will be set to 0 in case of errors    
        EXEC sys.sp_MSget_new_errorid @error_id OUTPUT  
          
        if (@runstatus = @failure and (@called_by_nonlogged_shutdown_detection_agent = 1 or @session_id_override is not null))  
        begin  
            exec sys.sp_MSadd_repl_error   
                @id = @error_id,  
                @error_type_id = 0,  
                @source_type_id = 0,  
                @source_name = NULL,  
                @error_code = 0,  
                @error_text = @comments,  
                @session_id = @session_id  
        end  
    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 dbo.MSmerge_history SET   
                    comments = @comments,  
            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  
  
        --  Insert idle record if there is not one  
        if @@ROWCOUNT = 0  
            INSERT INTO dbo.MSmerge_history  
            (  
            agent_id, comments, error_id, timestamp, updateable_row, session_id  
            )  
            VALUES   
            (  
            @agent_id, @comments, @error_id, NULL,@this_row_updateable, @session_id  
            )  
    end  
    else  
    begin  
        INSERT INTO dbo.MSmerge_history   
        (  
            agent_id, comments, error_id, timestamp, updateable_row, session_id  
        )  
        VALUES   
        (  
            @agent_id, @comments, @error_id, NULL, @this_row_updateable, @session_id  
        )  
    end  
      
    if @session_id_override is null  
    begin  
        UPDATE dbo.MSmerge_sessions SET   
            runstatus = case when runstatus in (@succeed, @retry, @failure) and @runstatus not in (@succeed, @retry, @failure)   
                        and spid is not null  
                        then runstatus   
                        else @runstatus end,   
            end_time = @current_time, duration = @duration,  
            delivery_time = @delivery_time,   
            delivery_rate = ISNULL(@delivery_rate, 0.0),   
            download_inserts = @download_inserts,  
            download_updates = @download_updates,  
            download_deletes = @download_deletes,  
            download_conflicts = @download_conflicts,  
            upload_inserts = @upload_inserts,  
            upload_updates = @upload_updates,  
            upload_deletes = @upload_deletes,  
            upload_conflicts = @upload_conflicts,  
            spid_login_time = @spid_login_time,  
            spid = @@spid  
            WHERE session_id=@session_id  
    end  
    else  
    begin  
        UPDATE dbo.MSmerge_sessions SET   
            runstatus = case when runstatus in (@succeed, @retry, @failure) and @runstatus not in (@succeed, @retry, @failure)   
                        and spid is not null  
                        then runstatus   
                        else @runstatus end,   
            end_time = @current_time, duration = @duration,  
            spid_login_time = NULL,        --  when NULL then a subsequent progress message from agent will be allowed to change failed status back to running.  
            spid = NULL                    --  when NULL then a subsequent progress message from agent will be allowed to change failed status back to running.  
            WHERE session_id=@session_id  
    end  
  
    --  Raise the appropriate error  
    if @do_raiserror = 1  
    begin  
        select @agentclassname = formatmessage(14554)  
        exec sys.sp_MSrepl_raiserror agentclassname, @agent_name, @runstatus, @comments  
    end  
  
    IF @@ERROR <> 0  
        RETURN (1)  
  
    RETURN (0)  
END

sp_MSadd_merge_anonymous_agent (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSadd_merge_anonymous_agent(smallint @publisher_id
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber_db
, nvarchar @subscriber_name
, uniqueidentifier @subid
, int @first_anonymous
, int @subscriber_version
, int @publisher_engine_edition)

MetaData:

   
CREATE PROCEDURE sys.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.  
    @subscriber_version int = 60, --  60=shiloh sp3 or lower, 90=yukon  
    @publisher_engine_edition int = null  
)  
AS  
begin  
    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 @retention_period_unit tinyint  
    declare @success        int  
    declare @expired        int  
    declare @dropped        int  
    declare @allow_anonymous bit  
    declare @publisher        sysname  
    declare @subscriber_name_current sysname  
  
    --   
    --  security check  
    --  Has to be executed from distribution database  
    --  PAL check is done after getting publication metadata  
    --   
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)  
    begin  
        raiserror(21482, 16, -1, 'sp_MSadd_merge_anonymous_agent', 'distribution')  
        return (1)  
    end  
      
    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 dbo.MSmerge_agent table for anonymous subscription for the   
    --  purpose of history logging  
     --   
  
    --  Check to see if the publication is valid and allows anonymous subscribers  
    select @publication_id = publication_id, @allow_anonymous = allow_anonymous, @retention = retention,  
            @retention_period_unit = retention_period_unit  
    from dbo.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  
      
    select @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id  
  
    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  
  
    --   
    --  PAL Security check  
    --   
    exec @retcode = sys.sp_MScheck_pull_access  
        @publication_id = @publication_id, @agent_type = 1  
    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 dbo.MSmerge_agents   
                where publisher_id=@publisher_id and   
                      publisher_db = @publisher_db and   
                      publication = @publication and   
                      subscriber_name = @subscriber_name   
                      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 dbo.MSmerge_agents where anonymous_subid=@subid)  
    begin  
  
        if @first_anonymous <= 0   -- only add agent entry for initial subscription only.   
        begin  
            select @not_exist = 1  
              
            begin tran   
              
            exec @retcode = sys.sp_MScheck_subscription_count_internal @mode = 1, @publisher = @publisher, @publisher_engine_edition = @publisher_engine_edition,      
                            @about_to_insert_new_subscription=1  
            if @@error <> 0 or @retcode <> 0  
            begin  
                if @@trancount > 0  
                    commit tran  
                return 1  
            end  
                  
            --  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 dbo.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)  
                           
            commit tran  
        end  
        else  
            select @dropped  =1  
    end  
  
    select @agent_id = id, @agent_name = name, @subscriber_name_current = subscriber_name from dbo.MSmerge_agents   
        where anonymous_subid=@subid  --  subid guarantees uniqueness  
  
    if @subscriber_name <> @subscriber_name_current and  @subscriber_name is not null and @subscriber_name <> N''  
    begin  
        --  Due to a bug in the upgrade code in yukon RTM that has been fixed in SP2 the subscriber_name column of MSmerge_agents may not be correct for anonymous   
        --  subscribers if the database has been upgraded from shiloh to pre yukon SP2 build. Running the following query to do the fixup.  
        update dbo.MSmerge_agents set subscriber_name = @subscriber_name  
            where anonymous_subid=@subid  --  subid guarantees uniqueness  
    end  
  
  
    if @by_pass = 0 and @subscriber_version < 90 -- by pass the checking if retention is NULL or 0 or if yukon  
    begin  
        select @min_valid_day = sys.fn_add_units_to_date(-@retention, @retention_period_unit, getdate())  
  
        --  only do history based expiration if there is a valid successful run information available.          
        --  If we do not find the history for the last successful run we will not cleanup.  
        --  however a seperate cleanup happens that uses the last_sync_time on the publisher. So  
        --  even if we do not cleanup here sp_MSdrop_expired_mergesubscription will take care of it  
        select Top 1 @last_status = runstatus, @last_history = end_time from dbo.MSmerge_sessions where agent_id = @agent_id and runstatus=2  
                    order by session_id 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 is not NULL  
        begin  
            if @last_history < @min_valid_day and @first_anonymous <> 0    -- do not check for re-initialized replicas.  
                select @expired = 1  
        end  
   end  
  
    select @agent_id, @agent_name, @expired where @dropped = 0 -- return empty result set   
end

sp_MSadd_merge_agent (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSadd_merge_agent(nvarchar @name
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, bit @local_job
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date
, nvarchar @optional_command_line
, bit @offloadagent
, nvarchar @offloadserver
, int @subscription_type
, nvarchar @hostname
, smallint @subscriber_security_mode
, nvarchar @subscriber_login
, nvarchar @subscriber_password
, smallint @publisher_security_mode
, nvarchar @publisher_login
, nvarchar @publisher_password
, nvarchar @job_login
, nvarchar @job_password
, nvarchar @internal
, int @publisher_engine_edition)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_merge_agent   
(  
    --  not null if from scripting  
    @name                           sysname = NULL,  
    @publisher                      sysname,                    --  Publisher server  --   
    @publisher_db                   sysname,                    --  Publisher database  --   
    @publication                    sysname,                    --  Publication name  --   
    @subscriber                     sysname,                    --  Subscriber server  --   
    @subscriber_db                  sysname,                    --  Subscription database  --   
    @local_job                      bit,  
    @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(255) = '',     --  Optional command line arguments  --   
    @merge_jobid                    binary(16) = NULL OUTPUT,  
    --  Agent offload  
    @offloadagent                   bit = 0,  
    @offloadserver                  sysname = NULL,  
    @subscription_type              int = 0, --  0 = push, 1 = pull  
    @hostname                        sysname = NULL,  
    --  used for subscription based security  
    @subscriber_security_mode         smallint = NULL,  
    @subscriber_login                sysname = NULL,  
    @subscriber_password             nvarchar(524) = NULL,  
    @publisher_security_mode         smallint = NULL,  
    @publisher_login                sysname = NULL,  
    @publisher_password             nvarchar(524) = NULL,  
    --  used for jobstep level proxy account  
    @job_login                        nvarchar(257) = NULL,  
    @job_password                    sysname = NULL,  
    @internal                        sysname = N'PRE-YUKON',        --  Can be: 'PRE-YUKON', 'YUKON ADD SUB', 'YUKON ADD AGENT'  
    @publisher_engine_edition        int = NULL  
      
)   
AS  
begin  
    SET NOCOUNT ON  
  
    --   
    --  Declarations.  
     --   
    declare @retcode        int  
    DECLARE @publisher_id   smallint  
    DECLARE @subscriber_id  smallint  
    DECLARE @profile_id     int  
    DECLARE @merge_type     int  
    DECLARE @command        nvarchar(4000)  
  
    DECLARE @subscriber_datasource_type     int     --  0 SQL Server, 1 ODBC, 2 Jet, 3 OLEDB  --   
    DECLARE @distributor                    sysname   
    DECLARE @database                       sysname  
    DECLARE @agent_id                       int  
    DECLARE @category_name                  sysname  
    DECLARE @dsn_subscriber                 tinyint  
    DECLARE @jet_subscriber                 tinyint  
    DECLARE @oledb_subscriber               tinyint  
    DECLARE @exchange_subscriber            tinyint  
    DECLARE @oracle_subscriber              tinyint  
    DECLARE @db2universal_subscriber        tinyint  
    DECLARE @platform_nt                    binary  
    DECLARE @provider_name                    sysname  
    DECLARE @merge_job_step_uid                uniqueidentifier  
    DECLARE @subscriber_encrypted_password     nvarchar(524)  
    DECLARE @publisher_encrypted_password     nvarchar(524)  
      
    set @distributor = @@SERVERNAME  
  
    set @dsn_subscriber = 1    --  Const: subscriber type 'dsn'  --    
    set @jet_subscriber = 2     
    set @oledb_subscriber = 3   
    set @exchange_subscriber = 4  
    set @oracle_subscriber = 5   
    set @db2universal_subscriber = 6  
  
    set @platform_nt = 0x1    
  
    set @merge_job_step_uid = NULL  
  
    --  Security Check: require sysadmin  
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)  
    BEGIN  
        RAISERROR(21089,16,-1)   
        RETURN 1  
    END  
  
    IF (sys.fn_MSrepl_isdistdb (DB_NAME()) != 1)  
    BEGIN  
           --  "sp_MSadd_merge_agent can only be executed in the distribution database."  
        RAISERROR(21482, 16, -1, 'sp_MSadd_merge_agent', 'distribution')  
        RETURN 1  
    END  
  
    IF @offloadagent IS NOT NULL  
        AND @offloadagent != 0  
    BEGIN  
        --  "Parameter '@offloadagent' is no longer supported."  
        RAISERROR(21698, 16, -1, '@offloadagent')  
        RETURN 1  
    END  
  
    IF ISNULL(@offloadserver, N'') != N''  
    BEGIN  
        --  "Parameter '@offloadserver' is no longer supported."  
        RAISERROR(21698, 16, -1, '@offloadserver')  
        RETURN 1  
    END  
      
    --  Set null @optional_command_line to empty string to avoid string concat problem  
    SELECT @optional_command_line = ISNULL(N' ' + LTRIM( RTRIM(@optional_command_line) ) + N' ', N'')  
      
    --   
    --  Initializations  
     --   
    --  Get subscriber info  
    select @subscriber_id = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber)  
    select @publisher_id = srvid from master.dbo.sysservers where UPPER(srvname) = UPPER(@publisher)  
  
    select @subscriber_datasource_type = type  
        from MSsubscriber_info   
        where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber)  
  
    --   
    --  Jet and Oracle subscribers are actually added to MSsubscriber_info as OLE DB subscribers,  
    --  since they can be used in transactional replication also.  
    --  Map the type to Jet or Oracle based on OLE DB provider name.  
     --   
    if (@subscriber_datasource_type = @oledb_subscriber)   
    BEGIN  
        select @provider_name = providername from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber)  
        if (upper(@provider_name) = 'MICROSOFT.JET.OLEDB.4.0')  
            select @subscriber_datasource_type = @jet_subscriber  
        else if (upper(@provider_name) = 'MSDAORA')  
            select @subscriber_datasource_type = @oracle_subscriber  
        else if (upper(@provider_name) = 'DB2OLEDB')  
            select @subscriber_datasource_type = @db2universal_subscriber  
    END  
      
    if (@subscriber_datasource_type IS NULL)  
        select @subscriber_datasource_type = 0  
  
    --  ONLY ALLOW THIS IN 8.0 or less CASE  
    IF @internal = N'PRE-YUKON'  
    BEGIN  
        --  if @name is not null, the proc is from DMO scripting  
        --  check to see if the job is there are not, if not, reset @job_existing and  
        --  @name values. This is for the case when the user generate the script at  
        --  the publisher but did not re-create repl jobs at the distributor.  
        if @local_job = 1 and @name is not null and   
           @name <> N''  
        begin  
            if not exists (select * from msdb.dbo.sysjobs_view   
                where name = @name   
                and UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  
                and master_server = 0)  
            begin  
                set @name = null  
            end  
        end  
    END  
      
    BEGIN TRANSACTION tr_add_merge_agent  
    SAVE TRANSACTION tr_add_merge_agent  
  
    --  Code for merge agent type in MSagent_profiles  --   
    SELECT @merge_type = 4  
  
    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)  
  
    IF @internal = N'PRE-YUKON'  
        OR @internal = N'YUKON ADD AGENT'  
    BEGIN  
        --  if the subscriber sec info was not provided then default  
        --  the vals to the values provided durring sp_addsubscriber...  
        --  this is only possible when called by SYSADMIN or 8.0 pub.  
        IF @subscriber_security_mode is NULL  
        BEGIN  
            -- Get default task parameter values from MSsubscriber_info   
            SELECT @subscriber_security_mode = security_mode,  
                    @subscriber_login = login,  
                    @subscriber_password = password  
                FROM MSsubscriber_info  
                WHERE UPPER(publisher) = UPPER(@publisher)   
                    AND UPPER(subscriber) = UPPER(@subscriber)  
  
            IF @subscriber_security_mode IS NULL  
            BEGIN  
                SELECT @subscriber_security_mode = 1,  
                        @subscriber_login = '',  
                        @subscriber_password = newid()  
  
                --  Encrypt the password before storing  
                EXEC @retcode = sys.sp_MSreplencrypt @subscriber_password OUTPUT  
                IF @@ERROR <> 0 OR  @retcode <> 0  
                    GOTO UNDO  
            END  
        END  
        ELSE  
        BEGIN  
            IF @subscriber_security_mode = 1  
            BEGIN  
                SELECT @subscriber_login = N'',  
                        @subscriber_password = newid()  
            END  
  
            EXEC @retcode = sys.sp_MSreplencrypt @subscriber_password OUTPUT  
            IF @@error <> 0 or @retcode <> 0  
                GOTO UNDO  
        END  
  
        --  if the publisher sec info was not provided then default  
        --  the vals to the values provided durring sp_adddistpublisher...  
        --  this is only possible when called by SYSADMIN or 8.0 pub.  
        IF @publisher_security_mode is NULL  
        BEGIN  
            --  if the publisher security mode was not provided then   
            --  we will fall back to the backcmpt form which is the   
            --  value found in the msdb..MSdistpublishers table...  
            select @publisher_security_mode = security_mode,  
                    @publisher_login = login,  
                    @publisher_password = password  
                from msdb..MSdistpublishers  
                where upper(name) = upper(@publisher)  
                    and distribution_db = db_name()  
        END  
        ELSE   
        BEGIN  
            IF @publisher_security_mode = 1  
            BEGIN  
                SELECT @publisher_login = N'',  
                        @publisher_password = newid()  
            END  
  
            EXEC @retcode = sys.sp_MSreplencrypt @publisher_password OUTPUT  
            IF @@error <> 0 or @retcode <> 0  
                GOTO UNDO  
        END  
    END  
      
    IF @internal = N'PRE-YUKON'  
        OR @internal = N'YUKON ADD SUB'  
    BEGIN  
        --  Try to drop it first  
        EXEC sys.sp_MSdrop_merge_agent   
            @publisher = @publisher,  
            @publisher_db = @publisher_db,  
            @publication = @publication,  
            @subscriber = @subscriber,  
            @subscriber_db = @subscriber_db  
        IF @@ERROR <> 0  
            GOTO UNDO  
  
        IF @internal = N'YUKON ADD SUB'  
        BEGIN  
            --  When calling it from internal = 0 we know that this is   
            --  a dummy row until the actual value is set on addpushagent  
            SELECT @subscriber_security_mode = 1,  
                    @subscriber_login = N'',  
                    @subscriber_password = newid(),  
                    @publisher_security_mode = 1,  
                    @publisher_login = N'',  
                    @publisher_password = newid()  
  
            EXEC @retcode = sys.sp_MSreplencrypt @subscriber_password OUTPUT  
            IF @@error <> 0 or @retcode <> 0  
                GOTO UNDO  
  
            EXEC @retcode = sys.sp_MSreplencrypt @publisher_password OUTPUT  
            IF @@error <> 0 or @retcode <> 0  
                GOTO UNDO  
        END  
          
        exec @retcode = sys.sp_MScheck_subscription_count_internal @mode = 1, @publisher = @publisher, @publisher_engine_edition = @publisher_engine_edition,  
                        @about_to_insert_new_subscription=1  
        if @@error <> 0 or @retcode <> 0  
            goto UNDO  
          
        --    
        --  Insert row  
         --   
        INSERT INTO dbo.MSmerge_agents (name, publisher_id, publisher_db, publication,   
             subscriber_id, subscriber_db, local_job, profile_id,  
             subscriber_security_mode, subscriber_login, subscriber_password, subscriber_name,   
             publisher_security_mode, publisher_login, publisher_password)  
             VALUES ('',@publisher_id, @publisher_db, @publication,   
             @subscriber_id, @subscriber_db, @local_job, @profile_id,  
             @subscriber_security_mode, @subscriber_login, @subscriber_password, @subscriber,  
             @publisher_security_mode, @publisher_login, @publisher_password)  
        IF @@ERROR <> 0  
            GOTO UNDO  
  
        SELECT @agent_id = @@IDENTITY  
    END  
    ELSE IF @internal = N'YUKON ADD AGENT'  
    BEGIN  
        SELECT @agent_id = id  
            FROM MSmerge_agents  
            WHERE publisher_id = @publisher_id  
                AND publisher_db = @publisher_db  
                AND publication = @publication  
                AND upper(subscriber_name) = upper(@subscriber)  
                AND subscriber_db = @subscriber_db  
  
        UPDATE MSmerge_agents  
            SET subscriber_security_mode = @subscriber_security_mode,  
                subscriber_login = @subscriber_login,  
                subscriber_password = @subscriber_password,  
                publisher_security_mode = @publisher_security_mode,  
                publisher_login = @publisher_login,  
                publisher_password = @publisher_password  
            WHERE @agent_id = id  
    END  
  
    declare @job_existing bit  
    IF @name IS NULL OR @name = N''  
    begin  
        SELECT @name = CONVERT(nvarchar(21),@publisher ) + '-' + CONVERT(nvarchar(21),@publisher_db) + '-' +   
                        CONVERT(nvarchar(21),@publication) + '-' + CONVERT(nvarchar(21),@subscriber) + '-' +  
                        CONVERT(nvarchar, @agent_id)  
        select @job_existing = 0  
    end  
    else  
        select @job_existing = 1  
  
    --  If creating a new job and the generated name already exists, re-generate the name with a   
    --  guid appended  
    IF  @job_existing = 0  
    BEGIN  
        IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view   
                        WHERE name = @name   
                            AND UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  
                            and master_server = 0)  
        BEGIN  
            SELECT @name = fn_repluniquename(newid(), @publisher, @publisher_db,   
                        @publication, @subscriber)  
        END              
    END  
      
    IF @internal = N'PRE-YUKON'  
        OR @internal = N'YUKON ADD AGENT'  
    BEGIN  
        if @frequency_type is NULL  
            set @frequency_type = 4     --  Daily  --   
        if @frequency_interval is NULL  
            set @frequency_interval = 1  
        if @frequency_relative_interval is NULL  
            set @frequency_relative_interval = 1  
        if @frequency_recurrence_factor is NULL  
            set @frequency_recurrence_factor = 0  
        if @frequency_subday is NULL  
            set @frequency_subday = 8   --  Hour  --   
        if @frequency_subday_interval is NULL  
            set @frequency_subday_interval = 1  
        if @active_start_time_of_day is NULL  
            set @active_start_time_of_day = 0  
        if @active_end_time_of_day is NULL  
            set @active_end_time_of_day = 235959  
        if @active_start_date is NULL  
            set @active_start_date = 0  
        if @active_end_date is NULL  
            set @active_end_date = 99991231  
  
        --  Add Perfmoon instance  
        dbcc addinstance ("SQL Replication Merge", @name)  
           
           IF @local_job = 1  
        BEGIN  
            if @job_existing = 0  
            begin  
  
                --  Construct task command  --   
  
                select @command = '-Publisher ' + QUOTENAME(@publisher) + ' -PublisherDB ' + QUOTENAME(@publisher_db) + ' '  
                select @command = @command + '-Publication ' + QUOTENAME(@publication) + ' '  
                select @command = @command + '-Subscriber ' + QUOTENAME(@subscriber)  + ' '  
  
                if (@subscriber_datasource_type = 0)  
                    select @command = @command + '-SubscriberDB ' + QUOTENAME(@subscriber_db) + ' '  
              
                if (@subscriber_datasource_type <> 0)  
                    select @command = @command + '-SubscriberType ' + convert(nvarchar(10),@subscriber_datasource_type) + ' '  
          
                select @command = @command + @optional_command_line  
                select @command = @command + '-Distributor ' + QUOTENAME(@distributor) + ' '  
  
                --  Always use integrated for local connection  
                select @command = @command + '-DistributorSecurityMode 1 '  
                  
                if @hostname is not null and rtrim(ltrim(@hostname)) <> ''  
                    select @command = @command + '-HostName ' + quotename(@hostname) + ' '  
  
                select @database = db_name()  
  
                --  Get Merge category name (assumes category_id = 14)  
                select @category_name = name FROM msdb.dbo.syscategories where category_id = 14  
  
                EXEC @retcode = dbo.sp_MSadd_repl_job  
                        @name = @name,  
                        @subsystem = 'Merge',  
                        @server = @@SERVERNAME,  
                        @databasename = @database,  
                        @enabled = 1,  
                        @freqtype = @frequency_type,  
                        @freqinterval = @frequency_interval,  
                        @freqsubtype = @frequency_subday,  
                        @freqsubinterval = @frequency_subday_interval,  
                        @freqrelativeinterval = @frequency_relative_interval,  
                        @freqrecurrencefactor = @frequency_recurrence_factor,  
                        @activestartdate = @active_start_date,  
                        @activeenddate = @active_end_date,  
                        @activestarttimeofday = @active_start_time_of_day,  
                        @activeendtimeofday = @active_end_time_of_day,  
                        @command = @command,  
                        @category_name = @category_name,  
                        @failure_detection = 1,  
                        @agent_id = @agent_id,  
                        @retryattempts = 10,  
                        @retrydelay = 1,  
                        @job_login = @job_login,  
                        @job_password = @job_password,  
                        @job_id = @merge_jobid OUTPUT,  
                        @job_step_uid = @merge_job_step_uid OUTPUT  
                  
                if @@ERROR <> 0 or @retcode <> 0  
                    goto UNDO  
  
                --  note that we only raise the warning when called from   
                --  'PRE-YUKON' code. when called from 'YUKON ADD AGENT' the   
                --  warning is not needed since it that case it was not implicit  
                IF @internal = N'PRE-YUKON'  
                BEGIN  
                    --  Warning: The merge agent job has been implicitly created and will run under the SQL Server Agent Service Account.  
                    RAISERROR(21452, 10, -1, 'merge')  
                END  
            end  
            else  
            begin  
                select @merge_jobid = sjv.job_id,  
                        @merge_job_step_uid = sjs.step_uid  
                from msdb.dbo.sysjobs_view as sjv  
                    join msdb.dbo.sysjobsteps as sjs  
                        on sjv.job_id = sjs.job_id  
                where sjv.name = @name  
                    and sjv.master_server = 0  
                    and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))  
                    and sjv.category_id = 14  
                    and sjs.subsystem = N'Merge'  
                    and sjs.database_name = db_name()  
                  
                if @merge_jobid IS NULL  
                begin  
                    --  Message from msdb.dbo.sp_verify_job_identifiers  
                    RAISERROR(14262, -1, -1, 'Merge Job', @name)            
                    GOTO UNDO  
                end  
            end  
        END  
        ELSE   
        BEGIN  
            --  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();  
            set @merge_job_step_uid = NULL  
        END  
    END  
    ELSE   
    BEGIN  
        --  Generate a job GUID even when we haven't created the job agent. This   
        --  will be used by the UI to uniquely identify rows returned by the enums  
        SELECT @merge_jobid = newid(),  
                    @merge_job_step_uid = NULL  
    END  
              
    UPDATE dbo.MSmerge_agents   
        SET name = ISNULL(@name, N''),  
            job_id = @merge_jobid,  
            job_step_uid = @merge_job_step_uid  
        WHERE id = @agent_id  
    IF @@ERROR <> 0  
        GOTO UNDO  
      
    COMMIT TRANSACTION tr_add_merge_agent  
  
    RETURN(0)  
  
UNDO:  
  
    if @@trancount > 0  
    begin  
        ROLLBACK TRANSACTION tr_add_merge_agent  
        COMMIT TRANSACTION  
    end  
      
    RETURN(1)  
end

sp_MSadd_logreader_history (Transact-SQL MetaData) Definition

Definition:

sys.sp_MSadd_logreader_history(int @agent_id
, int @runstatus
, nvarchar @comments
, varbinary @xact_seqno
, int @delivery_time
, int @delivered_transactions
, int @delivered_commands
, int @delivery_latency
, bit @log_error
, bit @perfmon_increment
, bit @update_existing_row
, bit @do_raiserror
, bit @updateable_row)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_logreader_history   
(  
    @agent_id int,  
    @runstatus int,  
    @comments nvarchar(4000),  
    @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,  
    @updateable_row bit = 1        --  used to override history verbose level to decide  
                                    --  whether the row being added can be updated by another.      
)  
AS  
BEGIN  
    set nocount on   
    DECLARE @current_time datetime  
            ,@start_time datetime  
            ,@duration int  
            ,@average_commands int  
            ,@delivery_rate float  
            ,@error_id int  
            ,@retcode int  
            ,@idle int  
            ,@succeed int  
            ,@startup int  
            ,@retry int  
            ,@inprogress int  
            ,@failure int  
            ,@lastrow_timestamp timestamp  
            ,@publisher sysname  
            ,@publisher_db sysname  
            ,@publication sysname  
            ,@agent_name nvarchar(100)  
            ,@last_delivered_commands int  
            ,@last_delivered_transactions int  
            ,@latest_delivered_commands int  
            ,@latest_delivered_transactions int  
            ,@latest_delivery_rate float  
            ,@last_delivery_rate float             --  was declare as int for perfmon,but never used in perfmon, change back to float to eb consistant with other rates  
            ,@last_delivery_latency int  
            ,@last_delivery_time int  
            ,@avg_delivery_rate float  
            ,@avg_delivery_latency int  
            ,@total_delivery_time int  
            ,@agentclassname sysname  
            ,@last_xact_seqno varbinary(16)  
            ,@prev_runstatus int  
            ,@prev_start_time datetime  
            ,@existing_row_updateble bit  
    --   
    --  security check  
    --  only db_owner can execute this  
    --   
    if (is_member ('db_owner') != 1)   
    begin  
        raiserror(14260, 16, -1)  
        return (1)  
    end  
  
    --   
    --  security check  
    --  Has to be executed from distribution database  
    --   
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)  
    begin  
        raiserror(21482, 16, -1, 'sp_MSadd_logreader_history', 'distribution')  
        return (1)  
    end  
      
    --    
    --  Status const defined in sqlrepl.h   
     --   
    select @startup = 1  
            ,@succeed = 2  
            ,@inprogress = 3  
            ,@idle = 4  
            ,@retry = 5  
            ,@failure = 6  
            ,@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  
  
    --  The initial values provided below are needed for the first entry for the agent that  
    --  appears in MSlogreader_history.  Normally, this occurs when the initialization message  
    --  is logged (@runstatus = 1).  It can also occur when an error message is logged before  
    --  the startup message is written. For message subsequent to the first message, these  
    --  values are overwritten.  
  
    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  
  
        if(@update_existing_row = 1 and substring(@comments, 1, 7) = N'<stats ')  
        set @update_existing_row = 0  
  
  
    --  Get start_time for latest agent run  --   
    select @agent_name = N'DEFAULTAGENT'  
    select @agent_name = isnull(name, N'DEFAULTAGENT') from MSlogreader_agents where id = @agent_id  
    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),  
            @last_xact_seqno = xact_seqno  
            ,@existing_row_updateble = updateable_row  
        FROM MSlogreader_history with (rowlock)  
            WHERE agent_id = @agent_id  
                and comments not like N'<stats state%'  
                ORDER BY timestamp DESC  
    END  
    ELSE  
    BEGIN  
        SELECT TOP 1   
            @prev_runstatus = runstatus,  
            @prev_start_time = start_time,  
            @last_xact_seqno = xact_seqno,  
            @last_delivery_latency = isnull(delivery_latency, 0)  
           ,@existing_row_updateble = updateable_row  
        FROM MSlogreader_history with (rowlock)  
            WHERE agent_id = @agent_id  
            ORDER BY timestamp  DESC  
  
        IF @prev_runstatus IN (@startup, @retry)  
        BEGIN  
            SELECT @start_time = @prev_start_time  
        END  
  
        --  New logreader message "Validating publisher" has runstatus = 1  
        --  and precedes message "Initializing". Only the first of these startup  
        --  messages should set startup for Perfmon counters.  
        IF @prev_runstatus != @startup  
        BEGIN  
            --  Set Startup Perfmon counters  
            dbcc addinstance ("SQL Replication Logreader", @agent_name)  
            dbcc setinstance ("SQL Replication Logreader", "Logreader:Delivery Latency", @agent_name, @last_delivery_latency )  
        END  
    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 xact_seqno to last value if not already set  
    if (@xact_seqno = 0x or @xact_seqno is NULL) and @last_xact_seqno is not null  
    begin  
        select @xact_seqno = @last_xact_seqno  
    end  
      
    --  Set Perfmon counters  
    --  Note that Startup perfmon counters are set above  
    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  
  
    --    
    --  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 sys.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  
        if (@runstatus = @idle)  
        begin  
            UPDATE MSlogreader_history SET runstatus = @runstatus, time = @current_time,  
            duration = @duration,comments = @comments,  
            error_id = case @error_id when 0 then error_id else @error_id end  
            ,updateable_row = @updateable_row  
            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 = case @error_id when 0 then error_id else @error_id end  
            ,updateable_row = @updateable_row  
            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, @total_delivery_time, @delivered_transactions, @delivered_commands,  
                @average_commands, @avg_delivery_rate, @avg_delivery_latency, @error_id, NULL, @updateable_row)  
        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, @updateable_row)  
    end  
  
    --  Get named information   
    select @publisher = srvname, @publisher_db = publisher_db, @publication = publication,  
        @agent_name = isnull(name, N'DEFAULTAGENT') from master.dbo.sysservers, MSlogreader_agents where  
        id = @agent_id and  
        publisher_id = srvid  
  
     --  Raise the appropriate error  
    if @do_raiserror = 1  
    begin  
        select @agentclassname = formatmessage(14552)  
  
        --  Only use the first 255 characters of the comment  
        select @comments = left(@comments, 255)  
  
        exec sys.sp_MSrepl_raiserror @agentclassname, @agent_name, @runstatus, @comments  
    end  
  
    IF @@ERROR <> 0  
    BEGIN  
       RETURN (1)  
    END  
END

C# and I

May 2, 2012

sp_MSadd_merge_history (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSadd_merge_anonymous_agent (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSadd_merge_agent (Transact-SQL MetaData) Definition

Definition:

MetaData:

sp_MSadd_logreader_history (Transact-SQL MetaData) Definition

Definition:

MetaData:

Total Pageviews

Where i go...