how to retrieve value from msdb.dbo.sp_help_job

Question

Post reply

how to retrieve value from msdb.dbo.sp_help_job

DKG-967908

SSCrazy

Points: 2345
More actions
January 11, 2011 at 8:31 am

#388602

Hi All,
I have created a stored procedure with a int parameter - if user passes 0 it will stop a particular job and disable it (as per users requirement) and if user passes 1 it will enable that particular job.
I have done with SP and its working fine. The only issue is i have used Openrowset is my stored procedure to get a current status of that job:
declare @status int
set @status = (select last_run_outcome from openrowset
('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q
where name = 'test job')
By some reason i cannt use openrowset here.
Is anyone can help me getting a different method to capture a value from sp_help_job.
An early reply would be appreciated.
Thanks,
DKG

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

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

Adiga One Orange Chip Points: 27284 More actions · Answer 1

You may find this script[/url] useful.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

AlexSQLForums SSChampion Points: 14277 More actions · Answer 2

DKG-967908 (1/11/2011)
Hi All,
I have created a stored procedure with a int parameter - if user passes 0 it will stop a particular job and disable it (as per users requirement) and if user passes 1 it will enable that particular job.
I have done with SP and its working fine. The only issue is i have used Openrowset is my stored procedure to get a current status of that job:
declare @status int
set @status = (select last_run_outcome from openrowset
('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') q
where name = 'test job')
By some reason i cannt use openrowset here.
Is anyone can help me getting a different method to capture a value from sp_help_job.
An early reply would be appreciated.
Thanks,
DKG

That's because sp_help_job is not a table. it's a stored procedure.

You can use linked server queries to retrieve job status from other servers as long as the login has administrative rights.

Lookup sysjobhistory in Books Online.

Alex S

DKG-967908 SSCrazy Points: 2345 More actions · Answer 3

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

AlexSQLForums SSChampion Points: 14277 More actions · Answer 4

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

Alex S

DKG-967908 SSCrazy Points: 2345 More actions · Answer 5

Alex you didn't understand...the code I have mentioned (posted by me initially) working perfectly fine. The only issue is that I cant use (means I don’t want to use) openrowset. That’s why I am looking for some other work around.

cheers!

DKG

Spindrift Ten Centuries Points: 1227 More actions · Answer 6

I have the exact same issue - not been able to find a workaround :-/