November 8, 2007 at 12:45 pm
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.
November 8, 2007 at 1:28 pm
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.
November 8, 2007 at 3:03 pm
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'
November 9, 2007 at 2:26 pm
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.
November 12, 2007 at 1:49 am
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.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply