SQLServerCentral.com / T-SQL (SS2K5) / SQL Server 2005 / how to retrieve value from msdb.dbo.sp_help_job / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 06:32:45 GMT20RE: how to retrieve value from msdb.dbo.sp_help_jobhttp://www.sqlservercentral.com/Forums/Topic1045937-338-1.aspxI have the exact same issue - not been able to find a workaround :-/Wed, 16 Mar 2011 06:24:28 GMTSpindriftRE: how to retrieve value from msdb.dbo.sp_help_jobhttp://www.sqlservercentral.com/Forums/Topic1045937-338-1.aspxAlex 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!DKGTue, 11 Jan 2011 16:11:10 GMTDKG-967908RE: how to retrieve value from msdb.dbo.sp_help_jobhttp://www.sqlservercentral.com/Forums/Topic1045937-338-1.aspx[quote][b]DKG-967908 (1/11/2011)[/b][hr]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[/quote]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', 1goreconfigure with overridegosp_configure 'Ad Hoc Distributed Queries', 1goreconfigure with overridego------------------------------declare @runstatus intset @runstatus = (select last_run_outcomefromopenrowset ('SQLOLEDB','Server=(local);Trusted_Connection=yes','SET FMTONLY OFF EXEC msdb.dbo.sp_help_job') qwhere name = '')print @runstatusTue, 11 Jan 2011 15:05:59 GMTAlexSQLForumsRE: how to retrieve value from msdb.dbo.sp_help_jobhttp://www.sqlservercentral.com/Forums/Topic1045937-338-1.aspxThanks 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,DKGTue, 11 Jan 2011 13:44:04 GMTDKG-967908RE: how to retrieve value from msdb.dbo.sp_help_jobhttp://www.sqlservercentral.com/Forums/Topic1045937-338-1.aspx[quote][b]DKG-967908 (1/11/2011)[/b][hr]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[/quote]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.Tue, 11 Jan 2011 12:49:16 GMTAlexSQLForumsRE: how to retrieve value from msdb.dbo.sp_help_jobhttp://www.sqlservercentral.com/Forums/Topic1045937-338-1.aspxYou may find [b][url=http://www.sqlservercentral.com/scripts/Miscellaneous/30916/]this script[/url][/b] useful.Tue, 11 Jan 2011 12:38:38 GMTAdigahow to retrieve value from msdb.dbo.sp_help_jobhttp://www.sqlservercentral.com/Forums/Topic1045937-338-1.aspxHi 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,DKGTue, 11 Jan 2011 08:31:02 GMTDKG-967908