How do you delete old one time subscriptions?

  • 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

  • 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

  • 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

  • Anyone know how to find a one timed subscription?


    Mike

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

  • 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