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

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 2

You can get a list of all server jobs using this SELECT:

SELECT [name] FROM msdb.dbo.sysjobs

If you'd like to get a list of currently running jobs and their information, I would recommend writing a stored procedure in SQL which your application calls. There's a good demonstration here you could use...

http://feodorgeorgiev.com/blog/2010/03/how-to-query-currently-running-sql-server-agent-jobs/

Good luck!

Answer 3

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();
}

Answer 4

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 5

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 6

For my use case, I specifically needed to know when the job was finished running, and whether or not it succeeded. Here is my code to do that:

using System;
using System.Data;
using System.Data.SqlClient;

namespace LaunchJobAndWaitTillDone
{
    class Program
    {
        const string connectionString = "Data Source=YOURSERVERNAMEHERE;Initial Catalog=msdb;Integrated Security=SSPI";
        const string jobName = "YOURJOBNAMEHERE";
        static readonly TimeSpan waitFor = TimeSpan.FromSeconds(1.0);

        enum JobExecutionResult
        {
            Succeeded,
            FailedToStart,
            FailedAfterStart,
            Unknown
        }

        static void Main(string[] args)
        {
            var instance = new Program();
            JobExecutionResult jobResult = instance.RunJob(jobName);

            switch (jobResult)
            {
                case JobExecutionResult.Succeeded:
                    Console.WriteLine($"SQL Server Agent job, '{jobName}', ran successfully to completion.");
                    break;
                case JobExecutionResult.FailedToStart:
                    Console.WriteLine($"SQL Server Agent job, '{jobName}', failed to start.");
                    break;
                case JobExecutionResult.FailedAfterStart:
                    Console.WriteLine($"SQL Server Agent job, '{jobName}', started successfully, but encountered an error.");
                    break;
                default:
                    Console.WriteLine($"Unknown result from attempting to run SQL Server Agent job, '{jobName}'.");
                    break;
            }

            Console.ReadLine();
            return;
        }

        JobExecutionResult RunJob(string jobName)
        {
            int jobResult;

            using (var jobConnection = new SqlConnection(connectionString))
            {
                SqlCommand jobCommand;
                SqlParameter jobReturnValue;
                SqlParameter jobParameter;

                jobCommand = new SqlCommand("sp_start_job", jobConnection);
                jobCommand.CommandType = CommandType.StoredProcedure;

                jobReturnValue = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                jobReturnValue.Direction = ParameterDirection.ReturnValue;
                jobCommand.Parameters.Add(jobReturnValue);

                jobParameter = new SqlParameter("@job_name", SqlDbType.VarChar);
                jobParameter.Direction = ParameterDirection.Input;
                jobCommand.Parameters.Add(jobParameter);
                jobParameter.Value = jobName;

                jobConnection.Open();
                try
                {
                    jobCommand.ExecuteNonQuery();
                    jobResult = (Int32)jobCommand.Parameters["@RETURN_VALUE"].Value;
                }
                catch (SqlException)
                {
                    jobResult = -1;
                }
            }

            switch (jobResult)
            {
                case 0:
                    break;
                default:
                    return JobExecutionResult.FailedToStart;
            }

            while (true)
            {
                using (var jobConnection2 = new SqlConnection(connectionString))
                {
                    SqlCommand jobCommand2 = new SqlCommand("sp_help_jobactivity", jobConnection2);
                    jobCommand2.CommandType = CommandType.StoredProcedure;

                    SqlParameter jobReturnValue2 = new SqlParameter("@RETURN_VALUE", SqlDbType.Int);
                    jobReturnValue2.Direction = ParameterDirection.ReturnValue;
                    jobCommand2.Parameters.Add(jobReturnValue2);

                    SqlParameter jobParameter2 = new SqlParameter("@job_name", SqlDbType.VarChar);
                    jobParameter2.Direction = ParameterDirection.Input;
                    jobCommand2.Parameters.Add(jobParameter2);
                    jobParameter2.Value = jobName;

                    jobConnection2.Open();
                    SqlDataReader rdr = jobCommand2.ExecuteReader();
                    while (rdr.Read())
                    {
                        object msg = rdr["message"];
                        object run_status = rdr["run_status"];
                        if (!DBNull.Value.Equals(msg))
                        {
                            var message = msg as string;
                            var runStatus = run_status as Int32?;
                            if (message != null && message.StartsWith("The job succeeded")
                                && runStatus.HasValue && runStatus.Value == 1)
                            {
                                return JobExecutionResult.Succeeded;
                            }
                            else if (message != null && message.StartsWith("The job failed"))
                            {
                                return JobExecutionResult.FailedAfterStart;
                            }
                            else if (runStatus.HasValue && runStatus.Value == 1)
                            {
                                return JobExecutionResult.Unknown;
                            }
                        }
                    }
                }

                System.Threading.Thread.Sleep(waitFor);
            }
        }
    }
}

Note that you may need database/server owner permissions or something like that for this code to work.