"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,
If I have any new Report Script to write I will load it into this table then update 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.