The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_MSreplagentjobexists(int @type, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, bit @independent_agent
, bit @frompublisher)
MetaData:
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
No comments:
Post a Comment