Remotely start a SQL job??

  • Is there a way that a SQL job can be started from a batch file or script remotely?

    We have 7 DTS packages set up on our SQL server that imports reports into some databases on the server.  The reports are generated by an Accounting application.  The accounting department runs the report macro and the macro outputs the reports into a directory for the all the DTS packages.  I currently set up all the DTS packages into one SQL job with 7 steps.  This will run at 8pm.  The accountants do the reports at the end of the day.  The problem is sometimes they need the information updated immediately.  The generate the reports and then have to call IT to run the job.  It would be less administration and a lot simplier if I can have them run a batch file.

    Any thoughts on this issue are greatly appreciated.

    Thanks

  • Rob

    Use the dtsrun utility.  You'll find the syntax in Books Online.  You may decide to bundle all the DTS packages into one so that your accountants only have to press one button instead of seven.

    John

  • May be you can script  following stored procedure in batch file to run sql agent job.

     

    USE msdb

    EXEC sp_start_job @job_name = 'your sql agent job name'

    Thanks,

    SR

    Thanks,
    SR

  • John, that sounds good, but then wouldn't I need to install the some part of SQL on each users' machine to install the DTSrun command?

    sree...That is sounding good.  Do you or anyone else have a link to anywhere that may show me enough of a script to get me started with that?

    I'm guessing a VBscript that makes a SQL connection then executes the Query??

     

    Thanks again

  • You can use VBscript to use ADO to execute sp_start_job as SR suggested to use. You can also use WMI scripts to manage the remote server if youn are an admin on that server.

    If you need to run the job On Demand, you may create Windows Scheduler Task on the server pointing to the OSQL batch executing your SP. Then from the client computer do the following:

    Strat->Run, enter \\servername\Admin$\Tasks

    Then you will be connected to the Tasks folder of your remote server and right-click on the task and run it.

    For some reason, you will not see remote Tasks folder but you will see the content of your client Tasks folder if you just map a drive to \\servername\Admin$ and navigate to Tasks. This is some sort of By Design or a Bug. You have to use Run to connect to remote Tasks folder.

    Regards,Yelena Varsha

  • Hi Rob,

    I am not much familiar with VB script.So I am not sure about using this in VB.But you can use OSQL command to run the job from batch as folllows.

     

    set osqlExeLocation="c:\Program Files\Microsoft SQL     Server\80Tools\Binn\osql.exe"

    call %osqlExeLocation% -E -d%dbname% -S%svr%  -n -b -Q"exec msdb.dbo.sp_start_job %SQLAgentJob%"

     

    Thanks,

    SR

     

    Thanks,
    SR

  • Or why not have 1 Job that checks the location where the files are produced. If there is anything in there then run the DTS from the Server. Simply Schedule the job to run every minute.

  • If you do the last one, make sure that the files are removed once processed, or the job will keep rerunning.

    I imagine once every 15mins or so would be often enough, too - perhaps only during work hours. A job which just checks a file location every minute won't cause any problems on its own, but a general policy of allowing unnecessary resource usage will.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply