How to monitor SQL Server Agent Job info in C#

Question

I need to create an application for monitoring SQL Server 2000 Agent Job status and info when Job occur same as show on Windows application event log. Now I connect to the database already via a connection string, but I don't know how to get the status and info from Job.

I need to show status and info on Textbox.

What do you suggestion how to do.

Developer tools :

1. MS SQL Sever 2000 SP4
2. MS Visual Studio 2008 (C#)

I am a rookie programmer.

Answer 1

i can do this already...

i select form table "Sysjobserver" in database "msdb" for read status, date, time of job that i want.

use this code

public void GetJobsAndStatus()
        {
            string sqlJobQuery = "select j.job_id, j.name, j.enabled, jh.run_status," +
            " js.last_outcome_message, jh.run_date, jh.step_name, jh.run_time" +
            " from sysjobs j left join sysjobhistory jh on (j.job_id = jh.job_id)" +
            " left join sysjobservers js on (j.job_id = js.job_id)" +
            " where jh.run_date = (select Max(run_date) from sysjobhistory)" +
            " and jh.run_time = (select Max(run_time) from sysjobhistory)";

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

            {

                try
               {
               // open connection
               _con.Open();
               SqlConnection.ClearPool(_con);

               // 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 last_run_outcome from sysjobserver
                       string jobMessage = rdr.GetString(4);    // read Message from sysjobserver
                       int jobRunDate = rdr.GetInt32(5);        // read run_date from sysjobhistory
                       string jobStepName = rdr.GetString(6);   // read StepName from sysjobhistory
                       int jobRunTime = rdr.GetInt32(7);        // read run_time from sysjobhistory


                        String[] lviData = new String[] // ตัวแปรอะเรย์ชื่อ lviData 
                    { 
                        jobID.ToString(),
                        jobName.ToString(),
                        jobStepName.ToString(),
                        jobMessage.ToString(), 
                        jobStatus.ToString(),
                        jobRunDate.ToString(),
                        jobRunTime.ToString(),
                        //jobEnabled.ToString(), 

                    };

                        newData = lviData;

                        DisplayList();  // for display data on datagridview


                   }

                   rdr.Close();
               }
           }

thank you for everybody help very much. :-D

Answer 2

SQL stored procedures of queries don't give you any system data unless you have db_owner rights on the msdb system database, at lease in SQL Server 2008. Therefore mentioned methods normally don't work for applications where you want to show or manage jobs. However SMO namespace provides you with managed code solution for many SQL Server management features, including the SQL Server Agent functions that only require SQLServerAgent* permissions that you normally could get sorted for your application user. A good intro of using SMO classes to work with jobs is given here:

http://www.codeproject.com/Tips/367470/Manage-SQL-Server-Agent-Jobs-using-Csharp

I work on a similar task now and whilst SQL queries give me access denied, with C# code and Microsoft.SqlServer.Management.Smo.Agent namespace I just listed all jobs with this code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo.Agent;

namespace SmoTest
{
    class Program
    {
        static readonly string SqlServer = @"SQL01\SQL01";

        static void Main(string[] args)
        {
            ServerConnection conn = new ServerConnection(SqlServer);
            Server server = new Server(conn);
            JobCollection jobs = server.JobServer.Jobs;
            foreach (Job job in jobs)
            {
                Console.WriteLine(job.Name);
            }
        }
    }
}

Answer 3

This should be a good starting point to find out how to find your SQL Agent jobs using T-SQL:

View (and disable) SQL Agent Jobs with TSQL

The script will list out all your jobs on your database, and when they will be run next and so forth.

Using the job_name, you should also be able to find out details about your jobs using the SQL Server Agent Stored Procedures in the msdb database on your server.

Answer 4

On SQL Server 2005 and above, you can use the system stored procedure msdb.dbo.sp_help_job to get information, including status, about SQL Server Agent Jobs. You can read more about sp_help_job at http://msdn.microsoft.com/en-us/library/ms186722(v=SQL.90).aspx.

Here is the sample code to do this from C#.

private Dictionary<int, string> ExecutionStatusDictionary = new Dictionary<int, string>()
{
    {0, "Not idle or suspended"},
    {1, "Executing"},
    {2, "Waiting for thread"},
    {3, "Between retries"},
    {4, "Idle"},
    {5, "Suspended"},
    {7, "Performing completion actions"}
};

public string GetStatus()
{
    SqlConnection msdbConnection = new SqlConnection("Data Source=SERVERNAME;Initial Catalog=msdb;Integrated Security=SSPI");
    System.Text.StringBuilder resultBuilder = new System.Text.StringBuilder();

    try
    {
        msdbConnection.Open();

        SqlCommand jobStatusCommand = msdbConnection.CreateCommand();

        jobStatusCommand.CommandType = CommandType.StoredProcedure;
        jobStatusCommand.CommandText = "sp_help_job";

        SqlParameter jobName = jobStatusCommand.Parameters.Add("@job_name", SqlDbType.VarChar);
        jobName.Direction = ParameterDirection.Input;
        jobName.Value = "LoadRegions";

        SqlParameter jobAspect = jobStatusCommand.Parameters.Add("@job_aspect", SqlDbType.VarChar);
        jobAspect.Direction = ParameterDirection.Input;
        jobAspect.Value = "JOB";

        SqlDataReader jobStatusReader = jobStatusCommand.ExecuteReader();

        while (jobStatusReader.Read())
        {
            resultBuilder.Append(string.Format("{0} {1}",
                jobStatusReader["name"].ToString(),
                ExecutionStatusDictionary[(int)jobStatusReader["current_execution_status"]]
            ));
        }
        jobStatusReader.Close();
    }
    finally
    {
        msdbConnection.Close();
    }

    return resultBuilder.ToString();
}