create procedure sys.sp_MSdrop_qreader_agent
(
@agent_id int = NULL
) AS
BEGIN
SET NOCOUNT ON
-- Declarations.
DECLARE @retcode int
DECLARE @job_id binary(16)
DECLARE @job_step_uid uniqueidentifier
DECLARE @name nvarchar(100)
DECLARE @database sysname
select @database = db_name()
IF @agent_id is NULL
BEGIN
SELECT TOP 1 @agent_id = id
FROM MSqreader_agents
END
-- get the agent and job ids
SELECT @job_id = job_id, @name = name, @job_step_uid = job_step_uid
FROM MSqreader_agents
WHERE id = @agent_id
if (@agent_id is NULL)
return (0)
-- Delete Perfmon instance
dbcc deleteinstance ('SQL Replication QueueReader', @name)
BEGIN TRAN sp_MSdrop_qreader_agent
-- delete job for the agent
IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view WHERE job_id = @job_id)
BEGIN
-- Checks if the job name was generated
EXEC @retcode = sys.sp_MSisqueuereaderjobnamegenerated
@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
-- Remove agent entry
DELETE MSqreader_agents WHERE id = @agent_id
IF (@@ERROR != 0)
GOTO UNDO
-- Remove associated history
DELETE MSqreader_history
WHERE agent_id = @agent_id
IF (@@ERROR != 0)
GOTO UNDO
COMMIT TRAN sp_MSdrop_qreader_agent
RETURN(0)
UNDO:
ROLLBACK TRAN sp_MSdrop_qreader_agent
return(1)
END