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.
- 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)