Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSreplagentjobexists

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSreplagentjobexists
(
	@type				int,							-- 0: Distribution (PUSH only) 1: ReplMerge (PUSH only) 2: Logreader 3: Snapshot 4: Queued
	@exists 			bit output,
	@job_name			sysname = NULL output,
	@job_id 			uniqueidentifier = NULL output,
	@job_step_uid		uniqueidentifier = NULL output,
	@proxy_id			int = NULL output,
	@publisher_id		int = NULL output,
	@subscriber_id		int = NULL output,				-- Retrieved for @type 0 only
	@publisher			sysname = NULL,
	@publisher_db		sysname = NULL,
	@publication		sysname = NULL,
	@subscriber 		sysname = NULL,
	@subscriber_db		sysname = NULL,
	@independent_agent	bit = 1,
	@frompublisher      bit = 0
)
as
begin
    set nocount on
	DECLARE @category_id	int,
			@subsystem		sysname,
			@dbname sysname,
			@srvname sysname

	SELECT @exists			= 0,
			@dbname = db_name(),
			@srvname = upper(CONVERT(sysname, SERVERPROPERTY('ServerName')))

	-- security: Has to be executed by SA
	--			 or DBO of distdb
	IF IS_SRVROLEMEMBER('sysadmin') != 1
		AND NOT (IS_MEMBER ('db_owner') = 1
				AND sys.fn_MSrepl_isdistdb(@dbname) = 1)
	BEGIN
		-- You do not have the required permissions to complete the operation.
		RAISERROR (14126, 16, -1)
		RETURN 1
	END
	
	
	-- if executed from publisher
	
	if (@frompublisher = 1)
	begin
		declare @loc_publisher sysname
				,@loc_distribdb sysname
				,@rpcsrvname sysname
				,@rpc nvarchar(1000)
				,@retcode int
		
		-- get the distributor rpc info
		
		select @loc_publisher = @srvname
		EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher = @loc_publisher,
												@rpcsrvname = @rpcsrvname OUTPUT,
												@distribdb = @loc_distribdb OUTPUT

		IF @@error <> 0 OR @retcode <> 0 or (@rpcsrvname IS NULL) or (@loc_distribdb IS NULL)
		BEGIN
			RAISERROR (14080, 16, -1, @loc_publisher)
			RETURN (1)
		END
		
		-- execute the RPC
		
		select @rpc = quotename(@rpcsrvname) + N'.' + quotename(@loc_distribdb) + N'.dbo.sp_MSreplagentjobexists'
		exec @retcode = @rpc @type = @type
							,@exists = @exists output
							,@job_name = @job_name output
							,@job_id = @job_id output
							,@job_step_uid = @job_step_uid output
							,@proxy_id = @proxy_id output
							,@publisher_id = @publisher_id output
							,@subscriber_id = @subscriber_id output
							,@loc_publisher = @loc_publisher
							,@publisher_db = @publisher_db
							,@publication = @publication
							,@subscriber = @subscriber
							,@subscriber_db = @subscriber_db
							,@independent_agent = @independent_agent
							,@frompublisher = 0
		if (@@error != 0)
		select @retcode = 1
		
		-- return
		
		return @retcode
	end

	-- database must be distribution db
	IF sys.fn_MSrepl_isdistdb(@dbname) <> 1
	BEGIN
		RAISERROR (21482, 16, -1, 'sp_MSreplagentjobexists', 'distribution')
		RETURN (1)
	END
	
    
    -- If we are here - this SP is being executed on distributor
    
	
	-- Note that we do not perform parameter checks here
	-- this is because this procedure should only be called by
	-- other replication procedures. Here are the rules:
	
	-- retrieve the publisher server id
	IF @publisher IS NOT NULL
	BEGIN
		SELECT @publisher_id = server_id
			FROM sys.servers
			WHERE UPPER(name) = UPPER(@publisher)
		IF @publisher_id IS null
		BEGIN
			-- Publisher @publisher does not exist.
			RAISERROR(21618, 16, -1, @publisher)
			RETURN 1
		END
	END

	-- Distribution Agent
	IF @type = 0
	BEGIN
		-- retrieve the subscriber server id
		IF @subscriber IS NOT NULL
		BEGIN
			SELECT @subscriber_id = server_id
				FROM sys.servers
				WHERE UPPER(name) = UPPER(@subscriber)
			IF @subscriber_id IS NULL
			BEGIN
				-- The server '@subscriber' is not a Subscriber.
				RAISERROR(14048, 16, -1, @subscriber)
				RETURN 1
			END
		END
		
		-- this only works for PUSH subscription agents created at the distrib
		SELECT @category_id 	= 10,
				@subsystem		= N'Distribution',
				@exists			= 1,
				@job_name		= msda.name,
				@job_id 		= CAST(msda.job_id as uniqueidentifier),
				@job_step_uid	= msda.job_step_uid,
				@proxy_id		= sjs.proxy_id
			FROM msdb.dbo.sysjobs_view sjv
				JOIN MSdistribution_agents msda
					ON sjv.job_id = CAST(msda.job_id as uniqueidentifier)
				JOIN msdb.dbo.sysjobsteps sjs
					ON sjv.job_id = sjs.job_id
						AND msda.job_step_uid = sjs.step_uid
			WHERE msda.publisher_id = @publisher_id
				AND msda.publisher_db = @publisher_db
				AND ((msda.publication = @publication
						AND @independent_agent = 1)
					OR (msda.publication = N'ALL'
						AND @independent_agent = 0))
				AND msda.subscriber_id = @subscriber_id
				AND msda.subscriber_db = @subscriber_db
	END
	-- Merge Agent
	ELSE IF @type = 1
	BEGIN
		-- this only works for PUSH subscription agents created at the distrib
		SELECT @category_id 	= 14,
				@subsystem		= N'Merge',
				@exists			= 1,
				@job_name		= msma.name,
				@job_id 		= CAST(msma.job_id as uniqueidentifier),
				@job_step_uid	= msma.job_step_uid,
				@proxy_id 		= sjs.proxy_id
			FROM msdb.dbo.sysjobs_view sjv
				JOIN dbo.MSmerge_agents msma
					ON sjv.job_id = CAST(msma.job_id as uniqueidentifier)
				JOIN msdb.dbo.sysjobsteps sjs
					ON sjv.job_id = sjs.job_id
						AND msma.job_step_uid = sjs.step_uid
			WHERE msma.publisher_id = @publisher_id
				AND msma.publisher_db = @publisher_db
				AND msma.publication = @publication
				AND UPPER(msma.subscriber_name) = UPPER(@subscriber)
				AND msma.subscriber_db = @subscriber_db
	END
	-- Logreader Agent
	ELSE IF @type = 2
	BEGIN
		SELECT 	@category_id 	= 13,
				@subsystem		= N'LogReader',
				@exists			= 1,
				@job_name		= msla.name,
				@job_id 		= CAST(msla.job_id as uniqueidentifier),
				@job_step_uid	= msla.job_step_uid,
				@proxy_id		= sjs.proxy_id
			FROM msdb.dbo.sysjobs_view sjv
				JOIN MSlogreader_agents msla
					ON sjv.job_id = CAST(msla.job_id as uniqueidentifier)
				JOIN msdb.dbo.sysjobsteps sjs
					ON sjv.job_id = sjs.job_id
						AND msla.job_step_uid = sjs.step_uid
			WHERE msla.publisher_id = @publisher_id
				AND msla.publisher_db = @publisher_db
	END
	-- Snapshot Agent
	ELSE IF @type = 3
	BEGIN
		SELECT @category_id 	= 15,
				@subsystem		= N'Snapshot',
				@exists			= 1,
				@job_name		= mssa.name,
				@job_id 		= CAST(mssa.job_id as uniqueidentifier),
				@job_step_uid	= mssa.job_step_uid,
				@proxy_id		= sjs.proxy_id
			FROM msdb.dbo.sysjobs_view sjv
				JOIN MSsnapshot_agents mssa
					ON sjv.job_id = CAST(mssa.job_id as uniqueidentifier)
				JOIN msdb.dbo.sysjobsteps sjs
					ON sjv.job_id = sjs.job_id
						AND mssa.job_step_uid = sjs.step_uid
			WHERE mssa.publisher_id = @publisher_id
				AND mssa.publisher_db = @publisher_db
				AND mssa.publication = @publication
	END
	-- Queued Agent
	ELSE IF @type = 4
	BEGIN
		SELECT @category_id 	= 19,
				@subsystem		= N'QueueReader',
				@exists			= 1,
				@job_name		= msqa.name,
				@job_id 		= CAST(msqa.job_id as uniqueidentifier),
				@job_step_uid	= msqa.job_step_uid,
				@proxy_id		= sjs.proxy_id
			FROM msdb.dbo.sysjobs_view sjv
				JOIN MSqreader_agents msqa
					ON sjv.job_id = CAST(msqa.job_id as uniqueidentifier)
				JOIN msdb.dbo.sysjobsteps sjs
					ON sjv.job_id = sjs.job_id
						AND msqa.job_step_uid = sjs.step_uid
	END

	-- if we didn't find a job_step_uid but we did find
	-- a job id then let's try to identify the job_step_uid
	IF @job_id IS NOT NULL
		AND @job_step_uid IS NULL
	BEGIN
		SELECT @job_step_uid 	= sjs.step_uid,
				@proxy_id		= sjs.proxy_id
		FROM msdb.dbo.sysjobs_view as sjv
			JOIN msdb.dbo.sysjobsteps as sjs
				ON sjv.job_id = sjs.job_id
		WHERE sjv.job_id = @job_id
			AND sjv.master_server = 0
			AND UPPER(sjv.originating_server) = @srvname
			AND sjv.category_id = @category_id
			AND sjs.subsystem = @subsystem
			AND sjs.database_name = @dbname
	END
		
	RETURN 0
END

 
Last revision 2008RTM
See also

  sp_addmergepushsubscription_agent (Procedure)
sp_addqreader_agent (Procedure)
sp_changemergesubscription (Procedure)
sp_changeqreader_agent (Procedure)
sp_MSaddpub_snapshot (Procedure)
sp_MScdc_tranrepl_check (Procedure)
sp_MSchange_logreader_agent_properties (Procedure)
sp_MSrepl_addlogreader_agent (Procedure)
sp_MSrepl_addpublication (Procedure)
sp_MSrepl_addpushsubscription_agent (Procedure)
sp_MSrepl_changelogreader_agent (Procedure)
sp_MSrepl_changepublication_snapshot (Procedure)
sp_MSrepl_changesubscription (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash