How to get job name and status from sql sever to textbox follow time schedule recurring

Question

Now, i can connect MS SQL Server 2000 via connection string and get job name and job

status in SQL Server Agent. but i want to show name and status continuously follow time

schedule recurring for monitoring JOB. Please suggestion how to do. (using MS Visual

Studio 2008 (C#))

public  void ConnectJOB() 
    {
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        try
        {
            SQLServer.Connect("10.17.13.70", "sa", "");
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

        SQLDMO.JobServer jobServer = SQLServer.JobServer;
        foreach (SQLDMO.Job job in jobServer.Jobs)
        {
            string jobName = job.Name;
            SQLDMO.SQLDMO_JOBEXECUTION_STATUS status = job.CurrentRunStatus;


            richTextBox1.Text = jobName.ToString();
            richTextBox2.Text = status.ToString();
        }

Answer 1

You could use straight T-SQL statements to grab the info about jobs and their history (status) from the sysjobs and sysjobhistory tables in the msdb database - something like this:

public class JobInfo
{
    public string Name { get; set; }
    public Guid ID { get; set; }
    public byte Enabled { get; set; }
    public int Status { get; set; }
}

public List<JobInfo> GetJobsAndStatus()
{
   List<JobInfo> _jobs = new List<JobInfo>();

   string sqlJobQuery = "select j.job_id, j.name, j.enabled, jh.run_state " +
                        "from sysjobs j inner join sysjobhistory jh on j.job_id = jh.job_id";

   // create SQL connection and set up SQL Command for query
   using(SqlConnection _con = new SqlConnection("server=10.17.13.70;database=msdb;user id=sa;pwd=XXXXXXX"))
   using(SqlCommand _cmd = new SqlCommand(sqlJobQuery, _con))
   {
       // open connection
       _con.Open();

       // create SQL Data Reader and grab data
       using(SqlDataReader rdr = _cmd.ExecuteReader())
       {
           // as long as we get information from the reader
           while(rdr.Read())
           {
               Guid jobID = rdr.GetGuid(0);        // read Job_id
               string jobName = rdr.GetString(1);  // read Job name
               byte jobEnabled = rdr.GetByte(2);   // read Job enabled flag
               int jobStatus = rdr.GetInt32(3);    // read run_state from jobhistory

               _jobs.Add(new JobInfo { Name = jobName, 
                                       ID = jobID, 
                                       Enabled = jobEnabled,
                                       Status = jobStatus });
    }

    rdr.Close();
}

// close connection again
_con.Close();     

return _jobs;
}

Once you have that list of jobs and their history/status, you can do with it whatever you like, e.g. grab the first one and put the values into textboxes, or whatever you want.

Answer 2

You might want to check out the SQL Server Agent Job system tables in SQL Server 2000 and grab the information from there.

For an overview of the tables available, check out this wiki post here. The main tables would be sysjobs and sysjobhistory which you can inspect and find the information needed. One thing to watch out for: those tables are in the msdb database and you need fairly elevated privileges to be able to read those.

This query will return all jobs currently in the system, their job_ID (GUID), name, and enabled status:

select job_id, name, enabled 
from msdb..sysjobs

Use that information, and join with the sysjobhistory or other relevant tables, to get the information you're looking for.

Update:

OK, here's the complete code sequence

// define the query to run
string sqlJobQuery = "select j.job_id, j.name, j.enabled, jh.run_status " +
                     "from sysjobs j inner join sysjobhistory jh on j.job_id = jh.jobid"

// create SQL connection and set up SQL Command for query
using(SqlConnection _con = new SqlConnection("server=10.17.13.70;database=msdb;user id=sa;pwd=XXXXXX")
using(SqlCommand _cmd = new SqlCommand(sqlJobQuery, _con))
{
    // open connection
    _con.Open();

    // create SQL Data Reader and grab data
    using(SqlDataReader rdr = _cmd.ExecuteReader())
    {
        // as long as we get information from the reader
        while(rdr.Read())
        {
            Guid jobID = rdr.GetGuid(0);        // read Job_id
            string jobName = rdr.GetString(1);  // read Job name
            int jobEnabled = rdr.GetInt(2);     // read Job enabled flag
            int jobRunStatus = rdr.GetInt(3);   // read job run status

            // do something with your data, e.g. store it into a list or something
        }

        rdr.Close();
    }

    // close connection again
    _con.Close();     
}