June 22, 2016 at 3:08 am
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.
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply