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 get output of SP_HELP_JOB into a script? Expand / Collapse
Author
Message
Posted Thursday, February 16, 2006 8:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 27, 2009 1:34 AM
Points: 8, Visits: 25

Trying to write a T-SQL script to check up on the status of a replication merge agent job that randomly stops.

I tried creating a temporary table and using the INSERT .. EXEC kind of syntax - however sp_help_job uses insert .. exec commands internally and these cannot be nested


So, a statement such as:

insert into #TempJobTable
   execute sp_help_job @category_name=N'REPL-Merge', @enabled=1, @execution_status=4

results in an error such as:

Server: Msg 8164, Level 16, State 1, Procedure sp_get_composite_job_info, Line 67
An INSERT EXEC statement cannot be nested.

I also tried to do the status test implicitly by specifiying the input parameters to sp_help_job so that it only returned information about the job I was interested in; with the paramaters I used the rowset was empty if the job was running and had one row if the job was stopped. However sp_help_job did not set @@rowcount to the number of rows that it returned (it was still set from the outcome of earlier statements in my script where I was looking up the job ID)    


Is there a way in which I can test the output of sp_help_job in a script, by getting it into a temporary table or into a bunch of local variables

Thanks,
Richard

Post #259078
Posted Thursday, February 16, 2006 8:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 26, 2009 8:06 AM
Points: 48, Visits: 12

you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status

 

HTH

Post #259277
Posted Thursday, February 16, 2006 9:18 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366

Not sure it will work, but why not try?

SET @SQL = insert into #TempJobTable
  EXEC('execute sp_help_job @category_name=N''REPL-Merge'', @enabled=1, @execution_status=4')


 

Post #259282
Posted Friday, February 17, 2006 11:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:01 PM
Points: 199, Visits: 600

SELECT * INTO #JobInfo
FROM OPENROWSET('sqloledb', 'server=(local);trusted_connection=yes'
, 'set fmtonly off exec msdb.dbo.sp_help_job @execution_status=4')

This works to get the info intoa temp table. The main advantage is no need to define the temp table. The select * into handles column creation.

I am not sure what the SET @sql = insert ... is doing. I was hoping it was simpler method to populate the tmp table, but I all i can see is populating the @sql variable.

cheers
Post #259535
Posted Monday, February 20, 2006 2:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 27, 2009 1:34 AM
Points: 8, Visits: 25

Thanks for all the replies,

Using the OPENROWSET method worked nicely.

 Note: the 'set fmtonly off' part of the command is necessary; the default in this case appears to be 'ON' which is not very useful and causes an error such as:
Could not process object 'exec msdb.dbo.sp_help_job @execution_status=4'. The OLE DB provider 'sqloledb' indicates that the object has no columns.

Thanks,
Richard

Post #259781
Posted Monday, February 20, 2006 6:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, February 27, 2009 1:34 AM
Points: 8, Visits: 25

In case it helps anyone else, here is my completed script to check the status of the merge replication agent (that was started when the SQL Server Agent started -  and should be running continuously). If the merge agent is found to be stopped a message is logged and it is restarted.

This can be run as a job on the server where the replication agent is running (in my case I was using push subscriptions so the script runs at the Distributor).

 

declare @TempJobId uniqueidentifier, @TempJobName sysname
set nocount on

use msdb

/* Search in MSDB for enabled replication merge jobs that are scheduled to run when SQL Server Agent starts */

declare TempJobCursor cursor local fast_forward for
   select j.job_id, j.name from syscategories c, sysjobs j, sysjobschedules s where
      j.job_id = s.job_id and
      j.category_id = c.category_id and
      c.name = N'REPL-Merge' and
      s.freq_type = 64 and
      j.enabled = 1 and
      s.enabled = 1

open TempJobCursor
fetch next from TempJobCursor into @TempJobId, @TempJobName
while @@fetch_status = 0

/* Found a matching job - check its status */

begin
   exec ('declare @TempStatus int
         select @TempStatus = current_execution_status from openrowset(''sqloledb'',
            ''server=(local);trusted_connection=yes'',
            ''set fmtonly off execute msdb.dbo.sp_help_job @job_id=''''' + @TempJobId +
            ''''', @job_aspect=''''JOB'''', @execution_status=4'')')
   if @@rowcount = 1

/* Job found to be in the idle (not running) state; log an event and restart it */

   begin
      exec ('execute sp_start_job @job_id=''' + @TempJobId + '''')
      raiserror ('Watchdog: Restarted Merge Replication agent %s', 0, 1, @TempJobName) with log
   end
   fetch next from TempJobCursor into @TempJobId, @TempJobName
end

/* Clean up */

close TempJobCursor
deallocate TempJobCursor
set nocount off

HTH
Richard

Post #259842
Posted Thursday, September 25, 2008 2:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 22, 2010 9:08 AM
Points: 2, Visits: 27
I also needed to find a solution and eventually found a simple one and documented it on my blog: http://raoulteeuwen.blogspot.com/2008/09/query-job-last-run-status-in-ms-sql.html .
Post #575768
Posted Wednesday, June 1, 2011 8:17 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, February 20, 2014 4:13 PM
Points: 371, Visits: 250
I was looking at this and although I've decided on the OPENROWSET method, I have got the table structure returned by sp_help_job if that helps anyone.

CREATE TABLE #JobInfo(
[job_id] [uniqueidentifier] NULL,
[originating_server] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[name] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[enabled] [tinyint] NULL,
[description] [nvarchar](512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[start_step_id] [int] NULL,
[category] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[owner] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notify_level_eventlog] [int] NULL,
[notify_level_email] [int] NULL,
[notify_level_netsend] [int] NULL,
[notify_level_page] [int] NULL,
[notify_email_operator] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notify_netsend_operator] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[notify_page_operator] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[delete_level] [int] NULL,
[date_created] [datetime] NULL,
[date_modified] [datetime] NULL,
[version_number] [int] NULL,
[last_run_date] [int] NOT NULL,
[last_run_time] [int] NOT NULL,
[last_run_outcome] [int] NOT NULL,
[next_run_date] [int] NOT NULL,
[next_run_time] [int] NOT NULL,
[next_run_schedule_id] [int] NOT NULL,
[current_execution_status] [int] NOT NULL,
[current_execution_step] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[current_retry_attempt] [int] NOT NULL,
[has_step] [int] NULL,
[has_schedule] [int] NULL,
[has_target] [int] NULL,
[type] [int] NOT NULL
)
Post #1118070
Posted Wednesday, November 28, 2012 1:48 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 7:59 PM
Points: 8, Visits: 273
Thank you, that saved me some time!
Post #1390111
Posted Thursday, January 30, 2014 1:53 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 6, 2014 9:44 AM
Points: 1, Visits: 259
Tested on SQL Server 2005 and 2012. This script select jobs that are currently running.

declare @CurrentJobs table
(
[Job ID] uniqueidentifier,
[Last Run Date] varchar(255),
[Last Run Time] varchar(255),
[Next Run Date] varchar(255),
[Next Run Time] varchar(255),
[Next Run Schedule ID] varchar(255),
[Requested To Run] varchar(255),
[Request Source] varchar(255),
[Request Source ID] varchar(255),
[Running] varchar(255),
[Current Step] varchar(255),
[Current Retry Attempt] varchar(255),
[State] varchar(255)
)
insert into @CurrentJobs
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,''

select *
from @CurrentJobs cj
join msdb.dbo.sysjobs sj
on
cj.[Job ID]= sj.job_id
where Running=1
Post #1536546
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse