DKG-967908 (1/11/2011)
Thanks Adiga and Alex for your response, Adiga that script is good but not solving my purpose 🙁 .Alex there must be some way to get a value from sp_help_job (variable or temp table). I am not very good in scripting that’s why struggling.
thanks,
DKG
DKG i was wrong about sp_help_job it turns out it does work and i was able to get a status of a job.
See if running these commands help:
------------------------------
sp_configure 'show advanced options', 1
go
reconfigure with override
go
sp_configure 'Ad Hoc Distributed Queries', 1
go
reconfigure with override
go
------------------------------
declare @runstatus int
set @runstatus = (select last_run_outcome
from
openrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q
where name = '')
print @runstatus