SQLize Online / PHPize Online  /  SQLtest Online

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