If you delete a job which handles particular SSRS schedule by mistake, then it can happen, that you will be unable to delete a report schedule.
You delete the schedule and when you refresh the management the schedule you wanted to delete appears again.
This is caused by a trigger [dbo].[Schedule_DeleteAgentJob] trigger on the [dbo].[Schedule] table in the report server database, which doesn’t check for the job if it exists before it tries to delete it. And therefore if you deleted the job by mistake, then there is raised an exception in the trigger and whole transaction with deletion of schedule is rolled back.
You can simply modify the trigger by adding additional check for the existence of the job. And once the modification is applied, your SSRS schedules will work even you delete the job for that schedule manually.
ALTER TRIGGER [dbo].[Schedule_DeleteAgentJob] ON [dbo].[Schedule] AFTER DELETE AS DECLARE id_cursor CURSOR FOR SELECT ScheduleID from deleted OPEN id_cursor DECLARE @next_id uniqueidentifier FETCH NEXT FROM id_cursor INTO @next_id WHILE (@@FETCH_STATUS <> -1) -- -1 == FETCH statement failed or the row was beyond the result set. BEGIN if (@@FETCH_STATUS <> -2) -- - 2 == Row fetched is missing. BEGIN -- Check that job exists before trying to delete it. IF (EXISTS(SELECT 1 FROM msdb.dbo.sysjobs WHERE name = CONVERT(nvarchar(128), @next_id))) exec msdb.dbo.sp_delete_job @job_name = @next_id -- delete the schedule END FETCH NEXT FROM id_cursor INTO @next_id END CLOSE id_cursor
Both SSRS 2005 and SSRS 2008 have the same problem.
I’m mentioning this also in my previous blog item about scale-out deployment of SSRS on Standard edition of SQL Server.