CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Justin
Justin

Reputation: 403

How to execute SQL Server Agent Jobs which are listed in SQL Table

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
)

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.

Upvotes: 1

Views: 1308

Answers (1)

IVNSTN
IVNSTN

Reputation: 9299

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

  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

e.g.

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) Create scheduled jobs to run this proc

  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'

  2. 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'

  3. 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'

Upvotes: 1

Related Questions