October 23, 2012 at 3:30 pm
For your viewing pleasures... here is another working sample tested on sql 8 - 10.50
IF EXISTS ( SELECT * FROM tempdb..sysobjects WHERE id = OBJECT_ID(N'#tmp_enum_jobs') )
DROP TABLE #tmp_enum_jobs;
ELSE
CREATE TABLE #tmp_enum_jobs (
job_id UNIQUEIDENTIFIER NOT NULL
, last_run_date NVARCHAR(20) NOT NULL
, last_run_time NVARCHAR(20) NOT NULL
, next_run_date NVARCHAR(20) NOT NULL
, next_run_time NVARCHAR(20) NOT NULL
, next_run_schedule_id INT NOT NULL
, requested_to_run INT NOT NULL
, request_source INT NOT NULL
, request_source_id NVARCHAR(256) NULL
, running INT NOT NULL
, current_step INT NOT NULL
, current_retry_attempt INT NOT NULL
, job_state INT NOT NULL );
DECLARE @job_owner NVARCHAR(256)
DECLARE @is_sysadmin INT
SET @is_sysadmin = ISNULL(IS_SRVROLEMEMBER('sysadmin'), 0)
SET @job_owner = SUSER_SNAME()
INSERT INTO #tmp_enum_jobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
UPDATE #tmp_enum_jobs
SET last_run_time = RIGHT('000000' + last_run_time, 6)
, next_run_time = RIGHT('000000' + next_run_time, 6);
SELECT @@SERVERNAME AS Server_Name
, j.name AS Job_Name
, j.enabled AS Enabled
, CASE t.running
WHEN 1 THEN 'Running'
ELSE CASE h.run_status
WHEN 2 THEN 'Inactive'
WHEN 4 THEN 'Inactive'
ELSE 'Completed'
END
END AS Current_Status
, COALESCE(t.current_step, 0) AS Current_Step
, CASE WHEN t.last_run_date > 0
THEN CONVERT (DATETIME, SUBSTRING(t.last_run_date, 1, 4) + '-'
+ SUBSTRING(t.last_run_date, 5, 2) + '-'
+ SUBSTRING(t.last_run_date, 7, 2) + ' '
+ SUBSTRING(t.last_run_time, 1, 2) + ':'
+ SUBSTRING(t.last_run_time, 3, 2) + ':'
+ SUBSTRING(t.last_run_time, 5, 2) + '.000', 121)
ELSE NULL
END AS Last_Run_Time
, CASE h.run_status
WHEN 0 THEN 'Fail'
WHEN 1 THEN 'Success'
WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancel'
WHEN 4 THEN 'In progress'
END AS Last_Run_Outcome
/*, CASE WHEN h.run_duration > 0
THEN ( h.run_duration / 1000000 ) * ( 3600 * 24 )
+ ( h.run_duration / 10000 % 100 ) * 3600 + ( h.run_duration
/ 100 % 100 )
* 60 + ( h.run_duration % 100 )
ELSE NULL
END AS Last_Run_Duration */
, CASE WHEN LEN(h.run_duration) > 6 THEN STUFF(STUFF(h.run_duration,LEN(h.run_duration)-3,0,':'),LEN(h.run_duration),0,':')
ELSE STUFF(STUFF(REPLACE(STR(h.run_duration,6,0),' ','0'),3,0,':'),6,0,':')
END AS Last_Run_Duration
FROM #tmp_enum_jobs t
LEFT JOIN msdb.dbo.sysjobs j (NOLOCK) ON t.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c (NOLOCK) ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h (NOLOCK) ON t.job_id = h.job_id
AND t.last_run_date = h.run_date
AND t.last_run_time = h.run_time
--AND h.step_id = 0
WHERE t.running = 1
DROP TABLE #tmp_enum_jobs;
Cheers,
~Leon
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply