Jobs and Always On

Question

I have some jobs which I need to stop if the current server is secondary. I found this code

DECLARE @rc int
set  @rc = sys.fn_hadr_is_primary_replica('DatabaseName') ;
IF @rc = 0
BEGIN;
    --Not Primary, exit job and terminate with error.
    Print 'Not the Primary, ==>EXITING JOB';

    DECLARE @name sysname;
    SELECT  @name = (SELECT name FROM msdb.dbo.sysjobs WHERE job_id = CONVERT(uniqueidentifier, '$(ESCAPE_NONE(JOBID))'));

    EXEC msdb.dbo.sp_stop_job @job_name = @name;
    PRINT 'Stopped the job since this is not a Primary Replica';
END;

However, it is failing at SELECT @name = blah blah step giving an error 'Conversion failed when converting from a character string to unique identifier'.

Any help is truly appreciated.

Shaulinator · Answer 1 · 2017-11-01T15:48:19.387

This should work for the part that is failing.

DECLARE @name NVARCHAR(128);

SELECT @name = NAME
FROM msdb.dbo.sysjobs
WHERE job_id = $(ESCAPE_SQUOTE(JOBID));

This should be the code entirety you'd want to use:

DECLARE @rc INT;

SET @rc = sys.fn_hadr_is_primary_replica('DatabaseName');

IF @rc = 0
BEGIN


    --Not Primary, exit job and terminate with error.
    PRINT 'Not the Primary, ==>EXITING JOB';

    DECLARE @name NVARCHAR(128);

    SELECT @name = NAME
    FROM msdb.dbo.sysjobs
    WHERE job_id = $(ESCAPE_SQUOTE(JOBID));

    EXEC msdb.dbo.sp_stop_job @job_name = @name;

    PRINT 'Stopped the job since this is not a Primary Replica';
END;

Jobs and Always On

1 Answers1