Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSenum_misc_agents

  No additional text.


Syntax
create procedure sys.sp_MSenum_misc_agents
as
begin
    SET NOCOUNT ON

    declare @timestamp timestamp
            ,@curdate datetime

    -- timestamp : Not supported, but returned to be consistent with other sp_MSenum_replication_agent
    -- result set.
    SELECT @timestamp = 0
            ,@curdate = getdate()

    
    -- return the resultset now
    
    select j.name, 'agent_type' = c.name,
            -- Job status - check sysjobactivity to see if the job
            -- is currently running or else use the runstatus from
            -- sysjobhistory
            'status' =
            case when (ja.start_execution_date <= @curdate
                        and ja.stop_execution_date is null) then 3
                else
                    case isnull(jh.run_status, 5) -- mapped to never run
                        when 0 then 5   -- Fail mapping
                        when 1 then 2   -- Success mapping
                        when 2 then 5   -- Retry mapping
                        when 3 then 2   -- Shutdown mapping
                        when 4 then 3   -- Inprogress mapping
                        when 5 then 0   -- Unknown is mapped to never run
                    end
            end,
            jh.message, 'start_time' = convert(nvarchar(8), jh.run_date) + N' ' +
                                       stuff(stuff(right(convert(nvarchar(7), jh.run_time + 1000000), 6), 5, 0, N':'), 3, 0, N':') +
                                       + N'.000',
            jh.run_duration,
            'job_id' = convert(binary(16), j.job_id), 'local_timestamp' = @timestamp
    from
            msdb.dbo.sysjobs AS j
                JOIN msdb.dbo.syscategories AS c
                    ON j.category_id = c.category_id
                        AND j.category_id in (11, 12,16,17,18)
                -- may or may not have job activity
                LEFT OUTER JOIN msdb.dbo.sysjobactivity AS ja
                    ON j.job_id = ja.job_id
                        AND ja.session_id = (select max(session_id)
                                            from msdb.dbo.sysjobactivity as ja2)
                -- May or may not have job history
                LEFT OUTER JOIN msdb.dbo.sysjobhistory AS jh
                    ON j.job_id = jh.job_id
                        AND jh.instance_id = (select max(instance_id)
                                            from msdb.dbo.sysjobhistory AS jh2
                                            where jh2.job_id = j.job_id)
    order by j.job_id asc

end

 
Last revision 2008RTM
See also

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