POWERBI Email Subscription

user_19584284 0
Viewed 31

Question: Anyone knows what triggers the sending of email for PowerBI Report Server Subscription service?

I am trying to see if any table (or column) in the Report Server Database itself triggers or handles the set schedule to fire the sending of a report.

power-bi-report-server ssrs-2012 reporting-services
1 Answers

They should all be jobs on the database server. The job name is in the ReportSchedule table.

SSMS Screenshot

enter image description here

Example SQL

SELECT
     [Job_Id] = rs.[ScheduleID]
   , c.[ItemID]
   , c.[CreatedById]
   , c.[ModifiedById]
   , c.[Type]
   , c.[Name]
   , c.[Description]
   , c.[Parameter]
   , [ReportCreationDate] = c.[CreationDate]
   , [ReportModifiedDate] = c.[ModifiedDate]
   , [ReportFolder] =
      CASE
         WHEN c.Path = '/' + c.Name THEN ''
         ELSE SUBSTRING(c.[Path], 2, Len(c.[Path])-Len(c.[Name])-2)
      END
   , [ReportPath] = c.[Path]
   , s.[SubscriptionID]
   , s.[Report_OID]
   , [SubscriptionDescription] = s.[Description]
   , s.[ExtensionSettings]
   , s.[EventType]
   , s.[OwnerID]
   , s.[ModifiedByID]
   , s.[ModifiedDate]
   , [RunTime] = CONVERT(VARCHAR(5), s.[LastRunTime], 8)
   , [LastRunDate] = CONVERT(VARCHAR(11), s.[LastRunTime], 13)
   , [LastRunTime] = CAST(CONVERT(CHAR(16), s.[LastRunTime], 113) AS DATETIME)
   , [SubscriptionType] = CASE WHEN CAST(s.[DataSettings] AS NVARCHAR(50)) IS NOT NULL THEN 'Data-Driven' ELSE 'Standard' END
   , s.[DeliveryExtension]
   , s.[MatchData]
   , [SubscriptionLastStatus] = s.[LastStatus]
   , [StatusFail] = CASE WHEN s.[LastStatus] LIKE '%Mail sent%' THEN 'N' ELSE 'Y' END
   , [EmailSubject] = CASE CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>SUBJECT</Name><Value>') + CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>SUBJECT</Name><Value>') + CHARINDEX('<Name>SUBJECT</Name><Value>', s.ExtensionSettings))) END
   , [EmailTo] = SUBSTRING(s.ExtensionSettings, LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>TO</Name><Value>') + CHARINDEX('<Name>TO</Name><Value>', s.ExtensionSettings)))
   , [EmailCc] = CASE CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>CC</Name><Value>') + CHARINDEX('<Name>CC</Name><Value>', s.ExtensionSettings))) END
   , [EmailBcc] = CASE CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>BCC</Name><Value>') + CHARINDEX('<Name>BCC</Name><Value>', s.ExtensionSettings))) END
   , [EmailComment] = CASE CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>Comment</Name><Value>') + CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>Comment</Name><Value>') + CHARINDEX('<Name>Comment</Name><Value>', s.ExtensionSettings))) END
   , [EmailIncludeLink] = CASE CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>IncludeLink</Name><Value>') + CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>IncludeLink</Name><Value>') + CHARINDEX('<Name>IncludeLink</Name><Value>', s.ExtensionSettings))) END
   , [EmailRenderFormat] = CASE CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>RenderFormat</Name><Value>') + CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>RenderFormat</Name><Value>') + CHARINDEX('<Name>RenderFormat</Name><Value>', s.ExtensionSettings))) END
   , [EmailPriority] = CASE CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings) WHEN 0 THEN '' ELSE SUBSTRING(s.ExtensionSettings, LEN('<Name>Priority</Name><Value>') + CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings), CHARINDEX('</Value>', s.ExtensionSettings, CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings) + 1) - (LEN('<Name>Priority</Name><Value>') + CHARINDEX('<Name>Priority</Name><Value>', s.ExtensionSettings))) END
   , s.[LastStatus]
FROM
   [dbo].[Subscriptions] AS s WITH (NOLOCK) 
   LEFT JOIN [dbo].[Catalog] AS c WITH (NOLOCK) ON s.[Report_OID] = c.[ItemID]
   LEFT JOIN [dbo].[ReportSchedule] AS rs WITH (NOLOCK) ON s.[SubscriptionID] = rs.[SubscriptionID];

I also have an example report that shows all the subscriptions.

enter image description here

user_90594240
Related