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