I'm newbie. Script to generate scripts (add job steps).

Hi, I want to add to my 50 single step jobs a second step with command
"execute JOB_STEP_FAIL <jobname>" where <jobname> is job name from sysjobs
table.
I want to use sp_add_jobstep .
I do not want to make this job by hand.
Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!If you have a way to identify the 50 Jobs you want to add a step to, then us
e
a cursor, or table variable, or while loop, to run sp_add_jobstep for each
one...
Say the job names all start with 'DD'
Then
Select Job_id
From msdb..sysjobs
Where name like 'DD%'
Will give you list of all jobs
Then you can use a while loop
Declare @.JobName varChar(200) Set @.JobName = ''
While Exists (Select * From msdb..sysJobs
Where name like 'DD%'
And name > @.JobName)
Begin
Select @.JobName = Min(name )
From msdb..sysJobs
Where name like 'DD%'
And name > @.JobName
-- --
Exec sp_Add_Job_Step
@.job_name = @.JobName,
@.step_id = 2,
@.Step_Name = 'FailStep',
@.subsystem = 'TSQL',
@.command = 'execute JOB_STEP_FAIL ' + @.JobName
End
But if <jobName> in your post is just the same job that just failed, it
looks like you are trying to reattempt the same job if it gets to this step,
But there is both a ReAttempt , and a Fail method on each jobStep that are
more appropriate for this purpose... You should investigate those
"andrea favero" wrote:

> Hi, I want to add to my 50 single step jobs a second step with command
> "execute JOB_STEP_FAIL <jobname>" where <jobname> is job name from sysjob
s
> table.
> I want to use sp_add_jobstep .
> I do not want to make this job by hand.
> Thanks !!!!!!!!!!!!!!!!!!!!!!!!!!|||Thanks a lot for your fast answer.
Thanks
"CBretana" wrote:
> If you have a way to identify the 50 Jobs you want to add a step to, then
use
> a cursor, or table variable, or while loop, to run sp_add_jobstep for each
> one...
> Say the job names all start with 'DD'
> Then
> Select Job_id
> From msdb..sysjobs
> Where name like 'DD%'
> Will give you list of all jobs
> Then you can use a while loop
> Declare @.JobName varChar(200) Set @.JobName = ''
> While Exists (Select * From msdb..sysJobs
> Where name like 'DD%'
> And name > @.JobName)
> Begin
> Select @.JobName = Min(name )
> From msdb..sysJobs
> Where name like 'DD%'
> And name > @.JobName
> -- --
> Exec sp_Add_Job_Step
> @.job_name = @.JobName,
> @.step_id = 2,
> @.Step_Name = 'FailStep',
> @.subsystem = 'TSQL',
> @.command = 'execute JOB_STEP_FAIL ' + @.JobName
> End
>
> But if <jobName> in your post is just the same job that just failed, it
> looks like you are trying to reattempt the same job if it gets to this ste
p,
> But there is both a ReAttempt , and a Fail method on each jobStep that are
> more appropriate for this purpose... You should investigate those
>
> "andrea favero" wrote:
>

Ignoring Errors

I have some "best effort" jobs that try at various times to walk over
missed records from one system to another about 5 times. After that,
the data becomes stale and is no longer usable, so I have another
process that comes after that to investigate the problem.
The problem I'm having is that when my best effort jobs ran in MS SQL
Server, they would run and then bomb out immediately. I want it instead
to just continue on error.
Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
weekend. The results were exactly the same -- the job dies on error.
Can you help me figure out a way either for osql.exe to continue on
error, or for me to ignore all stored procedure errors and just keep on
continuing?
I'm used to VB's "on error resume next" and unfortunately I don't see
one in MS SQL Server. This is absurd!
Check out the error handling sections of Erland's web site:
http://www.sommarskog.se/
Andrew J. Kelly SQL MVP
<googlemike@.hotpop.com> wrote in message
news:1113236973.134465.132330@.l41g2000cwc.googlegr oups.com...
>I have some "best effort" jobs that try at various times to walk over
> missed records from one system to another about 5 times. After that,
> the data becomes stale and is no longer usable, so I have another
> process that comes after that to investigate the problem.
> The problem I'm having is that when my best effort jobs ran in MS SQL
> Server, they would run and then bomb out immediately. I want it instead
> to just continue on error.
> Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
> weekend. The results were exactly the same -- the job dies on error.
> Can you help me figure out a way either for osql.exe to continue on
> error, or for me to ignore all stored procedure errors and just keep on
> continuing?
> I'm used to VB's "on error resume next" and unfortunately I don't see
> one in MS SQL Server. This is absurd!
>
|||OSQL is better than Agent TSQL as OSQL doesn't terminate on errors. However, for some errors, *SQL
Server* terminates the batch. I suggest you check out the articles on error handling at
www.sommarskog.se.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<googlemike@.hotpop.com> wrote in message
news:1113236973.134465.132330@.l41g2000cwc.googlegr oups.com...
>I have some "best effort" jobs that try at various times to walk over
> missed records from one system to another about 5 times. After that,
> the data becomes stale and is no longer usable, so I have another
> process that comes after that to investigate the problem.
> The problem I'm having is that when my best effort jobs ran in MS SQL
> Server, they would run and then bomb out immediately. I want it instead
> to just continue on error.
> Meanwhile, I then switched the jobs to osql.exe and Task Scheduler this
> weekend. The results were exactly the same -- the job dies on error.
> Can you help me figure out a way either for osql.exe to continue on
> error, or for me to ignore all stored procedure errors and just keep on
> continuing?
> I'm used to VB's "on error resume next" and unfortunately I don't see
> one in MS SQL Server. This is absurd!
>