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