Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do you delete old one time subscriptions? Expand / Collapse
Author
Message
Posted Thursday, October 3, 2013 8:16 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:16 AM
Points: 10, Visits: 366
Am using the following query, to get the subscriptions I want to delete.

SELECT
C.Name
,C.Path
,U.UserName
,S.InactiveFlags
,C.CreationDate
FROM ReportServer.dbo.Subscriptions S
INNER JOIN ReportServer.dbo.Catalog C
ON S.Report_OID = C.ItemID
INNER JOIN ReportServer.dbo.Users U
ON S.OwnerID = U.UserID
WHERE path like '/XXXXXX/%' and CreationDate <'10/01/2013'
ORDER BY LastRunTime DESC

This gives me the list of subscriptions that I want to delete.

Am unsure of the syntax to delete these subscriptions.
Any help is greatly appreciated.

Thanks,
Mike





Mike
Post #1501213
Posted Thursday, October 3, 2013 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:16 AM
Points: 10, Visits: 366
I replaced the select with a delete, it is not working.
delete
C.Name
,C.Path
,U.UserName
,S.InactiveFlags
,C.CreationDate
FROM ReportServer.dbo.Subscriptions S
INNER JOIN ReportServer.dbo.Catalog C
ON S.Report_OID = C.ItemID
INNER JOIN ReportServer.dbo.Users U
ON S.OwnerID = U.UserID
WHERE path like '/XXXXXX/%' and CreationDate <'10/01/2013'

Any helps appreciated.
Mike



Mike
Post #1501220
Posted Thursday, October 3, 2013 8:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:16 AM
Points: 10, Visits: 366
I think i figure it out.
Delete
FROM ReportServer.dbo.Subscriptions
FROM ReportServer.dbo.Subscriptions S
INNER JOIN ReportServer.dbo.Catalog C
ON S.Report_OID = C.ItemID
INNER JOIN ReportServer.dbo.Users U
ON S.OwnerID = U.UserID
WHERE path like '/XXXXXX/%' and CreationDate <'10/01/2013'

If someone has a better solution or suggestion, please reply.
THanks
Mike



Mike
Post #1501222
Posted Thursday, October 3, 2013 10:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:16 AM
Points: 10, Visits: 366
Anyone know how to find a one timed subscription?


Mike
Post #1501277
Posted Tuesday, October 22, 2013 9:41 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: 2 days ago @ 7:01 AM
Points: 81, Visits: 676
Mike,

Before we start I do not advocate deleting directly from the ReportServer and your posts scared me a little.

Can I ask why you are not using the report manager for this?

If you want to list the run once reports I've amended your SQL above.

SELECT
s.SubscriptionID
, C.Name
,C.Path
,U.UserName
,S.InactiveFlags
,C.CreationDate
, sc.*
FROM dbo.Subscriptions S
INNER JOIN dbo.Catalog C
ON S.Report_OID = C.ItemID
INNER JOIN dbo.Users U
ON S.OwnerID = U.UserID
INNER JOIN ReportSchedule r
ON s.SubscriptionID = r.SubscriptionID
INNER JOIN Schedule sc on r.ScheduleID = sc.ScheduleID
WHERE RecurrenceType = 1
ORDER BY LastRunTime DESC

If you must do it in SQL then to delete the subscriptions use:

exec DeleteSubscription @SubscriptionID = 'GUID-GUID-GUID-GUID'

The trigger on the Subscriptions table removes the SQL Agent job etc.

OK DeleteSubscription is actually just:

DELETE FROM [Subcriptions] WHERE Subscription_ID = @Subscription_ID

but it's usually good practice to use the SP since there may be more it does.

Post #1507223
Posted Tuesday, October 22, 2013 9:48 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, July 18, 2014 7:16 AM
Points: 10, Visits: 366
From what i recall of the issue, there were couple hundred that needed deleted.
I appreciate your help and your comments.
Thank,
Mike



Mike
Post #1507225
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse