I have a Configuration table with number of reports to be run, Weekly or Monthly. The configuration table that I have stores ReportName, TableName, ReportFileNme, EmailSubject, Script and EmailScript.
I was thinking of creating another Table for Scheduler with reports names and dates of execution, and a Proc that will check the table to see if that's anything that needs to be executed, if so then execute.
I'm not sure if my analysis to this problem is correct or efficient.
We have something similar. We have a TASK table, and a Stored Procedure (name) column. Thus the scheduler just runs the SProc - it might be a report, sent by EMail, but it might also be a Purge of stale data.
SQL Agent's scheduler has a separate Schedule but we just include that in the Task table. We have "Frequency" column and only allow certain value - every-ten-minutes, hourly, daily, monthly; that might not be flexible enough for your requirements.
We have Last Run and Next Run (date / time). When a process starts the Next Run time is calculated and the table updated. You might need something that prevents the task running if there is already an instance that is still running.
We have columns for IsEnabled (allows easy setting to DISabled, temporarily) and Sequence (allows tasks for the same time to be run sequentially, in a prescribed order)
We have a separate TaskRunHistory table, which just stores the ID of the task and the Start / End run date/time. Something with a Start Date/Time and NO End Date/Time is either still running ... or crashed!
If you need something more sophisticated I suggest you look at the SQL Agent scheduler, which has a lot more options and capabilities.
"SQL Agent's scheduler has a separate Schedule but we just include that in the Task table. " I'm not sure what you mean by that.
I haven't worked on Scheduler before, or Scheduled anything, this will be my first time.
Actually the exercise is to automate the Reports, which are Store Proc reports not SSRS.
The issue is How I built the solution, let me explain that first before I get to Scheduler;
There were Scripts that were run manually, so I'm automating them, so nice many scripts have things that are common in those scripts(reports), I built a Configuration Table, then "re-wrote" the scripts, placing whatever that is common through out the reports with variable, values to these variables are stored in the Configuration table.
This is my table:
[ConfigurationID] [int] IDENTITY(1,1) NOT NULL,
[ReportName] varchar NOT NULL,
[TableName] nvarchar NOT NULL,
[ReportFileName] nvarchar NOT NULL,
[EmailSubject] nvarchar NOT NULL,
[ListName] nvarchar NOT NULL,
[BodyScript] nvarchar NOT NULL,
[SendingEmailScript] nvarchar NOT NULL
BodyScript column is the script that was run manually.
Then I have a Proc that will go into this table, pick up BodyScript and do the REPLACE, from ReportName to SendingEmail.
All of these works fine. and why this route? I'm not writing same code throughout the reports, I load any Script into BodyScript column, then the name of the report into ReportName,
Scheduler:
Now I want to Schedule this monthly or weekly run. But now I'm not sure how to go on about that because I want scheduler to pick up only relevant Reports to execute.
In SQL Agent you create a Job / Task, and then "attach" a schedule to it. Thus a Schedule can be reused for multiple jobs and is independent of the Job itself. A Schedule can be set up to run on specific days of the week, repeat at intervals, stop after a given date - or only run once - and so on. All sorts of very flexible features.
In our own Task Scheduler we didn't need lots of flexibility, so we just allow for some very specific schedules - Ten minutes, Hourly or Daily.
Why not use SQL Agent's scheduler? You could create one job for each SProc - that would save you having to create a scheduler of your own.
If you want to build your own scheduler I reckon you need to do the following:
Run your "Report Launch Sproc" periodically - you can do that using SQL Agent's scheduler, and launch it at the minimum granularity that you need (from your description that appears to be "weekly")
Find anything in your Reports table that is overdue, and execute it. Also, set its Next Run Time to the next anniversary date/time (this needs some care if a task is being run "late" as the intention is probably that it should NEXT be run on the anniversary of the original date, not that of the actual run-date. Issues such as adding one month to 31-January depending on whether it is a leap year or not, and so on. You need to handle the situation where a Report raises an error (you don't want your whole run to abort, the remainder of the reports still need to run 
) . You also need to consider if the reports should run concurrently, or sequentially. The SQL Agent scheduler allows things to run either sequentially (a single Job with multiple Steps) or Concurrently (each task as a separate Job and then all scheduled at the same start time)
Hi Kristen, yes we do actually have something similar, so I want to understand yours because I think it's exactly what I'm trying to build. Excuse me if I'm too slow on these I haven't worked with SQL Agent before.
so your TASK table, I think it's what I want to have as well. TASK Table has Frequency, Last Run and Next run colums? only 3?
On my side I want to look at say your "TASK" Table, see if I have to run Weekly,Monthly or Daily report. So I'm thinking of creating that "TASK" table, with ReportName and Frequency columns, Then Scheduler will pick up a ReportName and runs the Pro, I don't know if you can make sense out of this. Please reply
ID
, Description
, SProc
, IsEnabled
, Sequence
, Frequency -- e.g. Ten-minute, Hourly, Daily
, LastRun
, NextRun
, Notes