CREATE PROCEDURE [dbo].[sp_syscollector_delete_jobs]
@collection_job_id uniqueidentifier,
@upload_job_id uniqueidentifier,
@schedule_id int = NULL,
@collection_mode smallint
AS
BEGIN
-- delete the jobs corresponding to the collection set
DECLARE @TranCounter INT
SET @TranCounter = @@TRANCOUNT
IF (@TranCounter > 0)
SAVE TRANSACTION tran_syscollector_delete_jobs
ELSE
BEGIN TRANSACTION
BEGIN TRY
IF (@collection_mode = 1) -- non-cached mode
BEGIN
IF (@upload_job_id IS NOT NULL)
BEGIN
-- note, upload job id = collection job id in this mode
IF (@schedule_id IS NOT NULL)
BEGIN
EXEC dbo.sp_detach_schedule
@job_id = @upload_job_id,
@schedule_id = @schedule_id,
@delete_unused_schedule = 0
END
EXEC dbo.sp_delete_jobserver
@job_id = @upload_job_id,
@server_name = N'(local)'
EXEC dbo.sp_delete_job
@job_id = @upload_job_id
END
END
ELSE -- cached mode
BEGIN
-- detach schedules, delete job servers, then delete jobs
IF (@upload_job_id IS NOT NULL)
BEGIN
EXEC dbo.sp_detach_schedule
@job_id = @upload_job_id,
@schedule_id = @schedule_id,
@delete_unused_schedule = 0
EXEC dbo.sp_delete_jobserver
@job_id = @upload_job_id,
@server_name = N'(local)'
EXEC dbo.sp_delete_job
@job_id = @upload_job_id
END
IF (@collection_job_id IS NOT NULL)
BEGIN
EXEC dbo.sp_detach_schedule
@job_id = @collection_job_id,
@schedule_name = N'RunAsSQLAgentServiceStartSchedule',
@delete_unused_schedule = 0
EXEC dbo.sp_delete_jobserver
@job_id = @collection_job_id,
@server_name = N'(local)'
EXEC dbo.sp_delete_job
@job_id = @collection_job_id
END
END
IF (@TranCounter = 0)
COMMIT TRANSACTION
RETURN (0)
END TRY
BEGIN CATCH
IF (@TranCounter = 0 OR XACT_STATE() = -1)
ROLLBACK TRANSACTION
ELSE IF (XACT_STATE() = 1)
ROLLBACK TRANSACTION tran_syscollector_delete_jobs
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorNumber INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure NVARCHAR(200);
SELECT @ErrorLine = ERROR_LINE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorMessage = ERROR_MESSAGE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');
RAISERROR (14684, @ErrorSeverity, -1 , @ErrorNumber, @ErrorSeverity, @ErrorState, @ErrorProcedure, @ErrorLine, @ErrorMessage);
RETURN (1)
END CATCH
END