This post will look into the scenario where we have some program\exe as part of some web project or any other project related to DB (even if it is not related to DB then also you can schedule it) and we want it to run recurring (frequency may vary). This can be achieved by SQL Server Agent which is a Windows service to run scheduled adminstrative tasks or commonly know as jobs (These are SQL jobs, you can schedule windows jobs too). SQL Server agent uses SQL Server t ostore job information. A job consists of steps which can be set using steps wizard during job scheduling. So how do we schedule a job for an exe to run every night 12 AM.
Job Scheduling process:
1) Connect to the SQL Server where you want to schedule your job.
2) Check if SQL Server Agent (you can find it at the bottom most entity) is running, if not run it and set it to run automatic.
3) Right Click SQL Server Agent. Select New -> Job. This will open a wizard to schedule a new job.
4) Select Steps from options given on the left side of wizard. This is the main place to set your exe\program as a running step. Give some name to this step, Select Operating System (cmd) for the Type and give the full path of exe in Text box for Command. Below is a sample step set up for running test.exe.
5) Now Select the job schedules option from left to set the frequency. Give some name to schedule, selection other options as shown in the figure below. This will set the job to run daily at 12 AM.
6) If you want you can set optional Alerts and notification (using SQL notification services) to send out email in case any things fails, succeeds or at any specific event.
7) The targets option set the DB to look at or put data in. It should commonly be the localhost but you can set a remote SQL Server too here in case you have some DB interaction but then make sure that the account which you are using should have access to the remote server.
Your job is now scheduled to run everyday test.exe. just to make sure that everything run properly go to Agent -> Jobs -> NEW_JOB (the one you just created), right click NEW_JOB and select “start job at step”. This will run the job instantly for you and show the status if the job run successfully or failed. So now you finally done.
Do you know where the job information is stored. Its stored in the same SQL Server’s system databases. You can find it under system databases -> msdb -> tables -> system tables -> sysjobs and related tables. All the information about your job is stored in this and other job related tables.
You can also quickly look into job activity monitor to see the current status of all the jobs. This helps in findind any issues with the jobs and correcting, if needed.
Hope this will help you in setting a SQL job for running an exe or program continuously.
bye for now.
my team @ http://india.rampgroup.com