THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Rob Farley

- Owner/Principal with LobsterPot Solutions (a MS Gold Partner consulting firm), Microsoft Certified Master, Microsoft MVP (SQL Server), APS/PDW trainer and leader of the SQL User Group in Adelaide, Australia. Rob is a former director of PASS, and runs training courses around the world in SQL Server and BI topics.

Re-running SSRS subscription jobs that have failed

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.

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.

Published Thursday, March 18, 2010 3:51 PM by Rob Farley
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Nimit Parikh said:

Even better control on re-sending SSRS report on demand see below link

http://www.mssqltips.com/tip.asp?tip=1846

March 18, 2010 8:42 AM
 

Rob Farley said:

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

March 19, 2010 9:06 PM
 

Jimmy George said:

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

March 21, 2010 6:36 AM
 

Nav said:

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?

March 21, 2010 10:17 AM
 

Rob Farley said:

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

March 21, 2010 5:25 PM
 

David Leibowitz said:

@Navi:

data.driven.subscriptions.

March 23, 2010 11:13 AM
 

Ian Emery said:

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.

September 6, 2010 11:01 AM
 

Robert Bishop said:

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?

March 30, 2012 9:07 AM
 

Rob Farley said:

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

March 30, 2012 9:41 AM
 

Robert Bishop said:

but there should be no problem enabling notifications on the SQL Agent job for failure, correct?

March 30, 2012 9:43 AM
 

Rob Farley said:

Correct. Probably fine.

March 30, 2012 10:44 AM
 

Zainab Dhuliawala said:

August 23, 2012 5:40 PM
 

Andy W said:

Perfect quick and easy - THANK YOU

June 11, 2013 7:05 AM
 

Alan P said:

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!

October 29, 2013 10:36 AM
 

Alan P said:

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!

October 29, 2013 2:15 PM
 

Rob Farley said:

Alan - I guess you've answered your own question. I'm pleased it's helped!

October 29, 2013 8:58 PM
 

Nevarda said:

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

July 22, 2014 7:12 AM
 

Rob Farley said:

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

July 22, 2014 7:46 AM
 

Nevarda said:

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

July 23, 2014 7:47 AM
 

Donovan said:

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.

August 27, 2014 5:58 AM
 

Rob Farley said:

Hi Donovan,

You should use SQLAgent, not SSIS. You could easily make a cursor from this, fetch each row and execute it.

Rob

August 27, 2014 6:18 AM
 

Donovan said:

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?

August 27, 2014 7:46 AM
 

Rob Farley said:

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?

August 27, 2014 8:42 AM
 

Donovan said:

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.

August 27, 2014 9:00 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement