Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

Using a SSRS Report to run SQL Agent Jobs part 2

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.

 

Comments

Posted by logicinside22 on 28 December 2011

i like to see the link for part-1

Posted by Devin Knight on 28 December 2011

It's in the first sentence of the post :).  www.bidn.com/.../using-a-ssrs-report-to-run-sql-agent-jobs

Leave a Comment

Please register or log in to leave a comment.