Report subscription deletion failed due to FK constraint

• roni.vered

  SSC Veteran

  Points: 274

  More actions

  June 22, 2016 at 3:08 am

  #308722

  Hi all,

  We have many SSRS report timed subscriptions (SQL Server 2008R2 SP4).

  One of our report developers tried to delete a report subscription, and got the following error:

  An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database. (rsReportServerDatabaseError) Get Online Help The DELETE statement conflicted with the REFERENCE constraint "FK_ReportSchedule_Subscriptions". The conflict occurred in database "ReportServer", table "dbo.ReportSchedule", column 'SubscriptionID'. The statement has been terminated.

  I solved the issue by manually deleting the schedule record from the ReportServer.dbo.ReportSchedule table.

  The steps were the following:

  1. Find the relevant subscription that needs to be deleted.

  SELECT sub.SubscriptionID,

  rs.ScheduleID,

  SUB.[Description]

  ,SUB.EventType

  ,SUB.DeliveryExtension

  ,SUB.LastRunTime

  FROM dbo.Subscriptions AS SUB

  INNER JOIN dbo.Users AS USR ON SUB.OwnerID = USR.UserID

  INNER JOIN dbo.ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID AND SUB.SubscriptionID = RS.SubscriptionID

  WHERE SUB.Description like '%Mail reciepient here%'

  ORDER BY sub.Description

  2. Find the relevant schedule that prevents the record from being deleted.

  SELECT [ScheduleID]

  ,[ReportID]

  ,[SubscriptionID]

  ,[ReportAction]

  FROM [ReportServer].[dbo].[ReportSchedule]

  WHERE SubscriptionID = 'GUID HERE'

  3. Backup the table

  SELECT *

  INTO [ReportServer].[dbo].[ReportScheduleBackup]

  FROM [ReportServer].[dbo].[ReportSchedule]

  4. Delete the schedule

  DELETE FROM [ReportServer].[dbo].[ReportSchedule]

  WHERE ScheduleID ='GUID HERE'

  The delete command deleted the schedule AND deleted the job from SQL instance that holds the RS databases. That job triggered the timed subscription.

  After that, I could delete the report timed subscription from the reports web GUI.

  My questions -

  - Why a manual deletion was needed in the first place ?

  - Why couldn't I delete the report subscription form the RS GUI?

  http://REPORTSERVER_NAME/Reports/Pages/Subscriptions.aspx)">(http://REPORTSERVER_NAME/Reports/Pages/Subscriptions.aspx)

  Thanks,

  Roni.