-- Name: sp_MSisqueuereaderjobnamegenerated
-- Descriptions: This procedure checks whether a given job id corresponds to
-- a queuereader agent job with a name generated by replication.
-- If so, a return value of zero will be returned, otherwise
-- a value of one will be returned to the caller.
-- Notes: 1) For this procedure to be effective, it must match the current
-- queuereader agent name generation algorithm. Note that the
-- algorithm used for matching auto-generated job name is not
-- exact. (See sp_MSadd_qreader_agent instdist.sql)
-- 2) This procedure is supposed to be executed at the distributor.
-- Parameters: @job_id uniqueidentifier
-- Returns: 0 - If the specified jobid corresponds to a queuereader job with
-- a generated name.
-- 1 - Otherwise
-- Security: This is an internal system procedure.
create procedure sys.sp_MSisqueuereaderjobnamegenerated
(
@job_id uniqueidentifier
)
as
begin
set nocount on
declare @job_name sysname
declare @generated_job_name nvarchar(4000)
select @job_name = null
select @job_name = name
from msdb.dbo.sysjobs_view
where job_id = @job_id
-- Makes sure that the given job_id refers to an existing job
if @job_name is null
begin
goto MISMATCH
end
-- Checks to make sure that the given job id corresponds to a queuereader
-- job (i.e. it has a queue reader job step)
if not exists (select *
from msdb.dbo.sysjobsteps
where job_id = @job_id
and upper(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'QUEUEREADER')
begin
goto MISMATCH
end
-- QueueReader job name is generated based on the server name of
-- the distributor and the distribution database id. Here,
-- we assume that this procedure is executed in the context of a
-- distribution database so we can just use @@servername and
-- db_name() to grab these two pieces of information
select @generated_job_name = quotename(@@servername) + '.' + cast(db_id() as nvarchar)
if UPPER(@job_name) = UPPER(@generated_job_name)
begin
return 0
end
MISMATCH:
if @job_name is not null
begin
raiserror(21695, -1, -1, @job_name)
end
return 1
end