dbo.Subscriptions.Description column length

  • I'm am trying to extract the email recipients of report subscriptions. To do this I'm getting the recipients from dbo.Subscriptions.Description but the recipients list cuts off the email addresses as if it is limited in character length. The email addresses do not exceed the character length that is designated for this column. The column data type is nvarchar(512), but it is cutting off around 55 characters. Is there a way around this?

  • The email values are in ExtensionSettings column.

  • Found it.

    select

    substring(SUBSTRING(ExtensionSettings,DATALENGTH('<ParameterValues><ParameterValue><Name>TO</Name><Value>')+1,DATALENGTH(ExtensionSettings)),1,CHARINDEX('</value>',SUBSTRING(ExtensionSettings,DATALENGTH('<ParameterValues><ParameterValue><Name>TO</Name><Value>')+1,DATALENGTH(ExtensionSettings)))-1)

    from Subscriptions

  • I am using this query when I need to inventory the subscriptions:

    SELECT

    Catalog.ItemID,

    Catalog.Path,

    Catalog.Name,

    Catalog.ParentID,

    Catalog.Type,

    Catalog.[Content],

    Catalog.Intermediate,

    Catalog.SnapshotDataID,

    Catalog.LinkSourceID,

    Catalog.Property,

    Catalog.Description,

    Catalog.Hidden,

    Catalog.CreatedByID,

    Catalog.CreationDate,

    Catalog.ModifiedByID,

    Catalog.ModifiedDate,

    Catalog.MimeType,

    Catalog.SnapshotLimit,

    Catalog.Parameter,

    Catalog.PolicyID,

    Catalog.PolicyRoot,

    Catalog.ExecutionFlag,

    Catalog.ExecutionTime,

    Subscriptions.SubscriptionID,

    Subscriptions.OwnerID,

    Subscriptions.Report_OID,

    Subscriptions.Locale,

    Subscriptions.InactiveFlags,

    Subscriptions.ExtensionSettings,

    Subscriptions.ModifiedByID AS SUBSCRIPT_ModifiedByID,

    Subscriptions.ModifiedDate AS SUBSCRIPT_ModifiedDate,

    Subscriptions.Description AS SUBSCRIPT_Description,

    Subscriptions.LastStatus,

    Subscriptions.EventType,

    Subscriptions.MatchData,

    Subscriptions.LastRunTime,

    Subscriptions.Parameters,

    Subscriptions.DataSettings,

    Subscriptions.DeliveryExtension,

    Subscriptions.Version

    FROM

    ReportServer.dbo.Catalog LEFT OUTER JOIN

    ReportServer.dbo.Subscriptions

    ON Catalog.ItemID = Subscriptions.Report_OID

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

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