Using a SSRS Report to run SQL Agent Jobs part 2

Devin-Knight, 2009-12-11

In part one of this post I showed how you can use a report to run SQL Server Agent Jobs by using a report.  In that post I only showed you how to run a single job but hardcoding in the job name.  Someone recently asked if it was possible to have a drop-down list of jobs to choose from instead of have the hardcoded job name so I thought it made for a great opportunity to write a part 2.   

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 a parameter called @jobname

Step Three

Create a second DataSet that runs a select statement against the system table sysjobs.

SELECT name
FROM sysjobs

 

Step Four

Right-click on the @jobname parameter and change the available values to Get values from a query.  The Dataset should be the one created in Step Three and in this case the Value field and Label field are both the field called name.

 

Step Five

Add some text!  Let the user know what’s going on.  Drag a textbox over and add the appropriate text.  I wrote an expression using the @jobname parameter so it read which job was started:

=“Running this report will start the SQL Agent job called “+Parameters!jobname.Value+“.”

Preview the report.  You should have a parameter drop-down list of all your jobs that you can select and run.

 

Select the job you would like to start then hit View Report.  I have several Reporting Services Subscriptions you can see by the GUID names listed.  When the report runs you should see your textbox appear.

 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads