Solved

SSRS Subscriptions Do Not Run, Sometimes

Posted on 2016-07-18
13
43 Views
Last Modified: 2016-08-02
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.
0
Comment
Question by:jat0818
  • 6
  • 5
  • 2
13 Comments
 
LVL 16

Expert Comment

by:DcpKing
ID: 41719036
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?

hth

Mike
0
 

Author Comment

by:jat0818
ID: 41719282
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.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 41720103
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

Mike
0
 

Author Comment

by:jat0818
ID: 41723794
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.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 41723974
"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 ...

Mike
0
 

Author Comment

by:jat0818
ID: 41724692
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.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 16

Expert Comment

by:DcpKing
ID: 41725462
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.

Mike
0
 
LVL 8

Expert Comment

by:LajuanTaylor
ID: 41725953
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.
How-to-Audit-Report-Execution-in-SSR.pdf
0
 

Author Comment

by:jat0818
ID: 41727707
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.
0
 
LVL 8

Expert Comment

by:LajuanTaylor
ID: 41727803
@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:
https://blogs.msdn.microsoft.com/deanka/2010/02/15/troubleshooting-subscriptions-part-ii-using-the-reporting-services-trace-log-file/

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?
0
 

Accepted Solution

by:
jat0818 earned 0 total points
ID: 41733514
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.
0
 
LVL 16

Expert Comment

by:DcpKing
ID: 41733941
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)

Mike
0
 

Author Closing Comment

by:jat0818
ID: 41738719
Came up with my own workaround to the problem.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL Backup 24 70
SQL Transaction logs 8 10
SQL JOIN + SUBQUERY? 3 14
How toselect unique values 3 10
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now