Skip to main content
Barracuda MSP Partner Toolkit

Delete orphaned delivery schedules in ReportServer

The following query can be used against any SCData_xyz (Hosted or On-Prem) to purge any orphaned delivery schedules from the ReportServer database which are no longer in the Service Center UI.

You should always backup the ReportServer database before enabling the deletion in case any unexpected results or deletions occur.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

-- USE SCData_xyz;

        

DECLARE @Delete BIT = 0 -- CHANGE TO 1 FOR DELETION

 

-- STEP 1: PARSE THE VAR NAME FROM THE EXISTING DB_NAME

DECLARE @VarDomainName NVARCHAR(100)

SET @VarDomainName = (SELECT REPLACE(DB_NAME(),'SCData_',''))

        

-- STEP 2: LOOKUP THE VAR ID BY NAME IN SCMASTER

 

DECLARE @VarID INT

 

SET @VarID =

(

       SELECT

              VarID

       FROM

              SCMaster.dbo.VarDomain

       WHERE

              Name = @VarDomainName

);

 

IF @VarID IS NULL

       RAISERROR ('VarID was not found',16, 1);

 

-- STEP 2: GET SUBSCRIPTIONS FROM REPORTSERVER DB LINKED TO THIS VAR

 

WITH VarRSSubscriptionsCTE

AS

(

       SELECT        

              RS.SubscriptionID,

              (SELECT Name FROM ReportServer.dbo.Catalog WHERE ItemID = S.Report_OID) AS [Report Name],

              S.Description AS [Schedule Details],

              S.InactiveFlags,

              S.ModifiedDate,

              S.LastRunTime,

              S.LastStatus

       FROM

              ReportServer.dbo.Subscriptions S

       JOIN

              reportserver.dbo.ReportSchedule RS ON RS.SubscriptionID = S.SubscriptionID

       WHERE

              S.Report_OID

       IN

       (

              SELECT

                     ItemID

              FROM

                     ReportServer.dbo.Catalog

              WHERE

                     Path like '%/Managed Workplace/VARs/' convert(nvarchar(10), @VarID) + '/%'

              and

                     Type = 4       

       )

)

 

-- GET ACTIVE SUBSCRIPTIONID's FROM SCDATA INCLUDING REPORT POLICIES

 

, ActiveSubscriptionsCTE AS

(

       SELECT

              RPS.RSSubscriptionGuid,

              C.ComponentName AS ReportName

       FROM

              ReportPolicySubscription RPS

       JOIN

              ReportPolicyReportLink PRL ON PRL.TemplateID = RPS.TemplateID

       JOIN

              SCMaster.dbo.LpiComponent C ON C.ComponentGuid = PRL.ComponentGuid

 

       UNION

 

       SELECT

              RS.RSSubscriptionID AS RSSubscriptionGuid,

              DS.ReportName

       FROM

              RSDeliverySchedule DS

       JOIN 

              RSReportSubscription RS ON DS.DeliveryScheduleID = RS.DeliveryScheduleID

 

        JOIN 

              RSReportSubscriptionSubjectLink RSL on RSL.SubscriptionID = RS.SubscriptionID

        --comment out this section that filters deleted sites. this may do bad things if the schedules are still linked in SCData

        --LEFT JOIN 

       --       vw_SiteNotRejected S on S.SiteID = RSL.SiteID

)

 

--  I THINK ITS SAFE TO DELETE THESE RESULTS || OR SET InactiveFlags to 0 ??

 

SELECT

       S.SubscriptionID,

              (SELECT Name FROM ReportServer.dbo.Catalog WHERE ItemID = S.Report_OID) AS [Report Name],

              S.Description AS [Schedule Details],

              S.InactiveFlags,

              S.ModifiedDate,

              S.LastRunTime,

              S.LastStatus

INTO #RsOrphanedSubscriptions

FROM ReportServer.dbo.Subscriptions S

 

       -- FILTER REPORTS ONLY IN RSSubscriptions CTE

 

       WHERE  S.SubscriptionID IN

       (

              SELECT SubscriptionID FROM VarRSSubscriptionsCTE

       )

 

       -- FILTER OUT REPORTS IN ActiveSubscriptions CTE

 

       AND S.SubscriptionID NOT IN

       (

              SELECT RSSubscriptionGuid FROM ActiveSubscriptionsCTE

       )

 

SELECT FROM #RsOrphanedSubscriptions

 

-- STEP 3: IF DELETION IS ENABLED, DELETE THE SUBSCRIPTIONS

 

IF @Delete = 1

BEGIN

       DELETE FROM ReportServer.dbo.Subscriptions

       WHERE SubscriptionID IN

       (

              SELECT SubscriptionID FROM #RsOrphanedSubscriptions

       )

END

 

DROP TABLE #RsOrphanedSubscriptions

It is possible in some cases that a report may still exist for a deleted site in SCData, in these cases the above query will not clean up these orphans as they still technically exist in the Service Center.

To address this scenario, use the following query to delete the orphaned entries from SCData_ before cleaning up the ReportServer

1

2

3

4

5

6

7

8

9

10

with orphanRSSubscriptions as (

select rs.RSSubscriptionID, rsl.SubscriptionID, rsl.SiteID

from RSReportSubscription rs

left outer join RSReportSubscriptionSubjectLink rsl on rs.SubscriptionID = rsl.SubscriptionID

left outer join Site on rsl.SiteID = Site.SiteID

where Site.Status = 2

)

select from orphanRSSubscriptions

 

--delete from RSReportSubscription where RSReportSubscription.RSSubscriptionID in (select RSSubscriptionID from orphanRSSubscriptions)

  • Was this article helpful?