如何从sql服务器获取作业名称和状态到textbox，遵循时间计划循环

Question

现在，我可以通过连接字符串连接MS SQL Server2000，并获得作业名称和作业

SQL Server代理中的状态。但是我想不断地显示姓名和状态跟随时间

计划定期执行监视作业。请建议如何做。(使用MS Visual

Studio 2008 (C#))

public  void ConnectJOB() 
    {
        SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
        try
        {
            SQLServer.Connect("10.17.13.70", "sa", "");
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }

        SQLDMO.JobServer jobServer = SQLServer.JobServer;
        foreach (SQLDMO.Job job in jobServer.Jobs)
        {
            string jobName = job.Name;
            SQLDMO.SQLDMO_JOBEXECUTION_STATUS status = job.CurrentRunStatus;


            richTextBox1.Text = jobName.ToString();
            richTextBox2.Text = status.ToString();
        }

Answer 1

您可能希望签出SQL Server 2000中的SQL Server代理作业系统表，并从中获取信息。

有关可用表的概述，请查看此wiki post here。主表是sysjobs和sysjobhistory，您可以查看它们并找到所需的信息。需要注意的一件事是:这些表位于msdb数据库中，您需要相当高的权限才能读取这些表。

此查询将返回系统中当前的所有作业、它们的job_ID (GUID)、名称和enabled状态：

select job_id, name, enabled 
from msdb..sysjobs

使用该信息，并与sysjobhistory或其他相关表连接，以获得您正在查找的信息。

更新：

好的，这是完整的代码序列

// define the query to run
string sqlJobQuery = "select j.job_id, j.name, j.enabled, jh.run_status " +
                     "from sysjobs j inner join sysjobhistory jh on j.job_id = jh.jobid"

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

    // 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
            int jobEnabled = rdr.GetInt(2);     // read Job enabled flag
            int jobRunStatus = rdr.GetInt(3);   // read job run status

            // do something with your data, e.g. store it into a list or something
        }

        rdr.Close();
    }

    // close connection again
    _con.Close();     
}

Answer 2

您可以使用直接的T-SQL语句从msdb数据库的sysjobs和sysjobhistory表中获取有关作业及其历史记录(状态)的信息-如下所示：

public class JobInfo
{
    public string Name { get; set; }
    public Guid ID { get; set; }
    public byte Enabled { get; set; }
    public int Status { get; set; }
}

public List<JobInfo> GetJobsAndStatus()
{
   List<JobInfo> _jobs = new List<JobInfo>();

   string sqlJobQuery = "select j.job_id, j.name, j.enabled, jh.run_state " +
                        "from sysjobs j inner join sysjobhistory jh on j.job_id = jh.job_id";

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

       // 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 run_state from jobhistory

               _jobs.Add(new JobInfo { Name = jobName, 
                                       ID = jobID, 
                                       Enabled = jobEnabled,
                                       Status = jobStatus });
    }

    rdr.Close();
}

// close connection again
_con.Close();     

return _jobs;
}

一旦你有了作业列表和它们的历史/状态，你可以随心所欲地处理它，例如，抓取第一个并将值放入文本框中，或者任何你想要的东西。