Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to retrieve value from msdb.dbo.sp_help_job Expand / Collapse
Author
Message
Posted Tuesday, January 11, 2011 8:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
Points: 63, Visits: 239
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
Post #1045937
Posted Tuesday, January 11, 2011 12:38 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Friday, August 1, 2014 6:18 AM
Points: 1,618, Visits: 20,980
You may find this script useful.

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Post #1046091
Posted Tuesday, January 11, 2011 12:49 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 1,294, Visits: 1,868
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
Post #1046100
Posted Tuesday, January 11, 2011 1:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
Points: 63, Visits: 239
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
Post #1046141
Posted Tuesday, January 11, 2011 3:05 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 7:37 AM
Points: 1,294, Visits: 1,868
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
Post #1046180
Posted Tuesday, January 11, 2011 4:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, October 29, 2014 7:13 AM
Points: 63, Visits: 239
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
Post #1046195
Posted Wednesday, March 16, 2011 6:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, November 13, 2014 2:31 AM
Points: 439, Visits: 172
I have the exact same issue - not been able to find a workaround :-/
Post #1078893
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse