info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance



Execute SSRS Report Subscriptions manually / on-demand
by BF (Principal Consultant; Architecture; Engineering)
2018-06-04








Solution:


Step 1:

USE [ReportServer]
GO

Declare @SubscriptionOwner nvarchar(max)
Set @SubscriptionOwner = ''

SELECT
SUB.SubscriptionID
,USR.UserName AS SubscriptionOwner
,CAT.[Path] AS ReportPath
,SUB.ModifiedDate
,SUB.[Description]
,SUB.EventType
,SUB.DeliveryExtension
,SUB.LastStatus
,SUB.LastRunTime
,SCH.NextRunTime
,SCH.Name AS ScheduleName
,CAT.[Description] AS ReportDescription
INTO #TMP
FROM dbo.Subscriptions AS SUB
INNER JOIN dbo.Users AS USR
ON SUB.OwnerID = USR.UserID
INNER JOIN dbo.[Catalog] AS CAT
ON SUB.Report_OID = CAT.ItemID
INNER JOIN dbo.ReportSchedule AS RS
ON SUB.Report_OID = RS.ReportID
AND SUB.SubscriptionID = RS.SubscriptionID
INNER JOIN dbo.Schedule AS SCH
ON RS.ScheduleID = SCH.ScheduleID
where (@SubscriptionOwner = '' or USR.UserName = @SubscriptionOwner)
--and CAT.[Path] like '%%'
ORDER BY LastRunTime desc

SELECT 'Debug Information: Subscriptions.....' as 'Message'
SELECT * from #TMP order by LastRunTime desc

--SELECT 'Debug Information: Schedules.....' as 'Message'
--SELECT ScheduleID, * FROM ReportSchedule where SubscriptionID in (Select SubscriptionID from #TMP)

SELECT 'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'',@EventData=' + '''' + convert(nvarchar(150),SubscriptionID) + '''' as 'Execute Batch.....'
FROM ReportSchedule where SubscriptionID in (Select SubscriptionID from #TMP)

DROP TABLE #TMP


Step 2:

Copy the Execute Batch scripts from above and run it against the ReportServer database


Helper T-SQL:

Select DateAdd(HOUR,-4,TimeEntered) as 'TimeEntered-EST', * from dbo.Event

select top 10 ItemPath, TimeStart as 'Time-Start', TimeEnd, TimeDataRetrieval,
TimeProcessing, TimeRendering, [Source], [Status], ByteCount, [RowCount], [Format]
from dbo.ExecutionLog3 order by TimeStart DESC

Select top 5 * from dbo.ExecutionLog order by TimeStart desc

Select top 5 * from dbo.ExecutionLog2 order by TimeStart desc

SELECT n.SubscriptionID, c.Name as ReportName, c.Path as ReportPath, u.UserName as SubscriptionOwner, n.ExtensionSettings,
n.NotificationEntered as QueuedSinceTime, n.ProcessAfter, n.SubscriptionLastRunTime, n.DeliveryExtension
from dbo.Notifications n with (nolock)
inner join dbo.Catalog c with (nolock) on n.ReportID = c.ItemID
inner join dbo.Users u with (nolock) on n.SubscriptionOwnerID = u.UserID
WHERE n.ProcessStart is NULL and (n.ProcessAfter is NULL or n.ProcessAfter < GETUTCDATE())
ORDER BY n.NotificationEntered

Select
s.[SubscriptionID],
cat.[Path],
s.[DeliveryExtension],
s.[EventType],
Owner.[UserName] 'Owner',
s.[LastRunTime],
Modified.[UserName] 'Modified By',
s.[ModifiedDate],
s.[Description],
s.[LastStatus]
From
dbo.Subscriptions [s]
inner join dbo.[Catalog] [cat] on s.[Report_OID] = cat.[ItemID]
inner join dbo.[Users] [Owner] on s.OwnerID = Owner.UserID
inner join dbo.[Users] [Modified] on s.ModifiedByID = Modified.UserID
order by LastRunTime desc

SELECT C.Name, S.LastRunTime, S.LastStatus, S.Description,
C.Path, LastStatus
FROM Subscriptions AS S
LEFT OUTER JOIN [Catalog] AS C
ON C.ItemID = S.Report_OID
WHERE LEFT (S.LastStatus, 12) != 'Mail sent to'
AND LEFT (S.LastStatus, 12) != 'New Subscrip'
order by LastRunTime desc

select * from ConfigurationInfo

Select * from dbo.Keys


If Reports are not being delivered:

Check Status Tables Above

Check SSRS Log: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles\

Check Report email reciepent address