SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Using a SSRS Report to run SQL Agent Jobs

Data Warehouse latency is often a complaint I have heard from end users when trying to access data via either Reporting Services reports or Excel.  Generally, I promise 24 hour latency unless the job mandates updates hourly or even sooner.  Screenshots for this blog are available at my regular blog site

With these complaints in mind I decided to create a report that could kick off the SQL Agent job that processed my Data Warehouse load and Cube update. It is a pretty simple report to create. Here are the steps I did:

Step One

Create a Data Source that points to MSDB on the server that the SQL Agent job that you want to run is located.

Step Two

Create a Dataset that runs the system stored procedure sp_start_job with the name of the job.

Step Three

Add some text! Let the user know what’s going on after they click on the report otherwise it will just show a blank report. Drag a textbox over and add the appropriate text.

Deploy the report and test!

There are some circumstances where you would not want to use this method:

· Running the job in the middle of the day could severely cripple a transactional system that the Data Warehouse load pulls from.

· The job takes longer than just a few minutes to process. Remember you are trying to improve latency. You don’t want to expose a poorly performing load process (even if the performance time is due to the size of the load not bad code)

· You haven’t trained your end users in what the report does. You don’t want end users clicking this report over and over again because it is running a major process. 

Devin Knight

Devin is a BI consultant at Pragmatic Works Consulting. Previously, he has tech edited the book Professional Microsoft SQL Server 2008 Integration Services and was an author in the book Knight's 24-Hour Trainer: Microsoft SQL Server 2008 Integration Services. Devin has spoken at past conferences like PASS and at several SQL Saturday events. He is a contributing member to the Business Intelligence Special Interest Group (SIG) for PASS as a leader in the SSIS Focus Group. Making his home in Jacksonville, FL, Devin is a participating member of the local users’ group (JSSUG).


Posted by Myles on 12 November 2009

Interesting idea.  Seems risky as the author points out.  What I'd really like to have is a way to initiate a process AFTER the report is rendered.  I have a report that display errors in data that require simple fixes, but the user has to take many steps to get into the application.  If they could just click on the item, get a pop-up to enter a value, then ta da! update.

Posted by jn_gray on 12 November 2009

Myles, I've done something like this using SSRS, but not with a popup.  What you need to do is create an "Update" report, set hidden parameters on the report to identify the record to update passed from the record clicked in your error report, and then non-hidden, prompted parameters that will ask for the updated values.  Pass them into a stored procedure data set that updated the specified values, then render a confirmation message and redirect link back to the error report!

Posted by knight_devin@hotmail.com on 12 November 2009

Myles that's a great idea sounds like jn_gray has a possible solution.

Posted by Myles Sigal on 12 November 2009

Excellent!  Thanks, I'll give that a try.  Let me see if I understand it correctly.  Call another report using the information from the record I click, pass some id's or data from the record to the report.  The called report has a data set that is a stored proc with dependent parameters passed in.  It does the work, and the report displays a confirmation, maybe a before and after picture of the specific update.

Posted by sqlguy-549681 on 18 November 2009

graet idea, but what if need to display my job result in the report

Posted by knight_devin@hotmail.com on 25 November 2009

It would likely have to be done in a second report that queries MSDB for SQL Agent results.  You need a second report because the report brings back results faster than the job would likely finish.  So you could have a link on this report to the second one.  

Posted by john schroeder on 19 January 2010

Will this work with SSRS 2005 and a 2005 database?

Posted by knight_devin@hotmail.com on 21 January 2010

Absolutely, this will work with 2005 also.

Leave a Comment

Please register or log in to leave a comment.