Creating a list of SQL Server Agent Job Status in RealTime

Essentially I want to replicate the functionality of the SQL Agent Job Monitor.

I am trying to find the correct system view or stored procedure to get a list of SQL Agent Jobs that are executing. Similar to what you would see when you use the SQL Agent Job Monitor to oversee SQL Agent Job execution. (I have verified that I have sysadmin permissions.)

There is a stored procedure called msdb.dbo.sp_help_jobactivity however it still does not return information on currently running jobs. There is a system table called 'sysjobactivity' that contains job information but not the runtime status of jobs. In the BOL there is a detailed list of all of the tables that SQL Server Agent uses...but none of them seem to give runtime information.

Do I need to join the sysjobactivity table to some other process table to get realtime status?

...cordell...

This should get you started:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx

...except that the author is wrong. You can't run sp_help_job into a table because it calls a nested stored proc.

This, however, should actually work:

declare @.job_owner_name varchar(100)
set @.job_owner_name = '' -- insert job owner name here, required for extended stored proc

if OBJECT_ID('tempdb..#xp_results') is not null
drop table #xp_results

CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

insert into #xp_results
exec master.dbo.xp_sqlagent_enum_jobs 1, @.job_owner_name

select name, * from #xp_results rj
inner join msdb.dbo.sysjobs sj
on sj.job_id = rj.job_id
where rj.running = 1

Creating a list of SQL Server Agent Job Status in RealTime

Essentially I want to replicate the functionality of the SQL Agent Job Monitor.

I am trying to find the correct system view or stored procedure to get a list of SQL Agent Jobs that are executing. Similar to what you would see when you use the SQL Agent Job Monitor to oversee SQL Agent Job execution. (I have verified that I have sysadmin permissions.)

There is a stored procedure called msdb.dbo.sp_help_jobactivity however it still does not return information on currently running jobs. There is a system table called 'sysjobactivity' that contains job information but not the runtime status of jobs. In the BOL there is a detailed list of all of the tables that SQL Server Agent uses...but none of them seem to give runtime information.

Do I need to join the sysjobactivity table to some other process table to get realtime status?

...cordell...

This should get you started:
http://www.microsoft.com/technet/abouttn/flash/tips/tips_060804.mspx

...except that the author is wrong. You can't run sp_help_job into a table because it calls a nested stored proc.

This, however, should actually work:

declare @.job_owner_name varchar(100)
set @.job_owner_name = '' -- insert job owner name here, required for extended stored proc

if OBJECT_ID('tempdb..#xp_results') is not null
drop table #xp_results

CREATE TABLE #xp_results
(job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL,
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL,
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

insert into #xp_results
exec master.dbo.xp_sqlagent_enum_jobs 1, @.job_owner_name

select name, * from #xp_results rj
inner join msdb.dbo.sysjobs sj
on sj.job_id = rj.job_id
where rj.running = 1

Creating A Boolean Computed Column

Consider the following columns:

EmpID, Status, TermDate

I want a computed column called OkToDisable as a bit value: True if the TermDate is any day before today.

The logic is if DATEDIFF(day, TermDate, GETDATE()) is greater than 0 then the column should be true, otherwise false.

I cant figure out how to do this... SQL keeps complaining that it can't validate the formula.

I was able to create a computed colum from 'DATEDIFF(day, TermDate, GETDATE())' that shows the number of days past the term date, but if I change it to: 'DATEDIFF(day, TermDate, GETDATE()) > 0' it says it can't validate the formula.

Can't you create boolean computed fields?

Thanks.

J

There is no boolean type in T-SQL. The [bit] type is an number type. You can do what you want with CASE:

CASE WHEN DATEDIFF(day, TermDate, GETDATE()) > 0 THEN 1 ELSE 0 END

Steve Kass
Drew University
http://www.stevekass.com
|||

Bit is a boolean type, no? )

I had tried the CASE but I couldn't get it to work properly... It helps when you use the proper syntax. (I had left off the END statement)

Then I had to use a cast, otherwise it was an integer.

Thanks for the reply.

J