How to monitor SQL Server Agent Job info in C#

by

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. 😀

More Related Contents:

  1. Query extremely slow in code but fast in SSMS
  2. Automatically create an Enum based on values in a database lookup table?
  3. How to add a Blend Behavior in a Style Setter
  4. Using ‘async’ in a console application in C# [duplicate]
  5. Interprocess communication for Windows in C# (.NET 2.0)
  6. Application.OpenForms.Count = 0 always
  7. Parse Math Expression [duplicate]
  8. Pair-wise iteration in C# or sliding window enumerator
  9. Use reflection to invoke an overridden base method
  10. Avoiding null reference exceptions
  11. How to cast Object to its actual type?
  12. How do I put text on ProgressBar?
  13. Linq OrderBy does not sort a List. How do I sort the list?
  14. JSON serialize properties on class inheriting list [duplicate]
  15. How to add property-level Attribute to the TypeDescriptor at runtime?
  16. htmlagilitypack and dynamic content issue
  17. Passing a SQL parameter to an IN() clause using typed datasets in .NET
  18. How can I make a single instance form (not application)?
  19. Using the C# Dispatcher in WPF Applications
  20. Non-Blocking read from standard I/O in C# [closed]
  21. Why can reflection access protected/private member of class in C#?
  22. Read from xml files with or without a namespace using XmlDocument
  23. Object Sharing between Applications?
  24. C# System.RegEx matches LF when it should not
  25. Developing C# on Linux [closed]
  26. Tool to refactor C# var to explicit type
  27. Get a list of all tree nodes (in all levels) in TreeView Controls
  28. Binding datagrid column width
  29. Timeout pattern on task-based asynchronous method in C#
  30. Make overlapping picturebox transparent in C#.net

Leave a Comment