如何执行 SQL 表中列出的 SQL Server 代理作业 [英] How to execute SQL Server Agent Jobs which are listed in SQL Table

问题描述

我试图将所有 SQL Server 代理作业存储在表名中，并希望根据它们的加载频率执行它们.

I am trying to store all the SQL Server Agent Jobs in Table name and want to execute them based on their loading frequency.

CREATE TABLE Maintainance
(
SQLJobName varchar(100), --SQL Job Name which needs to be executed
Frequency varchar(50), -- It can be Daily, Monthly, Weekly, Yearly
ManualRunDate date,  --If Frequency is not given need to execute on this date
LastRunDate datetime, -- If job ran successful it will put the date and time
IsSucceed bit, --1 for Success 0 for fail
Email nvarchar(50) -- email address
)

我想使用 TSQL 执行这些作业.此外，表中可能有多个需要运行的作业.如果第一个工作失败，它应该给表中列出的人发送电子邮件，它将执行下一个工作.如何在 sql 中使用游标或仅使用 While 循环来做到这一点?

I want to execute these jobs using TSQL. Also there could be more than one job into the table which needs to run. If 1st job failed it should email the person which listed in the table and it will execute next job. How can I do that using cursor or just While loop in sql?

declare cur cursor for
select Frequency from Maintainance

declare @x int
open cur
fetch next from cur into @x

while @@FETCH_STATUS = 0
BEGIN
    EXEC msdb.dbo.sp_start_job @job_name = @job_name

    fetch next from cur into @x
END

如果您有任何其他建议可以更好地完成此操作，请随时给我一个想法.

If you have any other suggestion that this can be done better way please do not hesitate to give me an idea.

推荐答案

a) 创建一个 proc，它通过具有适当 frequency 值的 proc 列表运行，执行它们并更新 lastrun列.

a) Create a proc that runs through list of procs with appropriate frequency value, executes them and updates lastrun column.

1. 在其中设置一个参数 @frequency，然后将其传递给外部.
2. 用这个参数过滤你的 proc 列表
3. 使用此频率"循环遍历 procs 列表并运行 procs

1. Make an argument @frequency in it, pass it form outside.
2. Filter your proc list with this argument
3. Loop through list of procs with this "frequency" and run procs

例如

alter proc dbo.RunProcs
  @Frequency varchar(50)
as
begin
  declare @crProcs cursor 

  set @crProcs = cursor fast_forward for
  select m.ProcName
  from dbo.Maintainance m
  where m.Frequency = @Frequency
  order by 1

  ...
  while @@fetch_status = 0
  beign
      begin try
        exec @ProcName
        ...
        update -> succeded
        ...
      end try
      begin catch
        ...
        update -> failed
        ...
      end catch
    fetch ...
  end

  return 1
end

b) 创建计划作业来运行此过程

b) Create scheduled jobs to run this proc

1. 创建一个作业DAILY PROCS"，为此作业分配一个时间表，使其每天运行.

   使用 T-SQL 添加作业步骤:exec dbo.RunProcs @Frequency = 'day'

1. Create a job "DAILY PROCS", assign a schedule to this job to make it run every day.

   Add a job-step with T-SQL: exec dbo.RunProcs @Frequency = 'day'

创建一个作业WEEKLY PROCS"，为该作业分配一个时间表，使其每周运行一次

使用 T-SQL 添加作业步骤:exec dbo.RunProcs @Frequency = 'week'

Create a job "WEEKLY PROCS", assign a schedule to this job to make it run every week

Add a job-step with T-SQL: exec dbo.RunProcs @Frequency = 'week'

创建一个作业MONTHLY PROCS"，为此作业分配一个时间表，使其每月运行

使用 T-SQL 添加作业步骤:exec dbo.RunProcs @Frequency = 'month'

Create a job "MONTHLY PROCS", assign a schedule to this job to make it run every month

Add a job-step with T-SQL: exec dbo.RunProcs @Frequency = 'month'

这篇关于如何执行 SQL 表中列出的 SQL Server 代理作业的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！