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