SSRS Subscriptions Do Not Run, Sometimes

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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

The reason for this question is to try to determine what part of your system (database, report, scheduler, email) is failing.

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?


jat0818Author Commented:
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.

The timing of when they stop running then start again does not coincide with any sort of calendar events.
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.
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?
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.
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.
(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)

Hope this helps you find the problem

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jat0818Author Commented:
There is no SSIS package. There are no procedures that feed these reports.  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.

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.
"There is no SSIS package." --  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.

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 ...

jat0818Author Commented:
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.
Ask your DBA if the server has the MS12-070 patch installed. If so, check out this KB. The symptoms look similar. That's the nearest I can find.

Other than that, I'm drawing a blank too - I'm sorry.

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.

Also, like @DcpKing mentioned add auditing support. In your case, add auditing to your SSRS Report execution - see attached tutorial.
jat0818Author Commented:
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.
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.
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.
@jat0818 - Does this particular job utilize the same service account as the rest of your jobs?

Secondly, have you seen any messages related to, "delivery extension could not be loaded"?

The following MSDN blog contains troubleshooting steps for SSRS jobs using a trace log:

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?
jat0818Author Commented:
I was not able to solve the issue, but I did come up with a workaround. I run the job within a loop check the status. If the status is anything other than 'mail sent', it waits a few seconds and repeats. This is not pretty but it works.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sometimes a work-around is all you can do, because systems just aren't always as understandable as you'd wish.
Well done, Jat0818, for getting something that does what you need.
(I'm sorry I couldn't help more)

jat0818Author Commented:
Came up with my own workaround to the problem.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.