Comment surveiller les informations d'emploi SQL Server Agent dans C #

Je peux faire cela déjà ...

I Sélectionnez la table de formulaire "sysjobserver" dans la base de données "msdb" pour statut de lecture, date, heure du travail que je veux. p>

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

sur SQL Server 2005 et supérieur STROND>, vous pouvez utiliser la procédure stockée du système msdb.dbo.sp_help_job pour obtenir des informations, y compris le statut, sur les emplois SQL Server Agent. Vous pouvez en savoir plus sur sp_help_job à http: // msdn.microsoft.com/en-us/library/ms186722(V=SQL.90).aspx .

Voici le code d'échantillon pour le faire de C #. P>

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

Les procédures stockées SQL des requêtes ne vous donnent aucune donnée de système, sauf si vous avez des droits db_owner sur la base de données système MSDB, au bail dans SQL Server 2008. Par conséquent, des méthodes mentionnées normalement ne fonctionnent normalement pas pour les applications où vous souhaitez montrer ou Gérer les emplois. Cependant, l'espace de noms SMO vous fournit une solution de code gérée pour de nombreuses fonctionnalités de gestion SQL Server, y compris les fonctions SQL Server Server Fonctions qui nécessitent uniquement des autorisations SQLServeragent * que vous pourriez normalement être triés pour votre utilisateur d'application. Une bonne intro de l'utilisation de classes SMO pour travailler avec des emplois est donnée ici:

http://www.codeproject.com/tips/367470/manage-sql-server-agent-jobs-utilisation-cshaparpe p>

Je travaille sur un Une tâche similaire maintenant et tandis que les requêtes SQL me donnent accès refusé, avec C # Code et Microsoft.sqlserver.management.smo.AgentesPace de noms Je viens de répertorier tous les travaux avec ce code: P>

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

Pour mon cas d'utilisation, j'ai spécifiquement besoin de savoir quand le travail a été fini et que cela a réussi ou non. Voici mon code pour faire cela:

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