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
 
 
 
 

Comments

Post a Comment

Popular posts from this blog

Calculating Age of the person in T-SQL

One of common calculations, we have to do in ETL is calculating age of the person from the date attribute And this is how I have calculated Age of the person. Select BirthDate , CASE             WHEN Month ( GetDate ()) < Month ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             WHEN Month ( GetDate ()) = Month ( c . [BirthDate] )             AND Day ( GetDate ()) < Day ( c . [BirthDate] )                 THEN DateDiff ( yy , c . [BirthDate] , GetDate ()) - 1             ELSE DateDiff ( yy , c . [BirthDate] , GetDate ())         END AS [Age]               from [dbo] . [DimCustomer] c               order by BirthDate I am sure, there would be other better ways to do this and would love to hear, of how you have calculated age of the person
Read more

MDX Queries - Current Day - Month and Year

Problem: Query to get Current Day - Month and Year in MDX /* MDX for retrieving current Year */ WITH MEMBER [Measures].[Current Year] AS     VBAMDX. Format (VBAMDX.Now(), "yyyy" ) SELECT   {[Measures].[Current Year]} ON COLUMNS FROM [Analysis Services Tutorial]; /* Current Month */ WITH MEMBER [Measures].[Current Month] AS     VBAMDX. Format (VBAMDX.Now(), "MM" ) SELECT   {[Measures].[Current Month]} ON COLUMNS FROM [Analysis Services Tutorial]; /* MDX for retrieving current Date */ WITH MEMBER [Measures].[Current Date] AS     VBAMDX. Format (VBAMDX.Now(), "dd" ) SELECT   {[Measures].[Current Date]} ON COLUMNS FROM [Analysis Services Tutorial];
Read more