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.