Select Single Column From exec msdb.dbo.sp_help_job

Question

Select Single Column From exec msdb.dbo.sp_help_job

David-155102

SSCarpal Tunnel

Points: 4361
More actions
November 8, 2007 at 12:45 pm

#200245

Is there a way to select just a single column from "exec msdb.dbo.sp_help_job"? I only want the current_execution_status column.

Viewing 5 posts - 1 through 4 (of 4 total)

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

David McFarland SSChampion Points: 11815 More actions · Answer 1

For a quick and dirty solution, you can just make a copy of that system stored proc (carefully, as you don't want to overwrite existing system stored procs if you can avoid it), and modify it to return only the column(s) you want.

If you want something to reuse over and over, I'd recommend you use that reverse engineer their stored proc, and use it to write your own, avoiding all of the overhead for the other stuff that that returns.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

select

current_execution_status

from

openrowset('SQLOLEDB','SERVER=(LOCAL);Trusted_Connection=yes;',

'set fmtonly off;execute msdb.dbo.sp_help_job') rmt

where

name = 'My Job Name'

David McFarland SSChampion Points: 11815 More actions · Answer 3

Depending on your environment, that solution may or may not be acceptable. By default, I don't think the surface area configurator is going to allow the openrowset shown above, although that can be changed if acceptable.

David-155102 SSCarpal Tunnel Points: 4361 More actions · Answer 4

Hi,

I managed to get the single column by creating a temp table and inserting all current jobs into the table. I then simply queried over the temp table:

INSERT INTO #xp_results
EXEC master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner
SELECT .... FROM #xp_results

This approach was suggested on a different site but I forget which site it was. Thanks to you both for your suggestions, much appreciated.