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)
Best Answer
I've been having the same problem for the last few weeks and had settled on manual deletes. I think I've cracked it today. I've compared the foreign key constraint [FK_ReportSchedule_Subscriptions] on the table Subscriptions to the same on a clean (working) install of ssrs. On my clean install the constraint is set with nocheck