Eric, I'm trying to create a script that would script all jobs on particular server programmatically? (this way I can run it any time I want to). I've looked in BOL; they have something under SQL-DMO but nothing particular for this subject. I appreciate any help. Thanks.

-- Anonymous, May 14, 2000

Answers

Albert,

You can control sql job scripts with the following stored procedures:

sp_add_job sp_add_jobschedule sp_add_jobstep sp_delete_job sp_delete_jobschedule sp_delete_jobstep sp_help_job sp_help_jobschedule sp_help_jobstep sp_update_job sp_update_jobschedule sp_update_jobstep

The trick is that the stored procedures are in the msdb database, so you must call them with a prefix of 'msdb..'. For instance you can enable a job with a call like:

msdb..sp_update_job @job_name = 'My job name', @enabled = 1

Good Luck,

Eric

-- Anonymous, May 19, 2000