Click here to Skip to main content
Click here to Skip to main content

Custom Scheduler - SQL Server Reporting Services

, 11 Jul 2006
Rate this:
Please Sign up or sign in to vote.
This article provides a customized manner of creating a scheduler for the SQL Server Reports

Sample Application

Introduction

I was currently working for a company name Hermz Software which needed to create reports using SQL Server Reporting Services as it's reporting tool. As far as creation of reports is concerned, I didnt have much problems!! However, the delivery of the reports were to be done based on the following snapshots.

Output1

Output2

Output3

This snapshots can easily be obtained from the Outlook task creator. Well, not a big deal, as SSRS had it's own scheduling techniques.... But, here comes the crux!! We needed some custom implementation for the scheduler, that wasn't being provided by the SSRS scheduler.

So, then I decided to created my own Scheduler that worked on the same architecture of the SQL Server Scheduler.

Prerequisites

In order the run this custom Scheduler, we need the following prerequisites to be present on your system.

1. SQL Server Reporting Services [Reporting server]

2. VS.net [1.1 or 2.0 framework]

3. SMTP server [email client]

4. SQL Server Database [Backend]

Architecture

The Architecture of the system is as per depicted in the diagram:

 Architecture

As per the Architecture, the system[in short] consists of 5 main layers:

1. DAC layer : This is the Data Access Control Layer. It is responsible for accessing the SQL Server Database to retrieve scheduling details.

2. Email Extension layer: This layer is responsible for sending mail using an SMTP emailing server. 

3. Logging Layer: This layer is reponsible for logging error to the user[to be located at D:\ErrorLog.txt] and for Status reporting of the system [D:\hermzLog.txt].  

4. Reporting Extension: This layer is reponsible for obtaining the reports from the report server. The Report name, which is to be fed into the database will be retrieved via this extension.

5. Process Scheduler: It is the heart of the system. Responsible for the main process/thread scheduling. The manner in which the process is scheduled is depicted in the following diagram. 

 Process Scheduling

 

Instructions

From this article, you will obtain 1. SQL script file and 2. Executable file

Steps:

1. Run the SQL Script using either isqlw or the Enterprise manager[SQL Server 2000]/SQL Server Management Studio[SQL Server 2005].

2. Run the executable at the VS.net command prompt: installutil HermzScheduler.exe

 InstallUtil

3. Check for presence of Hermz Scheduler in the Services tray. Windows->Run->Services.msc

 Services

4. Create a new login for SQL server authentication.

 Database

  Add New user

5. Add in Sample data into HermzDB database at Schedule table.

The Occurrence type is 1->Daily, 2-> Weekly, 3-> Monthly.

Report Name -> /SampleProject/SampleReport

From_name, To_name, will be the names to be present in the email

Subject is the subject of the mail.

Pattern - > Null for Daily, DateTime value for Monthly/Weekly

6. Run the HermzScheduler service.

7. If you have Outlook Express installed , then the mail will get automatically populated into you mailbox. Or else, you can view the contents present at location:C:\Inetpub\mailroot\Queue

Conclusion

This tool was created entirely for Hermz Software. Thanks to this, we can create a custom SQL Server Scheduler. I hope you liked this article. Please feel free to mail me incase of any doubts.

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Soshan is a software engineer, currently working in variant projects. She does her programming in stuff like Assembler, C++, MFC and lots of web- and database stuff and now uses ASP.NET and C# extensively, too.
 
In her free time, she works in animation stuffs with extensive usage of Adobe Photoshop and CorelDraw. Driving racer cars is her current craze.
 
Whenever she gets bored, her interests goes on to networking and hacking-ethical. Has designed an ip-filter for firewall with a group of friends.

Comments and Discussions

 
Questiongreat PinmemberMember 1033471024-Jan-14 21:15 
QuestionSQL 2008 Pinmemberrainland13-Jun-13 2:39 
Answergreat article. it's greater if you can send me the source code. Pinmemberleo zheng18-Feb-13 19:34 
QuestionNice article Pinmemberaswansco5-Jan-13 22:39 
QuestionIs is possible for you to send source code PinmemberAnkit Soanki7-Oct-11 10:01 
GeneralPlease send source code PinmemberRam HCL3-Jan-11 20:14 
GeneralMy vote of 1 Pinmemberbindasrakesh17-Nov-10 20:26 
GeneralRe: My vote of 1 Pinmemberitmanager881525-Nov-10 9:33 
GeneralMy vote of 1 Pinmemberbindasrakesh17-Nov-10 20:25 
GeneralSource code please Pinmemberlawry5522-Apr-10 1:19 
Questionhow to get the source code [modified] Pinmemberadamtuliper16-Nov-09 18:01 
GeneralNice One Pinmembersashidhar2-Nov-09 5:56 
GeneralSource Code please Pinmemberhighglow15-Oct-09 7:14 
GeneralRe: Source Code please Pinmembersashidhar2-Nov-09 5:55 
GeneralDailyRecurrence of Reporting Services 2005 PinmemberSangmeshwar patil26-Aug-09 21:40 
GeneralRe: DailyRecurrence of Reporting Services 2005 Pinmembersashidhar2-Nov-09 5:58 
GeneralDaily Scheduling of SSRS2005 PinmemberSangmeshwar patil26-Aug-09 21:33 
GeneralSouce Code Pls PinmemberSachos22-Jul-09 2:54 
GeneralSouce Pinmemberwint1009-Jun-09 6:01 
GeneralGreat article Pinmemberarkreddy1628-Apr-09 23:40 
GeneralCustom Scheduler - SQL Server Reporting Services Pinmembershrias1199-Mar-09 23:36 
GeneralGreat Article !! Please send me the source code ASAP PinmemberMember 455767116-Feb-09 1:10 
GeneralCustom Scheduler - SQL Server Reporting Services PinmemberLantushenko2-Feb-09 0:37 
GeneralRe: Custom Scheduler - SQL Server Reporting Services Pinmembersashidhar2-Nov-09 5:58 
GeneralMy vote of 1 Pinmembercrystvn12-Jan-09 14:36 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 11 Jul 2006
Article Copyright 2006 by Soshan Fernandes
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid