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
|
|
|
|
|