i got it. it's the simple things...

turns out after you have the (begin) all you need to do is prefix the

sp_update_jobstep with the following.

execute msdb.dbo.

and syntax checks out fine...

actually having some errors with this although the tsql looks

fine.

use msdb

go

declare @myrun_status int

select @myrun_status = [run_status]

from sysjobs

inner join sysjobhistory

on sysjobs.job_id = sysjobhistory.job_id

where sysjobs.name = 'MYJOB'

if @myrun_status =...

WOW... thanks for all the good replies!!

i'm working through this now.

i really appreciate it!

what about the then/else part? any thoughts on that?

excellent post, but just tried this, and having some issues.

don't suppose you can script out the job in it's entirety?

i would like to see the whole thing if thats ok.

yes but it doesn't show ALL jobs.

i'm trying to create a list of all jobs success, fail, enabled, disabled,

the works! only the most current job given. even...

cool... thanks for the reply, but

now i'm getting this:

Msg 241, Level 16, State 1, Line 5

Conversion failed when converting datetime from character string.

how can the "where sysjobhistory.step_id" be causing this?

"I want to point out that run_time is an int and when a job runs one second after midnight the value will be 1 in the column causing the sql...

good idea SQL ORACLE,

but there are some minor adjustments

ie;

use msdb & run_date enclosed in brackets [run_date], and where the query is drawing from like (FROM sysjobhistory)

here it is...

it's unreal the amount of conversion in the first reply though...

works great once you replace the (") double quotes with the (') single's,

and then a quick adjustment on the (order...

Ninja,

thanks for the heads-up with the single vs double quotes, but there

is an additional error:

Msg 408, Level 16, State 1, Line 2

A constant expression was encountered in the ORDER BY...

wangkhar

this works pretty good... thanks!!

use msdb

go

select

cast

(ltrim(str(run_date))+ ' '+stuff(stuff(right('000000'+ltrim(str(run_time)), 6) , 3, 0, ':'), 6, 0, ':')

as datetime) as 'last run' -- i just added this real quick.

from sysjobhistory

i'll...

michael,

getting the following error from that script:

Msg 241, Level 16, State 1, Line 2

Conversion failed when converting datetime from character string.

thoughts?

Kory

getting alot of the following errors based on the zero's

Invalid column name '00000'

Invalid column name '0000'

Invalid column name '000'

Invalid column name '00'

Invalid column name '0'

etc...

Y I K E S!

let me dive right into this and see if i can understand whats going on.

thanks