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