Remotely start a SQL job??

Question

Remotely start a SQL job??

Rob M

SSC Enthusiast

Points: 102
More actions
June 19, 2006 at 9:00 am

#94267

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

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

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

sree-226516 SSCrazy Points: 2590 More actions · Answer 2

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

Rob M SSC Enthusiast Points: 102 More actions · Answer 3

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

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

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

sree-226516 SSCrazy Points: 2590 More actions · Answer 5

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

Paul Smith-221741 SSCrazy Points: 2003 More actions · Answer 6

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.

stax68 SSChampion Points: 11711 More actions · Answer 7

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