x

Bug in SSRS Database (SSRS 2005 & SSRS 2008)?

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 [dbo].[Schedule].

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?

more ▼

asked Dec 18, 2009 at 02:31 PM in Default

Pavel Pawlowski gravatar image

Pavel Pawlowski
22.3k 9 11 21

(comments are locked)
10|1200 characters needed characters left

1 answer: sort oldest

It looks like a bug to me - apparently they just didn't think of that use case. I would report it via connect.

more ▼

answered Dec 18, 2009 at 04:38 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

It seems so.. I've posted this on SQL Server Connect.
Dec 18, 2009 at 05:05 PM Pavel Pawlowski
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1945
x1834
x547

asked: Dec 18, 2009 at 02:31 PM

Seen: 2182 times

Last Updated: Dec 18, 2009 at 07:00 PM