Tips on troubleshooting Subscription issue in Reporting Services
1 – Look at the Reporting Services Log File
Error Message from Log File:
Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: Email Provider has no server or pickup directory specified, Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException: The report server has encountered a configuration error. ;
INFO: Notification 20f7b482-eb74-4b51-9e53-9f0609034e72 completed. Success: False, Status: Failure writing file Startup : The report server has encountered a configuration error. , DeliveryExtension: Report Server FileShare, Report: Startup,
…….
2 – Run below query against ReportServer Database and this query will give you Subscription job details which are scheduled and history of when it was last run
select
‘SubnDesc’ = s.Description,
‘SubnOwner’ = us.UserName,
‘LastStatus’ = s.LastStatus,
‘LastRun’ = s.LastRunTime,
‘ReportPath’ = c.Path,
‘ReportModifiedBy’ = uc.UserName,
‘ScheduleId’ = rs.ScheduleId,
‘SubscriptionId’ = s.SubscriptionID
from ReportServer.dbo.Subscriptions s
join ReportServer.dbo.Catalog c on c.ItemID = s.Report_OID
join ReportServer.dbo.ReportSchedule rs on rs.SubscriptionID = s.SubscriptionID
join ReportServer.dbo.Users uc on uc.UserID = c.ModifiedByID
join ReportServer.dbo.Users us on us.UserID = s.OwnerId
join msdb.dbo.sysjobs j on j.name = CONVERT(nvarchar(128),rs.ScheduleId)
–This query will help you to investigate further
select
‘Report’ = c.Path,
‘Subscription’ = s.Description,
‘SubscriptionOwner’ = uo.UserName,
‘SubscriptionModBy’ = um.UserName,
‘SubscriptionModDate’ = s.ModifiedDate,
‘ProcessStart’ = dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessStart),
‘NotificationEntered’ = dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.NotificationEntered),
‘ProcessAfter’ = dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessAfter),
n.Attempt,
‘SubscriptionLastRunTime’ = dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.SubscriptionLastRunTime),
n.IsDataDriven,
‘ProcessHeartbeat’ = dateadd(hh,DATEDIFF(hh,Getutcdate(),Getdate()),n.ProcessHeartbeat),
n.Version,
n.SubscriptionID
from Notifications n
join Subscriptions s on n.SubscriptionID = s.SubscriptionID
join Catalog c on c.ItemID = n.ReportID
join Users uo on uo.UserID = s.OwnerID
join Users um on um.UserID = s.ModifiedByID
