March 20, 2002 at 5:50 am
Is it possible to start/stop/disable/enable jobs in SQL Server from the cmd? and if it is, how would I go about doing this? I only want to be able to control specific jobs. I figure stopping/starting SQL Server Agent will stop/start them, but I would rather have control over indivisual jobs.
If it can't be done through cmd, can it be done in a scripting language (Perl, Vbscript...)?
Thanks for your help.
Michael.
March 20, 2002 at 6:17 am
To do thru cmd line you can use isql. SOrry I don't generally use myself but for a list of the paramteres type isql /?. Otherwise,
I know you can do thru any languag that supports making database connections. In VBScript you can make an ADO connection to the database and execute on the connection "sp_start_job 'jobnamehere'" or "sp_stop_job 'jobnamehere'" or "sp_update_job 2jobname = 'jobnamehere', @enabled = 1(enabled)or0(disabled)" (note enabled just mean the job can be put on a schedule and has not affect on stop or start job sps) or even "sp_delete_job 'jobnamehere'". Se SQL BOL for details.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 20, 2002 at 7:30 am
You can do this either through isql or osql. You'll need to use a user that either a) owns the jobs or b) is a member of the sysadmin role, just as in Enterprise Manager.
From there, the system stored procedures you'll want to take a look at are:
sp_start_job
sp_stop_job
sp_update_jobschedule
They are the T-SQL commands behind the scenes of Enterprise Manager and thus can be scripted and run through isql or osql. They can also be executed through ADO.
K. Brian Kelley
http://qa.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
March 20, 2002 at 7:50 am
DMO is another alternative, though sp_start_job, etc, are just as good if that's all you're doing.
Andy
March 21, 2002 at 5:45 am
Thanks everyone, all is now well.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply