THE SQL Server Blog Spot on the Web

Welcome to - 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 provides consulting and training courses around the world in SQL Server and BI topics.

Re-running SSRS subscription jobs that have failed

Hi! - Great that you've found this page, but it's no longer here! You can find the content over at:

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



Nimit Parikh said:

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

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.


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 ?



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.


March 21, 2010 5:25 PM

David Leibowitz said:



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


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:


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?



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,


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,


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.


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 ,


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


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

Kieth Stewart said:


 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.

October 10, 2014 10:44 AM

Rob Farley said:

Excellent work!

October 10, 2014 5:20 PM

joe said:

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. you can also start from the MSDN documentation which has good examples on running reports.

January 21, 2015 12:56 PM

Sankar said:

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.



December 28, 2015 8:36 PM

Rob Farley said:

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.

December 28, 2015 10:34 PM

Vishnu Menon said:

Great article.. informative and very useful! Thanks!

March 17, 2016 1:49 PM

Joseph Arechiga said:

So simple!!! Thanks!!! I used to change the time on each subscription. Oyyyy.

October 17, 2016 12:09 AM
Anonymous comments are disabled

This Blog



No tags have been created or used yet.


News? Haven't you read my blog?

My Company

Can't find something?

Contact Me

Twitter: @rob_farley
Skype: rob_farley

MVP (SQL Server)


Adelaide SQL UG

Privacy Statement