如何监控在C＃中的SQL Server代理招聘信息 [英] How to monitor SQL Server Agent Job info in C#

问题描述

我需要在工作发生一样显示在Windows应用程序事件日志创建用于监控的SQL Server 2000代理作业状态和信息的应用程序。现在我连接到通过一个连接字符串的数据库了，但我不知道如何从工作状态和信息。

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.

开发人员工具：

1. MS SQL Sever的2000 SP4


2. MS Visual Studio 2008中（C＃）

我是菜鸟程序员。

推荐答案

我可以做到这一点已经...

i can do this already...

我选择形式表Sysjobserver数据库MSDB阅读状态，日期，我想工作的时间。

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

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

感谢您对大家帮助很大。 :-D

thank you for everybody help very much. :-D

这篇关于如何监控在C＃中的SQL Server代理招聘信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！