October 3, 2013 at 8:16 am
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
October 3, 2013 at 8:29 am
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
October 3, 2013 at 8:35 am
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
October 3, 2013 at 10:55 am
Anyone know how to find a one timed subscription?
Mike
October 22, 2013 at 9:41 am
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.
October 22, 2013 at 9:48 am
From what i recall of the issue, there were couple hundred that needed deleted.
I appreciate your help and your comments.
Thank,
Mike
Mike
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply