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 67An 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
you can modify the existing stored procedure, rename it and use that instead incorporating what you need to monitor the status
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.
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 sysnameset nocount on
/* 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 TempJobCursorfetch next from TempJobCursor into @TempJobId, @TempJobNamewhile @@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, @TempJobNameend
/* Clean up */
close TempJobCursordeallocate TempJobCursorset nocount off