August 5, 2011 at 8:52 am
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?
August 5, 2011 at 12:49 pm
The email values are in ExtensionSettings column.
August 8, 2011 at 7:24 am
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
August 8, 2011 at 7:41 am
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply