Sometimes, an SSRS subscription fails for some reason. It can be annoying, particularly as the appropriate response can be hard to see immediately. There may be a long list of jobs that failed one morning if a Mail Server is down, and trying to work out a way of running each one again can be painful. It’s almost an argument for using shared schedules a lot, but the problem with this is that there are bound to be other things on that shared schedule that you wouldn’t want to be re-run.
Luckily, there’s a table in the ReportServer database called dbo.Subscriptions, which is where LastStatus of the Subscription is stored. Having found the subscriptions that you’re interested in, finding the SQL Agent Jobs that correspond to them can be frustrating.
Luckily, the jobstep command contains the subscriptionid, so it’s possible to look them up based on that. And of course, once the jobs have been found, they can be executed easily enough. In this example, I produce a list of the commands to run the jobs. I can copy the results out and execute them.
|
1 2 3 4 5 |
select 'exec sp_start_job @job_name = ''' + cast(j.name as varchar(40)) + '''' from msdb.dbo.sysjobs j join msdb.dbo.sysjobsteps js on js.job_id = j.job_id join [ReportServer].[dbo].[Subscriptions] s on js.command like '%' + cast(s.subscriptionid as varchar(40)) + '%' where s.LastStatus like 'Failure sending mail%'; |
Another option could be to return the job step commands directly (js.command in this query), but my preference is to run the job that contains the step.
This Post Has 31 Comments
Even better control on re-sending SSRS report on demand see below link
http://www.mssqltips.com/tip.asp?tip=1846
Hi Nimit,
Thanks for the link.
The suggestion in that tip is similar to what you get if you return js.command, but my preference is to rerun the job rather than calling the AddEvent procedure.
Rob
Hi Gents,
Since all the metadata of the subscription is known, is there a way to insert a subscription directly into these tables and create a job without using SSRS pages.
I am working on a project where all subscription data is maintained in user defined tables.
Thoughts ?
Cheers
Jimmy
I got a requirement recently to email the same report to 70 users but before sending, I had to change the user parameter on the report to the specific user and also change the emailid each time before kicking off the subscription. I dint want to create 70 subscritions for same report and just change the user & email id.
So I created a table with the user# and email id and created a sp to loop 70 times and update the subscription table to change the "Parameters" & Extensions fields and then kick off the subscription. Edited the agent job which kicks off the subscription and removed the statement and put a call to the sp. But when I ran the loop was so fast that it just emailed the 70th user with his user report. So I put a wait for 1 minute after each update so that the report finishes the first update and email and then continues. It takes 70 mins for the job to finish but since it runs in the night I am ok with that.
Any other suggesstions?
Jimmy & Nav,
Yes, there are lots of ways to skin this particular cat. For the kind of things that you’re both describing I prefer to use the SSRS Web Services and call a variety of methods, including Render(). Everything you can do with Report Manager can be done with the Web Services, so it’s even very feasible to roll your own Data Driven Subscriptions.
My code is about re-running ones that have failed because of things like a Mail Server outage. If other settings need tweaking I’d go with the Web Services almost every time.
Rob
@Navi:
data.driven.subscriptions.
You can use this application to rerun subscriptions- for failed subscriptions and for subscriptions that have not failed and need to rerun for example- a failed ETL process.
I know this is an old post, but along these lines, I have a question. when you open a subscription job in SSMS, the first thing you see is
"This job is owned by a report server process. Modifying this job could result in database incompatibilities. Use Report Manager or Management Studio to update this job."
is it safe to enable Notifications of failure without messing up the subscription, and then using your query manually run the subscription? maybe include them in a 2nd step based on step 1 failure?
Hi Robert,
It should be safe, but you can’t guarantee that any further changes you make won’t destroy your Notification.
So… your mileage may vary. Good luck though. 🙂
Rob
but there should be no problem enabling notifications on the SQL Agent job for failure, correct?
Correct. Probably fine.
http://geekswithblogs.net/shervin/archive/2007/06/20/113351.aspx
Perfect quick and easy – THANK YOU
Forgive me my ignorance, but can you go into detail about the stored procedure ‘start_job’ as listed above? We recently had a whole series of reports fail due to another server being down at the scheduled run time and after painstakingly running each report manually and sending out I began researching how to do so via a more controlled, automated method. I was able to modify the where clause on your above example to get what I needed but obviously I haven’t built sp_start_job. I’m a bit new to SSRS in general so forgive me if this is a stupid request. Any help would be greatly appreciated!
Scratch that, I’m a silly man and it’s in the MSDB database. I did want to follow up and say thanks for the solution on this! You just saved me a ton of manual work (and the risk associated with trying to run 30+ reports manually with different parameters) the next time a DBA forgets to enable remote connections after a nightly backup. Hooray!
Alan – I guess you’ve answered your own question. I’m pleased it’s helped!
Hi,
this is very helpful, thank you.
could i ask another question in this blog?
I have been tasked to move an entire folder to a separate report manager.
i have the new directories and the reports in the right places(moved from the other server’s folders using an app called ReportSync.v1.1.2 ) however i need the subscriptions as well.
is there a way to list the reports that i need the subscriptions for, and copy them so that the new server, which only contains part of the original servers reports?
Thanks,
Nevarda
Hi Nevarda,
You should be able to query the first box using the tables: Subscriptions, Catalog, Users, ReportSchedule, and Schedule. You should have the Catalog and Users in place, and you’ll need to get the appropriate uniqueidentifier values for the fields involved. Then you should be able to create the necessary Schedule and ReportSchedule rows.
You ought to be able to do it using PowerShell too, using the SSRS API. This would be cleaner, but potentially a bit more work.
Hope this helps,
Rob
Thanks Rob.
you see my main issue is that when I delete the reports folder(only one as the rest must stay on server A) off Server A, server B needs to immediately start processing the subscriptions just like server A was.
This cannot happen simultaneasly hence i need a script to run that will recreate the subscriptions from server A on server B and immediatly take over the load.
i’ve now started with getting the job name for each report’s subscription and scripting it in SQL.
although it runs, it doesnt add the subscription quite like the manual method and is not visible if you view it on the web page subscriptions settings and i am not quite sure that it suceeded in running the job yet. I’m still going to play a bit more and hopefully get it right.
Thanks again,
Nevarda
Any chance we could automate this to run say every 30 minutes and exec the list it produces? I want to use SSIS.
I love this query btw, I use it daily. I am moving to another position and want this run by itself.
Hi Donovan,
You should use SQLAgent, not SSIS. You could easily make a cursor from this, fetch each row and execute it.
Rob
Thanks Rob, I have actually started that already.
A more complex idea that I have though is to rerun the reports only once, and then, if it still fails, to send an email notification to the reporting services team as well as the recipients of the reports.
Any ideas?
You’d have to give the jobs a bit of time to run. Why not have a third job which queries the status again, but instead of generating start statements, try having send_dbmail?
Yea, we do have everything backed up from time to time.
I am going to use this code now to identify which reports have still failed after 12pm:
SELECT s.Description ,
s.laststatus ,
c.Path ,
c.Name ,
LastRunTime ,
ScheduleID
FROM ReportServer.dbo.subscriptions s WITH ( NOLOCK )
JOIN ReportServer.dbo.Users u WITH ( NOLOCK ) ON s.ownerid = u.userid
JOIN ReportServer.dbo.catalog c WITH ( NOLOCK ) ON s.report_oid = c.itemid
JOIN ReportServer.dbo.ReportSchedule rs WITH ( NOLOCK ) ON rs.SubscriptionID = s.SubscriptionID
WHERE (( ( s.laststatus LIKE ( ‘Done: %’ )
AND s.laststatus NOT LIKE ( ‘% 0 errors.’ )
)
OR s.laststatus LIKE ( ‘Failure%’ )
)
OR s.laststatus LIKE ‘%not valid%’)
AND DATEPART(HOUR,LastRunTime) >= 12
AND DATEPART(DAY,LastRunTime) > DATEPART(DAY,GETDATE()-1)
Most of our reports are sent out by 10:30AM, so if they are still failing after 12PM, there is a problem.
I can then extract those email addresses and send them a fail notification and let the admins know so they can fix the reports.
Thanks again for your initial code Rob – It has really helped me.
OK,
It took me a while and a lot of resources, but I eventually figured it out on my own.
We use forced error as a way to stop a subscription from firing if the report is empty, usually, if it is empty, that means there was some sort of data integrity issue that needs to be sorted out, then we have to run the subscription.
I built a little application that makes this user friendly, but here is the jest of what it does:
1) Get the list of subscriptions from the Reporting Database
Select SubscriptionID, EventType from Subscriptions
This will give you the data needed to fire the event. Important.
2) Fire the ‘AddEvent’ Stored procedure from the same database using the
SubscriptionID and EventType as the parameters.
3) The SQL Job will pick up the new event from the table and fire the
subscription then delete the entry from the table.
You can see how it is easy to put an interface around this. It works very well and there is not a whole lot to it. I put in some other features, like being able to stop or start the subscription and what not.
Excellent work!
the addevent procedure will always return positive (and immediately), which doesn’t make it easy for error handling. if instead you would call a thing that waits for the report completion and throws errors, then you can start exception handling.
having tried all this out at the places i worked, i’d recommend anyone who has a lot of emailing going on to avoid the subscriptions and go for the web services. maybe it’s a bit unusual at the beginning but it makes the processes clean, with errors handled or thrown, and the subscriptions managed centrally.
i built this tool but there are others. http://busyreports.codeplex.com/. you can also start from the MSDN documentation which has good examples on running reports.
i got a situation like different parameter for same one report subscription in ssrs Email subscription but same recipient address, which means all diff. parameter attachments in One Email because same recipient name. can any one help me on this.
thanks
sankar
Sankar – this is what you are trying to do? SSRS doesn’t do multiple attachments like that. I would suggest using the web services and constructing an email yourself in .Net code.
Great article.. informative and very useful! Thanks!
So simple!!! Thanks!!! I used to change the time on each subscription. Oyyyy.