jat0818
asked on
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.
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.
ASKER
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.
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
Mike
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
ASKER
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.
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 ...
Mike
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
ASKER
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.
Mike
Other than that, I'm drawing a blank too - I'm sorry.
Mike
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
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
ASKER
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.
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:
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Well done, Jat0818, for getting something that does what you need.
(I'm sorry I couldn't help more)
Mike
ASKER
Came up with my own workaround to the problem.
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