CODEWITHSUNDEEP
sql-server-2008reporting-servicesssrs-2008-r2
samalkobi
samalkobi

Reputation: 77

SSRS scheduled reports not executing -

We have a number of reports scheduled to run throughout the week. Some reports are also setup as Agent Job reports and use SP_send_dbmail to the send the report in HTML format. For the last week or so, multiple reports have failed to send, while others are going through in the same day. IT's been an intermitten issue as some days all reports seem to go through, but i don't have any alerts to notify me of failed reports.

I looked through the log files for the reportserver from \Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\LogFiles ; This error messages comes up frequently:

ERROR: PollingMaintenance: Restarting maintenance thread for the following exception:  This method or property cannot be called on Null values.
   at System.Data.SqlClient.SqlBuffer.get_String()
   at Microsoft.ReportingServices.Library.InstrumentedSqlDataReader.<>c__DisplayClass3d.<GetString>b__3c()
   at Microsoft.ReportingServices.Library.SqlBoundaryWithReturn`1.Invoke(Method m)
   at Microsoft.ReportingServices.Library.EventQueueWorker.GetNextQueueItem(IDataRecord record)
   at Microsoft.ReportingServices.Library.QueuePollWorker.ProcessData(IDataReader reader)
   at Microsoft.ReportingServices.Library.DBPoll.PollingFunction()
   at Microsoft.ReportingServices.Library.DBPoll.PollingMaintenance().
library!WindowsService_827!8d4!07/10/2015-05:54:19:: i INFO: PollingMaintenance: Polling cycle completed.

Other than that, i'm not seeing any useful info in the ExecutionLogStorage table under LastStatus ( just says rsProcessAborted on a few records).

I also tried running this query to look for failed reports, but the ones that failed today aren't coming up:

SELECT
    c.Name AS [ReportName],
    sb.[Description] AS [SubscriptionDescription],
    sb.DeliveryExtension AS [DeliveryType],
    sb.LastStatus AS [LastRunStatus],
    sb.LastRunTime AS [LastRunTime],
    c.Path AS [ReportPath],
    'http://sql-server/Reports/Pages/Report.aspx?ItemPath='+REPLACE(REPLACE(C.[Path],'/','%2f'),' ','+')+'&SelectedTabId=PropertiesTab&ViewMode=List&SelectedSubTabId=SubscriptionsTab' AS [SubscriptionLink],
    sc.ScheduleID AS [SQLAgentJobName],
    sb.SubscriptionID
FROM
    ReportServer.dbo.ReportSchedule AS RS
    INNER JOIN ReportServer.dbo.Schedule sc ON rs.ScheduleID = sc.ScheduleID
    INNER JOIN ReportServer.dbo.Subscriptions sb ON rs.SubscriptionID = sb.SubscriptionID
    INNER JOIN ReportServer.dbo.[Catalog] c ON rs.ReportID = c.ItemID AND sb.Report_OID = c.ItemID
WHERE
    (sb.LastStatus LIKE 'Failure%' OR sb.LastStatus LIKE 'Error%' OR sb.LastStatus LIKE '%not valid%')
    order by lastruntime asc

Upvotes: 0

Views: 7011

Answers (1)

samalkobi
samalkobi

Reputation: 77

This is now resolved; This required more digging into the events table and we found an Agent job that was scheduled to update an old subscription on a report. Said report was causing issues over month ago, and i deleted the subscription and created a new report using the same query. The job updating the report seemed to be queuing events in the Events table, and they kept timing out as there was no subscription for that report. We cleared out those events the other day and only a few were remaining, but all reports have been going out on schedule. The links i posted in my last comment above cover the main areas that pointed us in the right direction.

Upvotes: 1

Related Questions