Post reply

Select Single Column From exec msdb.dbo.sp_help_job

  • 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.

  • November 8, 2007 at 1:28 pm

    #749629

    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

    #749660

    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

    #749994

    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

    #750164

    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