How to Troubleshoot Subscription issue in Reporting Services

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