Post reply

Delete an email from SSRS subscriptions

  • July 6, 2012 at 7:47 am

    #271996

    Hi,

    Does anyone have a script to remove an email address from report subscriptions?

    I usually do this from report manager, but there are a few emails that needs to be removed from a bunch of reports. Using script will be much easier.

    Thanks!

  • July 9, 2012 at 3:02 am

    #1510218

    SELECT

    U.UserName AS SubscriptionOwner,

    C.Path AS ReportPath,

    CONVERT(XML,S.ExtensionSettings) AS SubscriptionSettings,

    S.EventType AS SubscriptionType,

    CONVERT(XML,S.Parameters) AS ReportParameters,

    S.DeliveryExtension AS DeliveryMethod,

    Sc.Name AS ScheduleName,

    Rs.ScheduleID

    FROM

    Subscriptions S

    INNER JOIN

    Users U

    ON

    S.OwnerID = U.UserID

    INNER JOIN

    Catalog C

    ON

    S.Report_OID = C.ItemID

    INNER JOIN

    ReportSchedule RS

    ON

    S.SubscriptionID = RS.SubscriptionID

    INNER JOIN

    Schedule Sc

    ON

    RS.ScheduleID = Sc.ScheduleID

    ORDER BY

    1,2

    This will give you the basis, but as ExtensionSettings is NTEXT you cant use text comparrisions (hence conversion to XML, so that it is easily readable), so I would always say do it in the front end, unless you can strip out what you want from a conversion then update the row.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply