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.
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:
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.
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
3. Check for presence of Hermz Scheduler in the Services tray. Windows->Run->Services.msc
4. Create a new login for SQL server authentication.
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.
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.