SSRS 2005 uses a Agent for the schedules of reports subscriptions. For each subscription there is one Job (it's name is GUID of the schedule) created and scheduled and this job simply inserts an event in the reporting services database which is periodically checked by the SSRS service process.
Unfortunatelly, when you delete this job by mistake, then you will not be able do delete particular schedule using the management studio or reporting services pages.
The problem is caused by a trigger
[dbo].[Schedule_DeleteAgentJob] on the table
This trigger simply tries to delete a job using
sp_delete_job, unfortunatelly the trigger doesn't check for the existence of the job and if it was deleted manualy prior deleting the schedule, it raise exception and the delete operation is rolled back.
The original trigger looks like:
CREATE 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 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 DEALLOCATE id_cursor
Shouldn't be there a check for the existence of the trigger?
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
I have encountered the problem when moving the SSRS database from one SQL server instnce to another as on the new instance you do not have the jobs for schedules stred in the SSRS database. Solved the issue by adding the check.
The same code and the same problem exists not only in SSRS 2005 but also 2008 (didn't checked 2000). Do you think it is a bug in the reporting services?