Author Bio

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, Code Camps and 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).

Using a SSRS Report to run SQL Agent Jobs

By knight_devin@hotmail.com in Devin Knight | 11-11-2009 1:15 PM | Categories: Filed under: Reporting Services 2008

Rating: |

Discuss | 3,783 Reads | 116 Reads in Last 30 Days |8 comment(s)

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.

Comments

Myles Sigal said:

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.

November 12, 2009 8:05 AM

jn_gray said:

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!

November 12, 2009 8:12 AM

knight_devin@hotmail.com said:

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

November 12, 2009 10:02 AM

Myles Sigal said:

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.

November 12, 2009 10:49 AM

sqlguy-549681 said:

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

November 18, 2009 3:29 AM

knight_devin@hotmail.com said:

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.

November 25, 2009 7:11 AM

john schroeder said:

Will this work with SSRS 2005 and a 2005 database?

January 19, 2010 11:15 AM

knight_devin@hotmail.com said:

Absolutely, this will work with 2005 also.

January 21, 2010 11:13 AM