<div>
Just a request for clarification; what is your evidence that the reports do not run? Is it that the emails are not received, or is it something more fundamental ? For example, you might be using a stored procedure call to provide the report with data and be able to assess that the proc. was not called, implying that the didn't run to call it. <br />
<br />
The reason for this question is to try to determine what part of your system (database, report, scheduler, email) is failing.<br />
<br />
Further, you say that the reports run fine for months on end and then fail to produce. Is it possible that the failures happen in the Spring and also the Autumn? If so, could they possibly be associated with the time changes associated with Daylight Savings Time?<br />
<br />
hth<br />
<br />
Mike
</div>


<div>
The reports are run by SQL Agent jobs. One of the jobs is the one that was created by SSRS when the subscription was created. The other is the last step of another job, but it kicks off the job created by the subscription. I can see that the jobs all complete without errors, but no email is sent. I can also see in the SSRS logs, there is no mention that the report tried to run. Normally there would be a line saying that the report was run.<br />
<br />
The timing of when they stop running then start again does not coincide with any sort of calendar events.
</div>


<div>
Do they all stop and restart at about the same time? I've heard that embedding MHTML can encounter problems if you aren't very careful with the links - having spaces, apparently, can cause them to fail. However, you say that the SSRS log makes no mention of the report at all.<br />
I can guess that the reports have suddenly failed again and you're trying to see why. Will they run when started manually? Will they run when you open the SSIS job and manually execute just that last task in the package? For that matter, does that last task run at all on the occasions that the reports do not run? <br />
The problem with having only a lack of something is to have the patience to work back up the chain until you reach the place where you _do_ have something (that is working) and then examine the first failure point, which may not be the reports at all, in this case.<br />
I'm sorry that I can't magically pinpoint your problem, but the clues available to either of us are meagre: I'd suggest adding logging to your SSIS package in the form of calls to a procedure to write to a table, and similar calls within stored procedures in the package. Finally, also in the procedure that feeds data to the report (and if there isn't one, convert it - it'll be more easily maintained in the future). In this way you can hopefully gather enough information to determine just where something goes wrong, and what it is.<br />
(I'd recommend a LogTable with, at least, an int identity field, a datetime, a field holding the origin (procedure, package, etc), and 2 KB of varchar to write in what ever you want)<br />
<br />
Hope this helps you find the problem<br />
<br />
Mike
</div>


<div>
There is no SSIS package. There are no procedures that feed these reports. &nbsp;They pull from existing tables. One of them is executed directly from an SSRS subscription. The other is the last step of another job. It starts the agent job which was created by a subscription. I can't schedule the subscription because I do not exactly what time the previous steps of the job will complete.<br />
<br />
If I run the job steps manually, they usually work, but not always. I can see in the job history that the step does run and says successful but I can tell that the report did not run because there is nothing written to the log at that time.
</div>


<div>
&quot;There is no SSIS package.&quot; -- &nbsp;oops! I'm sorry. When you mentioned that the report was running as the last step in a job I had assumed that the job was something build with SSIS - my mistake.<br />
<br />
I'd suggest that you take at least one of the reports and convert the way it gets data from a direct pull from tables to a stored procedure that does that work, so that you can instrument the stored procedure with code to write out to a table details about the fact that it ran and what data it obtained. For example, you might discover that on the days it appears not to run no data was obtained, and some feature of your logic effectively stops the report when a certain piece of data is missing ...<br />
<br />
Mike
</div>


<div>
It would never be the case that these reports would have no data. If that were the case, it would probably mean that we went out of business and nobody told me. In testing, running and finding no data would still generate a report with page headers, footers, etc but no data. I can go into the dataset within one or both of the reports and insert logging before and after the data is pulled. By the way, the last two days, one of them ran as scheduled and I had to manually run the other one.
</div>


<div>
Ask your DBA if the server has the MS12-070 patch installed. If so, check out <a href="https://blogs.msdn.microsoft.com/mariae/2012/11/12/reporting-services-service-doesnt-start-after-the-installation-of-ms12-070-security-patch/" rel="ugc">this KB</a>. The symptoms look similar. That's the nearest I can find.<br />
<br />
Other than that, I'm drawing a blank too - I'm sorry.<br />
<br />
Mike
</div>


<div>
Hi @jat0818 - Have you taken a look at how your SQL Services are set to startup? I've encountered situations where the services didn't start or were delayed. Sometimes Microsoft updates can impact the scheduling if a server reboot occurs and the SQL services don't start properly. <br />
<br />
Also, like @DcpKing mentioned add auditing support. In your case, add auditing to your SSRS Report execution - see attached tutorial.<br />
<a href="https://filedb.experts-exchange.com/incoming/2016/07_w30/1108088/How-to-Audit-Report-Execution-in-SSR.pdf" target="_blank" class="file-inline" title="How to add auditing to SSRS Report Execution (705 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>How-to-Audit-Report-Execution-in-SSR.pdf</a>
</div>


How to add auditing to SSRS Report Execution

How-to-Audit-Report-Execution-in-SSR.pdf

<div>
It can't be related to the services not being started. We have reports that are kicked off by other processes all day/night long. If it were a service issue, we would know very quickly because those reports would not run either.<br />
As far as logging, I have those options enabled. In the ExecutionLogStorage table, I see the same thing as in the log file. If the report ran ok, there is an entry in this table. If it did not run, there is nothing written to it.<br />
I am going to test the 'Include Link' option rather than 'Include Report', however I don't know if that will meet the user requirements as some of the recipients of the email are outside the company.
</div>


<div>
@jat0818 - Does this particular job utilize the same service account as the rest of your jobs? <br />
<br />
Secondly, have you seen any messages related to, &quot;delivery extension could not be loaded&quot;?<br />
<br />
The following MSDN blog contains troubleshooting steps for SSRS jobs using a trace log:<br />
<a href="https://blogs.msdn.microsoft.com/deanka/2010/02/15/troubleshooting-subscriptions-part-ii-using-the-reporting-services-trace-log-file/" rel="ugc">https://blogs.msdn.microsoft.com/deanka/2010/02/15/troubleshooting-subscriptions-part-ii-using-the-reporting-services-trace-log-file/</a><br />
<br />
Oh, and what about report format... Does the intermittently failing job provide the same output format as the jobs that don't have a problem?
</div>


<div>
Sometimes a work-around is all you can do, because systems just aren't always as understandable as you'd wish.<br />
Well done, Jat0818, for getting something that does what you need. <br />
(I'm sorry I couldn't help more)<br />
<br />
Mike
</div>


<div>
Came up with my own workaround to the problem.
</div>


<div>
<div class="content wysiwyg-content">
In SSRS 2008, we have some reports that are scheduled to run automatically. One of them has a subscription which runs at 5:00am every day. The other is executed from the last step of a sqlagent job. They both run as they are supposed to for months on end. Then they will sometimes not run. This lasts for a few days, then they start running properly again. &nbsp;Sometimes, stopping the SSRS service and starting it again helps but not always. They both embed the report into the email and are using MHTML. I think I have seen that subscriptions are more reliable when attaching the report as a PDF instead of embedding it, but that is not really an option with these.<br />
So my question is simply, Why do they stop working for a few days then start again on their own. &nbsp;The server did not get rebooted at any point during troubleshooting this.
</div>
</div>


In SSRS 2008, we have some reports that are scheduled to run automatically. One of them has a subscription which runs at 5:00am every day. The other is executed from the last step of a sqlagent job. They both run as they are supposed to for months on end. Then they will sometimes not run. This lasts for a few days, then they start running properly again.  Sometimes, stopping the SSRS service and starting it again helps but not always. They both embed the report into the email and are using MHTML. I think I have seen that subscriptions are more reliable when attaching the report as a PDF instead of embedding it, but that is not really an option with these.
So my question is simply, Why do they stop working for a few days then start again on their own.  The server did not get rebooted at any point during troubleshooting this.

SSRS Subscriptions Do Not Run, Sometimes

SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It can prepare and deliver a variety of interactive and printed reports and is administered via a web interface or its web service. Its web services interface also supports the development of custom reporting applications. Finally, SSRS can also be integrated with SharePoint. SSRS competes with other business intelligence tools, most prominently Crystal Reports.

SSRS

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

Microsoft SQL Server

Microsoft SQL Server 2008 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. Major improvements include the  Always On technologies and support for unstructured data types.