-- Name: sp_MSissnapshotjobnamegenerated

-- Descriptions: This procedure checks whether a given job id corresponds to
--               a snapshot 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
--           snapshot agent name generation algorithm. Note that the
--           algorithm used for matching auto-generated job name is not
--           exact. (See sp_MSadd_snapshot_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 snapshot 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
--             @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_MSissnapshotjobnamegenerated
(
    @publisher     sysname,
    @publisher_db  sysname,
    @publication   sysname,
    @job_id        uniqueidentifier
)
AS
BEGIN
    SET NOCOUNT ON

	DECLARE @retcode					int
    DECLARE @generated_job_name			nvarchar(4000)
    DECLARE @generated_job_name_length	int
    DECLARE @job_name					sysname
    DECLARE	@publisher_type				sysname
    DECLARE @loc_publisher_db			sysname

    -- First, make sure that the given job id corresponds to a job
	SELECT	@job_name = name
	FROM	msdb.dbo.sysjobs_view
	WHERE	job_id = @job_id

    IF @job_name IS NULL
    BEGIN
        GOTO MISMATCH
    END

    -- Checks whether the specified job id corresponds to a snapshot agent
    -- job (i.e. the job contains snapshot subsystem steps)
    IF NOT EXISTS
    (
    	SELECT	*
		FROM	msdb.dbo.sysjobsteps
		WHERE	job_id = @job_id
		  AND	UPPER(subsystem collate SQL_Latin1_General_CP1_CS_AS) = N'SNAPSHOT'
	)
    BEGIN
        GOTO MISMATCH
    END

	-- Get publisher type
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher		= @publisher,
														@publisher_type	= @publisher_type OUTPUT

	IF @retcode != 0 OR @@ERROR != 0
	BEGIN
		RETURN (1)
	END

   	-- For naming purposes, use @publisher instead of @publisher_db for HREPL
   	-- publishers that don't support publisher db notion
    IF @publisher_type LIKE N'ORACLE%'
    BEGIN
    	SELECT @loc_publisher_db = @publisher
    END
    ELSE
    BEGIN
    	SELECT @loc_publisher_db = @publisher_db
    END

    -- 1) Try doing a match for a generated snapshot job name based on agentid
    SELECT	@generated_job_name =	LEFT(@publisher, 28) + N'-' +
    								LEFT(@loc_publisher_db, 28) + N'-' +
    								LEFT(@publication, 28) + N'-' + N'[0-9]%'

    IF UPPER(@job_name) LIKE UPPER(@generated_job_name)
    BEGIN
        RETURN 0
    END

    -- 2) Try doing a match for a generated snapshot job name based on
    -- uniqueidentifier, see fn_repluniquename
    SELECT	@generated_job_name = sys.fn_repluniquename (NULL,
    														@publisher,
    														@loc_publisher_db,
    														@publication,
    														NULL)

    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