Running an exe or program as SQL Server Job

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

Advertisements

22 Responses to Running an exe or program as SQL Server Job

  1. Sam says:

    One advantage in using Sql jobs over the windows scheduled tasks is, we don’t have to bother about password changes those jobs are running on as integrated authentication can be used.

  2. tiru says:

    Hi,

    I have created the sql job for .net exe file as you mentioned in the blog..
    But job is running endlessly without any clue..

    Do u have any idea about this..

  3. sushantp says:

    Hi Tiru,

    This job would run endlessly because this is what it is supposed to do a continuous running job or say a sort of scheduler. But if you want to control it you need to set the job schedule options so that it runs as per your reqs. Secondly you should use some Console functions to give you an idea of what is currently running as a good practice (basically to have a watch of things going on internally with your .net exe) you can as well store\log activities\action in DB or using a logger program.

    Hope this helps!

    -Thanks,
    Sushant

  4. Pranitha says:

    Hi,

    I am Tiru’s team. Our requirement is to tun the .exe that was developed in .net from sql agent job.

    To take an example and try, we have put the notepad.exe to be executed from job. Followed the same process that u explained in above bolg. Executed the job manually(1 time run,will schedule the job later). With this, the job is taking end less time to execute and the notepad doesn’t get opened.

    Are we missing any other settings? otherwise, at least it should work with notepad.exe

  5. Manish says:

    Hi,

    I am agree with Tiru and Tiru’s team member and we tried with same notepad.exe as well as excel.exe

    but result is.. job is running but no end…

    So , I am also facing same problem, please any one have idea to over come this problem

    Regards
    Manish

  6. Hi there,

    I am trying to figure out to make this work on my machine , but no luck. I think it is simple, but always get such kind of error message “system could not find specified file path”…any idea on this?

  7. sushantp says:

    Hi,
    Can you please let me know more about it. Send me a snapshot of the job step at sushant.pandey@gmail.com I will then be able to help you better.

    Thanks,
    Sushant

  8. Peter says:

    Thanks Sushant for this tip.

    I was able to use it. The “System Could Not Find specified path” is that we have to use a full qualified path of the location.

    For example: \\servername\folder\subfolder\job.exe

    Or E:\folder\subfolder\job.exe

  9. aashik says:

    This is very useful for me….. Thanks…

  10. Diane says:

    Great post. It showed me everything I needed to do.

  11. Harish says:

    Sushant,

    Were you able to finally run the exe from SQL Job ? Even I have created a test application in .NET & try to run it from SQL job. I dont see any form comming up. It just runs endlessely.

    Am I missing something ?

  12. sepideh says:

    thancks

  13. anand says:

    very nice post. thank you

  14. my site says:

    my site…

    […]Running an exe or program as SQL Server Job « Sushantp’s Weblog[…]…

  15. 100 Oz Hydration Packs…

    […]Running an exe or program as SQL Server Job « Sushantp’s Weblog[…]…

  16. dobinf says:

    saved me bunch of time…thanks for the post.

  17. automatic call distribution…

    […]Running an exe or program as SQL Server Job « Sushantp’s Weblog[…]…

  18. Mat says:

    Thanks for the post, very useful. If I need to run the job for 2 days, would I need to be logged in to the system all the time? Start the job and if I log off would the job still run

  19. click says:

    When I start your Rss feed it appears to be a ton of garbage, is the problem on my side?

  20. Pramendra Si ngh says:

    Use Console application to register jobs. It will finish the job and exit without waiting.

    Thanks
    Pramendra Singh

  21. sagar says:

    avoid any input commands in .net
    use Operating System Cmd.exe type step on job creation in SQL
    Ensure SQL server Agent is authorized to execute the file, if possible change the logon properties
    It worked for me!

  22. Al says:

    Before I try this, will it work on a C# console app?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Who am I what am I doing?

Who am I what am I doing?

%d bloggers like this: