-- Name: sp_MSismergejobnamegenerated

-- Descriptions: This procedure checks whether a given job id corresponds to
--               a merge 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
--           merge agent name generation algorithm. Note that the
--           algorithm used for matching auto-generated job name is not
--           exact. (See sp_MSadd_merge_agent instdist.sql)
--        2) This procedure is supposed to be executed at the distributor.
--           The @publisher, @publisher_db, and @publication parameters
--           are supposed to be passed through the RPC link from the
--           publisher.
--        3) There are two distinct code paths for the generation of
--           the merge job name, one uses the agent id, and the other
--           uses a guid via the fn_repluniquename function.

-- Parameters: @publisher     sysname
--             @publisher_db  sysname
--             @publication   sysname (This can be null for shared agent)
--             @subscriber    sysname
--             @job_id        uniqueidentifier

-- Returns: 0 - If the specified jobid corresponds to a snapshot job with
--              a generated name.
--          1 - Otherwise

-- Security: This is an internal system procedure.

create procedure sys.sp_MSismergejobnamegenerated
(
    @publisher    sysname,
    @publisher_db sysname,
    @publication  sysname,
    @subscriber   sysname,
    @job_id       uniqueidentifier
)
as
begin
    set nocount on

    declare @generated_job_name nvarchar(4000)
    declare @generated_job_name_length int
    declare @job_name sysname

    select @job_name = null
    select @job_name = name
      from msdb.dbo.sysjobs_view
     where job_id = @job_id

    if @job_name is null
    begin
        goto MISMATCH
    end

    -- First of all, make sure that the job is indeed a merge agent
    -- job (there should be a step with Merge as the subsystem in the job)
    if not exists (select *
                     from msdb.dbo.sysjobsteps
                    where job_id = @job_id
                      and upper(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'MERGE')
    begin
       goto MISMATCH
    end

    -- 1) Try doing a match for a name generated based on agentid
    select @generated_job_name = left(@publisher, 21) + N'-'
                               + left(@publisher_db, 21) + N'-'
                               + left(@publication, 21) + N'-'
                               + left(@subscriber, 21) + N'-'
                               + N'[0-9]%'

    if upper(@job_name) like upper(@generated_job_name)
    begin
        return 0
    end

    -- 2) Try doing a match for a name generated using a guid, see fn_repluniquename
    select @generated_job_name = sys.fn_repluniquename (NULL,
    														@publisher,
    														@publisher_db,
    														@publication,
    														@subscriber)

    select @generated_job_name_length = len(@generated_job_name)

    -- Try matching the prefix of the job name
    if upper(left(@job_name, @generated_job_name_length)) = upper(@generated_job_name)
    begin
        -- Checks if the tail end of the job name matches a hexadecimal guid
        if upper(right(@job_name, len(@job_name) - @generated_job_name_length) collate SQL_Latin1_General_CP1_CS_AS)
            like N'[0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9A-F][0-9
A-F][0-9A-F][0-9A-F]'
        begin
            return 0
        end
      else
        begin
            goto MISMATCH
        end
    end

MISMATCH:
    if @job_name is not null
    begin
        raiserror(21695, -1, -1, @job_name)
    end
    return 1
end