Create And Schedule A SQLAgent Job From TSQL

Question

Post reply

Create And Schedule A SQLAgent Job From TSQL

crosspatch

Hall of Fame

Points: 3108
More actions
January 17, 2003 at 11:32 am

#79621

I'd like to build a TSQL stored procedure that sets up a SQL Agent job, creating the steps, scheduling etc.
Has anyone done this and knows of any 'gotchas' I need to prepare for. Even better could anyone help with a ready-rolled sp. (OK I know I'm lazy, anything to save time).
We use SQLServer version 7 in production (we're still UATing our apps on 2000)
Much gratitude in advance
I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961
sp_add_job
sp_add_jobstep
sp_add_jobschedule

But got an error saying the procs could not be found.

Anybody got any clues? I am using SqlServer 2000

Bastiaan Oelrich

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 1

Look at the system stored procedures:

sp_add_job
sp_add_jobstep
sp_add_jobschedule

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

crosspatch Hall of Fame Points: 3108 More actions · Answer 2

Thanks Brian

I'd sussed those procs, I was really after any known Gotcha's.

Well let's be brutally honest I was hoping that someone had a ready-rolled proc I could clone, why re-invent the wheel? (Or rather why think when someone else can do it?)

We've built the proc now, basically it cycles round a table which contains one record for each line of the script and invoke the add step etc.

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

Rodrigo Acosta SSC-Insane Points: 21261 More actions · Answer 3

I would create the jobs with EM, script it and then create a master sp with all the job SPs included. You should change all the variables that you want to specify to your sp.

bastiaan oelrich Grasshopper Points: 14 More actions · Answer 4

bastiaan oelrich

Grasshopper

Points: 14

September 9, 2004 at 4:02 am

#522256

Hi ,

I tried to use

bastiaan oelrich Grasshopper Points: 14 More actions · Answer 5

I found the solution to the problem above myself. I thought the system stored procedures would be located in master db, but these are located in msdb db

Bastiaan