Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to get output of SP_HELP_JOB into a script?


How to get output of SP_HELP_JOB into a script?

Author
Message
Richard Culpan
Richard Culpan
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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


Jen-169507
Jen-169507
Valued Member
Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)Valued Member (50 reputation)

Group: General Forum Members
Points: 50 Visits: 12

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

HTH


Sergiy
Sergiy
SSCertifiable
SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)SSCertifiable (6.4K reputation)

Group: General Forum Members
Points: 6416 Visits: 11541

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')



Daryl AZ
Daryl AZ
SSC Veteran
SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)SSC Veteran (208 reputation)

Group: General Forum Members
Points: 208 Visits: 685
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
Richard Culpan
Richard Culpan
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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


Richard Culpan
Richard Culpan
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 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


raoul.teeuwen
raoul.teeuwen
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 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 .
RichardBo
RichardBo
Old Hand
Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)Old Hand (382 reputation)

Group: General Forum Members
Points: 382 Visits: 251
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
)
pdornai2
pdornai2
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 375
Thank you, that saved me some time!
michael.a.keleher
michael.a.keleher
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 360
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search