Trying to determine the status of a job in TSQL can be difficult if you are trying to do it through automated code. You can't use a tempory table to store the results of sp_help_job since it already does this, and nested inserts of executing procedures is not allowed. To get around this limitation, you can use a CLR TVF to return the output of sp_help_job as a table.
This function uses an series of enums to transpose the integer values of various columns into the correct string information for display. It must be registered as EXTERNAL_ACCESS since it uses a second non-context connection to execute the procedure since this is not allowed on a context connection in a function.
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
/*===========================================================================
File: fn_ReadErrorLog.cs
Object Type: Table Valued User Defined Function
Permission Set: EXTERNAL_ACCESS
Summary: Retrieves the results of sp_help_job as a table.
Parameter: None
Date: May 8, 2008
---------------------------------------------------------------------
THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
PARTICULAR PURPOSE.
============================================================================= */
public partial class UserDefinedFunctions
{
public partial class Enums
{
public enum SQLAgent_Job_Type
{
Job_Has_No_Target_Servers = 0,
Local_Job = 1,
Multiserver_Job = 2
}
public enum SQLAgent_Job_LastRunOutcome
{
Failed = 0,
Succeeded = 1,
Canceled = 3,
Unknown = 5
}
public enum SQLAgent_Job_ExecutionStatus
{
Returns_only_those_jobs_that_are_not_idle_or_suspended = 0,
Executing = 1,
Waiting_for_thread = 2,
Between_retries = 3,
Idle = 4,
Suspended = 5,
Performing_completion_actions = 7
}
}
[Microsoft.SqlServer.Server.SqlFunction(Name = "fn_AgentJobStatus",
FillRowMethodName = "FillRowAgentJobStatus",
DataAccess = DataAccessKind.Read,
TableDefinition = @"job_id uniqueidentifier, name sysname, enabled int,
description nvarchar(512), category sysname, owner sysname,
date_created datetime, date_modified datetime, last_run_date int,
last_run_time int, last_run_outcome nvarchar(100), next_run_date int,
next_run_time int, current_execution_status nvarchar(100),
current_execution_step sysname, type nvarchar(100)")]
public static IEnumerable InitMethodAgentJobStatus()
{
List<object[]> logitems = new List<object[]>();
string servername;
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select serverproperty('ServerName')";
servername = (string)cmd.ExecuteScalar();
}
conn.Close();
}
using (SqlConnection connection = new SqlConnection("Server=" + servername + ";Integrated Security=true"))
{
connection.Open();
SqlCommand sqlCommand = connection.CreateCommand();
sqlCommand.CommandText = "exec msdb..sp_help_job";// +LogNumber.ToString();
SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
while (sqlDataReader.Read())
{
object[] item = new object[16];
item.SetValue(sqlDataReader["job_id"].ToString(), 0);
item.SetValue(sqlDataReader["name"].ToString(), 1);
item.SetValue(sqlDataReader["enabled"].ToString(), 2);
item.SetValue(sqlDataReader["description"].ToString(), 3);
item.SetValue(sqlDataReader["category"].ToString(), 4);
item.SetValue(sqlDataReader["owner"].ToString(), 5);
item.SetValue(sqlDataReader["date_created"].ToString(), 6);
item.SetValue(sqlDataReader["date_modified"].ToString(), 7);
item.SetValue(sqlDataReader["last_run_date"].ToString(), 8);
item.SetValue(sqlDataReader["last_run_time"].ToString(), 9);
item.SetValue(Enum.GetName(typeof(Enums.SQLAgent_Job_LastRunOutcome), Int32.Parse(sqlDataReader["last_run_outcome"].ToString())).Replace("_", " "), 10);
item.SetValue(sqlDataReader["next_run_date"].ToString(), 11);
item.SetValue(sqlDataReader["next_run_time"].ToString(), 12);
item.SetValue(Enum.GetName(typeof(Enums.SQLAgent_Job_ExecutionStatus), Int32.Parse(sqlDataReader["current_execution_status"].ToString())).Replace("_", " "), 13);
item.SetValue(sqlDataReader["current_execution_step"].ToString(), 14);
item.SetValue(Enum.GetName(typeof(Enums.SQLAgent_Job_Type), Int32.Parse(sqlDataReader["type"].ToString())).Replace("_", " "), 15);
logitems.Add(item);
}
}
return logitems;
}
public static void FillRowAgentJobStatus(object obj, out SqlGuid job_id,
out SqlString name, out SqlInt32 enabled, out SqlString description,
out SqlString category, out SqlString owner, out SqlDateTime date_created,
out SqlDateTime date_modified, out SqlInt32 last_run_date, out SqlInt32 last_run_time,
out SqlString last_run_outcome, out SqlInt32 next_run_date, out SqlInt32 next_run_time,
out SqlString current_execution_status, out SqlString current_execution_step, out SqlString type)
{
object[] item = (object[])obj;
job_id = SqlGuid.Parse(item[0].ToString());
name = (SqlString)item[1].ToString();
enabled = SqlInt32.Parse(item[2].ToString());
description = (SqlString)item[3].ToString();
category = (SqlString)item[4].ToString();
owner = (SqlString)item[5].ToString();
date_created = SqlDateTime.Parse(item[6].ToString());
date_modified = SqlDateTime.Parse(item[7].ToString());
last_run_date = SqlInt32.Parse(item[8].ToString());
last_run_time = SqlInt32.Parse(item[9].ToString());
last_run_outcome = (SqlString)item[10].ToString();
next_run_date = SqlInt32.Parse(item[11].ToString());
next_run_time = SqlInt32.Parse(item[12].ToString());
current_execution_status = (SqlString)item[13].ToString();
current_execution_step = (SqlString)item[14].ToString();
type = (SqlString)item[15].ToString();
}
};