SQL Server Reporting Services – Report schedule not deleted in SSRS 2005 and 2008

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s