Database table design for scheduling solution

  • 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.

    Please help.

  • This sounds as if you're trying to build your own version of SSRS.

    Why not use what is already there?


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Actually the exercise is to automate the Reports, so I'm just trying to come up with a smarter way to automate scripts. and since many scripts have things that are common with them I thought of configuration table, where I will load ReportName and so on, and have a Proc that will be calling my Configuration Table.

    Now I want to Schedule my Reports, others will be running monthly other weekly. But now I'm not sure how to go on about that because I want scheduler to pick up only relevant Reports to execute.

  • Option 1: (If the reports are SSRS reports)

    Schedule them as subscriptions in SSRS

    Option 2: (If the reports are not real reports but SQL stored procedures)

    Schedule them as jobs in SQL Agent


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • I think the issue is How I built the solution, let me explain that first before I get to the Scheduler;

    There were Scripts that were run manually, so I'm automating them, so since 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 throughout 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](100) NOT NULL,

    [TableName] [nvarchar](200) NOT NULL,

    [ReportFileName] [nvarchar](200) NOT NULL,

    [EmailSubject] [nvarchar](200) NOT NULL,

    [ListName] [nvarchar](200) NOT NULL,

    [BodyScript] [nvarchar](max) NOT NULL,

    [SendingEmailScript] [nvarchar](max) 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

    ----------------------------------------------------------------------------------------------------------------------------------

    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.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply