How to get output of SP_HELP_JOB into a script?
-
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
-
you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status
HTH
-
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')
_____________
Code for TallyGenerator -
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
-
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
-
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
-
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 .
-
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
)
-
Thank you, that saved me some time!
-
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
- Jen-169507 - Thursday, February 16, 2006 8:58 PM
Hi,
Can you please give me the script o the same?Prakash Anand
- prakashanand2223 - Saturday, September 22, 2018 1:13 AM
You should try making the mods yourself for two reasons... 1) you have to support it and 2) it's another good way of learning. Use sp_helptext to get the code and have at it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - prakashanand2223 - Saturday, September 22, 2018 1:13 AM
You are asking for help, or actually you are asking someone to do your work, on a thread that is 9 years old who has not logged onto this site for 9 years.
Why don't you start a new thread, explain what your issue may be, what you want to do, and what you have tried. You will probably get help faster, and more importantly, help that is specific to your needs.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/ - Michael L John - Saturday, September 22, 2018 4:40 PM
Heh... either that or go back through this very thread and see that there are at least two other solutions already available. My favorite is the OPENROWSET one but it does sometimes cause problems depending on how the procedure being called was written. Sometimes it's written in such a fashion that it doesn't expose the meta-data necessary to successfully use SELECT/INTO to build a Temp Table from the results.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Michael L John - Saturday, September 22, 2018 4:40 PM
Hi John,
I tried something different and the sql procedure which is mentioned below will fetch all the sql jobs getting executed at different servers (condition is that all the servers names must be stored in server_list table).As per my requirement I also appended stop job proc into the same procedure.
If not required then comment out those codes.--SQL PROCEDURE START
ALTER PROCEDURE [dbo].[get_all_servers_job_executing_list]
AS
BEGIN
SET NOCOUNT ON;declare @servercount varchar(max);
declare @servername varchar(max);
select @servercount = count(*) from server_list --(server_list is the table where all the server names are stored. It must have a column ---server_name) ;
declare @jobname varchar(max)
declare @query varchar (max);
declare @query1 varchar (1000);
declare @query2 varchar (1000);
declare @i varchar(max) = 1;
declare @JOBcount varchar(max);
declare @j-2 varchar(max) = 1;
create TABLE records1
(
[Job ID] UNIQUEIDENTIFIER,[Last Run Date] CHAR(8),[Last Run Time] CHAR(6),[Next Run Date] CHAR(8),[Next Run Time] CHAR(6),[Next Run Schedule ID] INT,
[Requested To Run] INT,[Request Source] INT,[Request Source ID] SQL_VARIANT,[Running] INT,[Current Step] INT,[Current Retry Attempt] INT,[State] INT
)
while (@i <= @servercount )
begin
WITH s AS (
SELECT (ROW_NUMBER() OVER (ORDER BY server_name)) as row,server_name
FROM server_list )
SELECT @servername= server_name FROM s WHERE row = @i
set @query1= 'INSERT INTO records1 EXECUTE ' + @servername +' .master.dbo.xp_sqlagent_enum_jobs @can_see_all_running_jobs=1,@job_owner=''0x4A6F6E47757267756C'''
--print (@query1)
exec (@query1);
alter table records1 add server_name varchar(100)
update records1 set server_name = @servernameset @query = 'SElECT
t.server_name,
t.[Request Source ID] [Requester]
,t.[Job ID] [JobID]
,sj.[name] [JobName]
,sjs.[step_id] [StepID]
,sjs.[step_name] [StepName]
into records2
FROM records1 t
INNER JOIN '+@servername+'. msdb.dbo.sysjobs sj ON t.[Job ID] = sj.[job_id]
INNER JOIN '+@servername+'. msdb.dbo.sysjobsteps sjs ON sjs.[job_id] = sj.[job_id]
AND t.[Job ID] = sjs.[job_id]
AND t.[Current Step] = sjs.[step_id]
INNER JOIN
(
SELECT * FROM '+@servername+' . msdb.dbo.sysjobactivity d
WHERE EXISTS
(
SELECT 1
FROM ' +@servername+' . msdb.dbo.sysjobactivity l
GROUP BY l.[job_id]
HAVING l.[job_id] = d.[job_id]
AND MAX(l.[start_execution_date]) = d.[start_execution_date]
)
) sja
ON sja.[job_id] = sj.[job_id]
LEFT JOIN (SELECT SUBSTRING([program_name],30,34) p,[session_id] FROM ' +@servername+' .msdb . sys.dm_exec_sessions
WHERE [program_name] LIKE ''SQLAgent - TSQL JobStep%'') es
ON CAST('''' AS XML).value(''xs:hexBinary(substring(sql:column("es.p"),3))'',''VARBINARY(MAX)'') = sj.[job_id]'--print (@query)
exec (@query)
alter table records1 drop column server_name;SELECT * INTO RECORDS3 FROM records2
DROP TABLE mi_uwhca.dbo. records2select @JOBcount = count(*) from RECORDS3 ;
while (@J <= @JOBcount )
begin
WITH P AS (
SELECT (ROW_NUMBER() OVER (ORDER BY JOBname)) as row,JOBname
FROM RECORDS3 )
SELECT @JOBname= JOBname FROM P WHERE row = @j-2
set @query2 = 'exec ' +@servername +'. msdb.[dbo].[sp_stop_job]' + @JOBNAME
--print(@query2)
exec (@query2)
PRINT @JOBNAME + ' STOPPED EXEXCUTING'
SET @j-2 = @j-2+1
END
insert into records4 select * from records3 --where jobname in (select distinct jobname from RECORDS3 )
drop table RECORDS3
set @i= @i+1
truncate table records1
enddrop table records1
END
--SQL PROCEDURE ENDResults will be saved in table records4.
Please let me know if any further query arises.
Thank You,
Prakash Anand
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply