Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenum_replication_status

  No additional text.


Syntax
create procedure sys.sp_MSenum_replication_status
(
    @agent_type int = 0
)
as
begin
    set nocount on
    declare @anonymous_mask int,
                @all_but_misc_agents int,
                @all_agents int,
                @snapshot_agent int,
                @logreader_agent int,
                @distrib_agent int,
                @merge_agent int,
                @queue_agent int,
                @misc_agent int

    select @anonymous_mask = 0x80000000,
                @all_but_misc_agents = -1,
                @all_agents = 0,
                @snapshot_agent = 1,
                @logreader_agent = 2,
                @distrib_agent = 3,
                @merge_agent = 4,
                @queue_agent = 9,
                @misc_agent = 5

    -- if we want all agents excluding the misc agents then
    -- that is the same as requesting all of the agents since
    -- in this proc we do not do any work on misc agents
    if @agent_type = @all_but_misc_agents
    begin
        select @agent_type = @all_agents
    end

    
    -- SNAPSHOT AGENT STATUS
    
    (  select 'publisher' 		= upper(ss.srvname collate database_default),
            'publisher_db' 		= mssa.publisher_db,
            'publication' 		= mssa.publication,
            'publication_type' 	= mssa.publication_type,
            'agent_type' 		= @snapshot_agent,
            'status' 			= mssh.runstatus,
            'agent_id' 		       = mssa.id,
            'agent_name' 		= mssa.name,
            'job_id'            = cast(mssa.job_id as uniqueidentifier),
            'time_stamp' 		= mssh.time,
            'publisher_srvid'	= ss.srvid
    from master.dbo.sysservers ss with (nolock),
            dbo.MSsnapshot_history mssh with (nolock),
            dbo.MSsnapshot_agents mssa with (nolock)
    where @agent_type in (@all_agents, @snapshot_agent)
            and ss.srvid = mssa.publisher_id
            and mssh.agent_id = mssa.id
            and mssh.timestamp = (select max(timestamp)
                                                from dbo.MSsnapshot_history mssh2 with (nolock)
                                                where mssh2.agent_id = mssa.id)

    UNION

    select upper(ss.srvname collate database_default),
            mssa.publisher_db,
            mssa.publication,
            mssa.publication_type,
            @snapshot_agent,
            0,
            mssa.id,
            mssa.name,
            cast(mssa.job_id as uniqueidentifier),
            null,
            'publisher_srvid'	= ss.srvid
    from master.dbo.sysservers ss with (nolock),
            dbo.MSsnapshot_agents mssa with (nolock)
    where @agent_type in (@all_agents, @snapshot_agent)
            and ss.srvid = mssa.publisher_id
            and not exists (select *
                                    from dbo.MSsnapshot_history mssh with (nolock)
                                    where mssh.agent_id = mssa.id)
    )
    
    -- LOGREADER AGENT STATUS
    
    UNION ALL

    (    select upper(ss.srvname collate database_default),
            msla.publisher_db,
            'ALL',
            0,
            @logreader_agent,
            mslh.runstatus,
            msla.id,
            msla.name,
            cast(msla.job_id as uniqueidentifier),
            mslh.time,
            'publisher_srvid'	= ss.srvid
    from master.dbo.sysservers ss with (nolock),
            dbo.MSlogreader_history mslh with (nolock),
            dbo.MSlogreader_agents msla with (nolock)
    where @agent_type in (@all_agents, @logreader_agent)
            and ss.srvid = msla.publisher_id
            and mslh.agent_id = msla.id
            and mslh.timestamp = (select max(timestamp)
                                                from dbo.MSlogreader_history mslh2 with (nolock)
                                                where mslh2.agent_id = msla.id)

    UNION

    select upper(ss.srvname collate database_default),
            msla.publisher_db,
            'ALL',
            0,
            @logreader_agent,
            0,
            msla.id,
            msla.name,
            cast(msla.job_id as uniqueidentifier),
            NULL,
            'publisher_srvid'	= ss.srvid
    from master.dbo.sysservers ss with (nolock),
            dbo.MSlogreader_agents msla with (nolock)
    where @agent_type in (@all_agents, @logreader_agent)
            and ss.srvid = msla.publisher_id
            and not exists (select *
                                    from dbo.MSlogreader_history mslh with (nolock)
                                    where mslh.agent_id = msla.id)
    )
    
    -- DISTRIBUTION AGENT STATUS
    
    UNION ALL
    (
    -- Normal agents with or without history
    select distinct upper(ss.srvname collate database_default),
                msda.publisher_db,
                msp.publication,
                msp.publication_type,
                N'agent_type' = @distrib_agent,
                N'runstatus' = isnull(msdh.runstatus,0),
                msda.id,
                msda.name,
                cast(msda.job_id as uniqueidentifier),
                N'time' = msdh.time,
                N'publisher_srvid' = ss.srvid
        from dbo.MSdistribution_agents msda with (nolock)
            join master.dbo.sysservers ss with (nolock)
                on ss.srvid = msda.publisher_id
            left outer join dbo.MSdistribution_history msdh with (nolock)
                on msda.id = msdh.agent_id
                and msdh.timestamp = (select max(timestamp)
                                                from dbo.MSdistribution_history msdh2 with (nolock)
                                                where msdh2.agent_id = msdh.agent_id)
            join dbo.MSsubscriptions mss with (nolock)
                on mss.agent_id = msda.id
            join dbo.MSpublications msp with (nolock)
                on msp.publication_id = mss.publication_id
        where @agent_type in (@all_agents, @distrib_agent)
            and msda.subscriber_id >= 0 -- skip virtual entries
            and msda.anonymous_agent_id is null -- pick only normal

    UNION ALL

    -- Anonymous agents with or without history
    -- Anonymous agents will not have entries in MSsubscriptions
    select distinct upper(ss.srvname collate database_default),
                msda.publisher_db,
                msp.publication,
                msp.publication_type,
                N'agent_type' = @distrib_agent | @anonymous_mask,
                N'runstatus' = isnull(msdh.runstatus,0),
                msda.id,
                msda.name,
                cast(msda.job_id as uniqueidentifier),
                N'time' = msdh.time,
                N'publisher_srvid' = ss.srvid
        from dbo.MSdistribution_agents msda with (nolock)
            join master.dbo.sysservers ss with (nolock)
                on ss.srvid = msda.publisher_id
            left outer join dbo.MSdistribution_history msdh with (nolock)
                on msda.id = msdh.agent_id
                and msdh.timestamp = (select max(timestamp)
                                                from dbo.MSdistribution_history msdh2 with (nolock)
                                                where msdh2.agent_id = msdh.agent_id)
            join dbo.MSpublications msp with (nolock)
                on msp.publisher_id = msda.publisher_id
                    and msp.publisher_db = msda.publisher_db
                    and msp.publication = msda.publication
        where @agent_type in (@all_agents, @distrib_agent)
            and msda.subscriber_id >= 0 -- skip virtual entries
            and msda.anonymous_agent_id is not null -- pick only anonymous
    )
    
    -- MERGE AGENT STATUS
    
    UNION ALL
    -- all subscription with its agent that has run with latest run status
    ( select upper(ss.srvname collate database_default),
            msma.publisher_db,
            msma.publication,
            2,
            case when msma.anonymous_subid is not null then @merge_agent | @anonymous_mask else @merge_agent end,
            msmh.runstatus,
            msma.id,
            msma.name,
            cast(msma.job_id as uniqueidentifier),
            null,
            'publisher_srvid'	= ss.srvid
    from master.dbo.sysservers ss with (nolock),
            dbo.MSmerge_sessions msmh with (nolock),
            dbo.MSmerge_agents msma with (nolock)
    where @agent_type in (@all_agents, @merge_agent)
            and ss.srvid = msma.publisher_id
            and msmh.agent_id = msma.id
            and msmh.session_id = (select top 1 msmh2.session_id
                                   from dbo.MSmerge_sessions msmh2 with (nolock)
                                   where msmh2.agent_id = msma.id
                                   order by msmh2.session_id desc)

    UNION
    -- all subscription with its agent that has not yet run with latest run status=0
    select upper(ss.srvname collate database_default),
            msma.publisher_db,
            msma.publication,
            2,
            @merge_agent,
            0,
            msma.id,
            msma.name,
            cast(msma.job_id as uniqueidentifier),
            null,
            'publisher_srvid'	= ss.srvid
    from master.dbo.sysservers ss with (nolock),
            dbo.MSmerge_agents msma with (nolock)
    where @agent_type in (@all_agents, @merge_agent)
            and ss.srvid = msma.publisher_id
            and not exists (select *
                                    from dbo.MSmerge_sessions msmh with (nolock)
                                    where msmh.agent_id = msma.id)
    )
    
    -- QUEUE READER AGENT STATUS
    
    UNION ALL

    select upper(@@servername),
            db_name(),
            'ALL',
            0,
            @queue_agent,
            msqh.runstatus,
            msqa.id,
            msqa.name,
            cast(msqa.job_id as uniqueidentifier),
            msqh.time,
            'publisher_srvid' = (select top 1 srvid from master.dbo.sysservers where srvname = @@servername)
    from dbo.MSqreader_history msqh with (nolock),
            dbo.MSqreader_agents msqa with (nolock)
    where @agent_type in (@all_agents, @queue_agent)
            and msqh.agent_id = msqa.id
            and msqh.timestamp = (select max(msqh2.timestamp)
                                                from dbo.MSqreader_history msqh2 with (nolock)
                                                where msqh2.agent_id = msqa.id)
end

 
Last revision 2008RTM
See also

  sp_instdist (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSload_tmp_replication_status (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