sp_add_jobstep

Question

sp_add_jobstep

big boy

Old Hand

Points: 340
More actions
September 2, 2004 at 2:15 pm

#162195

I am trying to set up an automated way to set up backup jobs using sp_add_job with sp_add_jobstep. I would like to make the @command parameter of sp_add_jobstep to be a few lines of t-sql code but I can't seem to get it to work. I have tried concatenating it with carriage return and line feed character codes but it doesn't like that.
Does anyone have any ideas?

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

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

hoo-t SSChampion Points: 12713 More actions · Answer 1

Script an existing job (right click on the job, select "all tasks", select "generate scripts", "preview", and "copy", then paste into query analyzer) to get the syntax, and just make your changes to that!

Steve

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 2

DECLARE @commandtext varchar(2000)

SET @commandtext = 'this

is

my

multiline

command'

exec sp_add_jobstep @command = @commandtext, ...

Julian Kuiters
juliankuiters.id.au

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 3

Have you considered making the "few lines of T-SQL" into stored procedure and then executing that?

DECLARE @commandtext varchar(2000)

SET @commandtext = 'myStpreProc'

exec sp_add_jobstep @command = @commandtext

big boy Old Hand Points: 340 More actions · Answer 4

Julian's suggestion is what I was looking for. DAB, I am monitoring over 50 servers and over 200 databases in 10 different domains and did not want to create a stored procedure on each server for this.

Thanks.

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 5

Big boy,

I do my stuff like DAB's way too because I manage more server then you and more databases. I also have a job that connect to all server to check the task status and report only failed job.

mom

Scalability Doug SSCertifiable Points: 5351 More actions · Answer 6

Thanks for the validation mom. It's amazing what you can do with @@servername, sysservers and a parameterized table in order to run sp's on remote servers.