Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_agentstatussummary

  No additional text.


Syntax


-- Name:
--        sp_MSrepl_summaryagentstatus

-- Description:
--        Called by sp_MSrepl_summarypublication in the context of the
--        distribution database to get snapshot and log reader agent status
--        for a publication

-- Security:
--        Public, PAL access
--  Requires Certificate signature for catalog access

-- Returns:
--        0 : success
--        1 : failure

CREATE PROCEDURE sys.sp_MSrepl_agentstatussummary
(
    @publisher     sysname,
    @publisher_db  sysname,
    @publication   sysname,
    @snap_status   int           OUTPUT,
    @snap_time     datetime      OUTPUT,
    @snap_comments nvarchar(255) OUTPUT,
    @snap_duration int OUTPUT,
    @log_status    int           OUTPUT,
    @log_time      datetime      OUTPUT,
    @log_comments  nvarchar(255) OUTPUT,
    @log_duration int OUTPUT
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE    @snap_agent_id int,
            @log_agent_id  int,
            @retcode       int

    
    -- PAL check and validate publication
    
    exec @retcode = sp_MSreplcheck_pull    @publication = @publication,
                                        @raise_fatal_error = 0
    IF (@@error != 0) or (@retcode != 0)
        RETURN 1

    -- Initialize output params
    SELECT @snap_status   = 0,
            @snap_time     = NULL,
            @snap_comments = formatmessage(14196),
            @snap_duration = 0,
            @log_status    = 0,
            @log_time      = NULL,
            @log_comments  = formatmessage(14196),
            @log_duration = 0

    -- Get snapshot agent id (skip dynamic snapshot agents)
    SELECT    @snap_agent_id = SA.id
    FROM    dbo.MSsnapshot_agents AS SA,
            sys.servers AS SS
    WHERE    SA.publisher_id = SS.server_id
        AND    SA.publisher_db = @publisher_db
        AND    SA.publication  = @publication
        AND    UPPER(SS.name COLLATE DATABASE_DEFAULT) = UPPER(@publisher) COLLATE DATABASE_DEFAULT
        AND    SA.dynamic_filter_login is NULL
        AND    SA.dynamic_filter_hostname is NULL

    -- Get logreader agent id
    SELECT    @log_agent_id = LA.id
    FROM    dbo.MSlogreader_agents AS LA,
            sys.servers SS
    WHERE    LA.publisher_id = SS.server_id
      AND    LA.publisher_db = @publisher_db
      AND    UPPER(SS.name COLLATE DATABASE_DEFAULT) = UPPER(@publisher) COLLATE DATABASE_DEFAULT

    -- Get snapshot agent summary
    IF EXISTS
    (
        SELECT    *
        FROM    dbo.MSsnapshot_history with (READPAST)
        WHERE    agent_id = @snap_agent_id
    )
    BEGIN
        SELECT TOP(1)
                 @snap_status   = runstatus,
                 @snap_time     = sys.fn_replformatdatetime(start_time),
                 @snap_comments = comments,
                 @snap_duration = duration
        FROM     dbo.MSsnapshot_history with (READPAST)
        WHERE     agent_id = @snap_agent_id
        ORDER BY time DESC, timestamp DESC
    END

    -- Get logreader agent summary
    IF EXISTS
    (
        SELECT    *
        FROM    dbo.MSlogreader_history with (READPAST)
        WHERE    agent_id = @log_agent_id
    )
    BEGIN
        SELECT     TOP(1)
                 @log_status    = runstatus,
                 @log_time      = sys.fn_replformatdatetime(start_time),
                 @log_comments  = comments,
                 @log_duration = duration
        FROM     dbo.MSlogreader_history with (READPAST)
        WHERE     agent_id = @log_agent_id
        ORDER BY time DESC, timestamp DESC
    END

    RETURN (0)
END

 
Last revision SQL2008SP1
See also

  sp_MSdrop_replcom (Procedure)
sp_MSrepl_publicationsummary (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash