01 August, 2008

Running an exe or program as SQL Server Job

This can be achieved by SQL Server Agent which is a Windows service to run scheduled adminstrative tasks or commonly know as jobs. 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.

Job Scheduling process:
  • Connect to the SQL Server where you want to schedule your job.
  • 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.
  • Right Click SQL Server Agent. Select New -> Job. This will open a wizard to schedule a new job.
  • 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.
  • 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.
  • 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.
  • 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.



No comments: