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_MSdrop_distribution_agent(smallint @publisher_id, nvarchar @publisher_db
, nvarchar @publication
, smallint @subscriber_id
, nvarchar @subscriber_db
, int @subscription_type
, bit @keep_for_last_run
, bit @job_only)
MetaData:
CREATE PROCEDURE sys.sp_MSdrop_distribution_agent ( @publisher_id smallint, @publisher_db sysname, @publication sysname, @subscriber_id smallint, @subscriber_db sysname, @subscription_type int, @keep_for_last_run bit = 0, @job_only bit = 0 ) AS BEGIN SET NOCOUNT ON -- -- Declarations. -- DECLARE @stopcode int ,@retcode int ,@job_id binary(16) ,@job_step_uid uniqueidentifier ,@is_continuous bit ,@local_job bit ,@publisher sysname ,@schedule_name sysname ,@job_command nvarchar(512) ,@name nvarchar(100) ,@agent_id int ,@queue_id sysname ,@qservicestatus int ,@qservername nvarchar(255) ,@subscriber sysname -- -- security check -- only db_owner can execute this -- if (is_member ('db_owner') != 1) begin raiserror(14260, 16, -1) return (1) end select @stopcode = 1 ,@qservername = queue_server ,@job_id = job_id, @local_job = local_job, @name = name, @agent_id = id, @job_step_uid = job_step_uid, @queue_id = queue_id FROM MSdistribution_agents WHERE publisher_id = @publisher_id AND publisher_db = @publisher_db AND publication = @publication and subscriber_id = @subscriber_id and subscriber_db = @subscriber_db and subscription_type = @subscription_type -- Delete Perfmon instance dbcc deleteinstance ("SQL Replication Distribution", @name) select @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id select @subscriber = srvname from master..sysservers where srvid = @subscriber_id -- Return if not exists IF @local_job IS NULL RETURN(0) BEGIN TRAN if @queue_id is not null and @queue_id != N'mssqlqueue' and @job_only = 0 begin -- -- MQ specific processing -- exec @retcode = sys.sp_MSdropmqforsubscription @qservername, @queue_id IF @retcode != 0 GOTO UNDO end IF @local_job = 1 and @keep_for_last_run = 0 BEGIN IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @job_id) BEGIN -- Checks if the job name matches one that is generated -- by replication EXEC @retcode = sys.sp_MSisdistributionjobnamegenerated @publisher = @publisher, @publisher_db = @publisher_db, @publication = @publication, @subscriber = @subscriber, @job_id = @job_id IF @@ERROR <> 0 GOTO UNDO -- Only drop the job if the name was generated IF @retcode = 0 BEGIN EXEC @retcode = sys.sp_MSdrop_repl_job @job_id = @job_id, @job_step_uid = @job_step_uid IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO END END END IF @local_job = 1 and @keep_for_last_run = 1 BEGIN select @job_command=command from msdb.dbo.sysjobsteps where job_id=@job_id and step_id=2 if PATINDEX('%-[Cc][Oo][Nn][Tt][Ii][Nn][Uu][Oo][Uu][Ss]%', @job_command) > 0 begin select @is_continuous = 1 create table #sqlstatus(status nvarchar(20)) insert into #sqlstatus (status) exec master.dbo.xp_servicecontrol 'QUERYSTATE', 'SQLServerAgent' if exists (select * from #sqlstatus where status='Running.') exec @stopcode = msdb.dbo.sp_stop_job @job_id = @job_id if @@ERROR<>0 GOTO UNDO drop table #sqlstatus if @stopcode=0 waitfor delay '00:00:30' end EXEC @retcode = msdb.dbo.sp_update_job @job_id=@job_id, @delete_level=3 -- NOTE: Run once, success or failure! IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO EXEC @retcode = msdb.dbo.sp_delete_jobstep @job_id=@job_id, @step_id=3 IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO EXEC @retcode = msdb.dbo.sp_delete_jobstep @job_id=@job_id, @step_id=1 IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO select @job_command=command from msdb.dbo.sysjobsteps where job_id=@job_id and step_id=1 select @job_command = @job_command + ' -UnSubscribe 0 ' -- currently the value does not really matter EXEC @retcode = msdb.dbo.sp_update_jobstep @job_id=@job_id, @step_id=1, @on_success_action=1, @on_fail_action=2, @command=@job_command IF @@ERROR <> 0 or @retcode <> 0 GOTO UNDO select @schedule_name = formatmessage(20532) EXEC @retcode = msdb.dbo.sp_update_jobschedule @job_id=@job_id, @name=@schedule_name, @freq_subday_type = 2, @freq_subday_interval=30 IF @@ERROR<>0 or @retcode<>0 GOTO UNDO if (@is_continuous = 1) and (@stopcode = 0) begin EXEC @retcode = msdb.dbo.sp_start_job @job_id=@job_id if @@ERROR<>0 GOTO UNDO end -- -- The last run of this job will be as scheduled -- END -- In case this was a PeerToPeer agent, delete all the cached PeerToPeer info DELETE FROM MScached_peer_lsns WHERE agent_id=@agent_id IF @@ERROR <> 0 GOTO UNDO IF @job_only = 0 BEGIN -- Remove agent entry DELETE MSdistribution_agents WHERE id = @agent_id IF @@ERROR <> 0 GOTO UNDO END -- Remove associated history DELETE MSdistribution_history WHERE agent_id = @agent_id IF @@ERROR <> 0 GOTO UNDO COMMIT TRAN RETURN(0) UNDO: if @@TRANCOUNT = 1 ROLLBACK TRAN else COMMIT TRAN return(1) END
No comments:
Post a Comment