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

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