PowerShell and SQL Agent: A Powerful Combination

Many people know that PowerShell integrates well with SQL Server especially when you use the SQLPS module.  But not everyone knows that this same module makes integration with SQL Agent a snap. 


I recently had the need to allow a technically proficient end user the ability to submit some SQL Agent jobs.  My first thought was to give her SQL Server Management Studio.  However, the company has rules against this which precluded that option.  Then I realized that PowerShell offers another option.  Why not give her a PowerShell script that allows her to submit  jobs on demand.


To do this, I developed a script that loads a list of SQL Agent jobs into the good ole Out-GridView where she can select jobs she wants to run.  Let's look at the script...

if(-not(Get-Module -name "sqlps") {
          Import-Module "sqlps"
          }
# Set where you are in SQL Server...
set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\JobServer\Jobs

while (1 -eq 1)
{
   $jobname = $null
   $jobname = Get-ChildItem | Select-Object -Property Name, Description, LastRunDate, LastRunOutcome  | Where-Object {$_.name -like "User*"} | out-gridview -Title "Job Run" -OutputMode Single
  
   If (!$jobname) { Break }


   $jobobject = Get-ChildItem | where-object {$_.name -eq $jobname.Name}
  
   $jobobject.start()
}




The initial block of code just loads the SQLPS module if it is not loaded.  Note: If you installed SQL Server Management Studio, you have this module.  If not, you can download is at:
http://www.microsoft.com/en-us/download/details.aspx?id=29065




Let's look at the code that loads the module below.



if(-not(Get-Module -name "sqlps")) {
          Import-Module "sqlps"
          }


Pretty simple.  If the module is not loaded, load it.  The SQLPS module take a minute to load so I like to check rather than waste the time of reloading it.


PowerShell supports a feature called Providers.  This is when an a resource such as SQL Server or the Windows Registry provides an interface that simulates the file system.  The SQLPS module implements a provider that allows us to navigate around SQL Server as if it were a file system.  Hence, we can do the command below.


set-location SQLSERVER:\SQL\BryanCafferkyPC\DEFAULT\JobServer\Jobs




The line above sets the current location to the SQL Agent jobs on the default instance of my machine. 


Although this could be a fall through script that submits the job and exits, I thought it would be nice if it came back to the selection list after submitting a job so you could submit more.  To do this, I set up an infinite loop with a While statement as follows...



while (1 -eq 1) {


Since 1 will always equal 1, the loop never ends.  But we can use the break statement to exit when we are ready.   


Within the While loop, we are going to test the job name for null as a condition to exit.  Therefore  we want to initialize $jobname to null.
$jobname = $null


Then we have a rather complex looking statement.  Fear not! It is actually pretty easy to follow.  We are leveraging PowerShell's ability to pipe (a fancy word for stack) commands, i.e. the output of one command becomes the input of the next.  The bar '|' separates the commands in the stack.  Let's look at the statement below more closely.


   $jobname = Get-ChildItem | Select-Object -Property Name, Description, LastRunDate, LastRunOutcome  | Where-Object {$_.name -like "User*"} | out-gridview -Title "Job Run" -OutputMode Single


Although the variable $jobname is on the left, it is the last part of the statement to be executed.  In other words, everything to the right of '$jobname =' must be executed before the value is returned to $jobname.  Get-ChildItem simply returns the list of items in a folder.  However, I want to restrict the jobs available to the user to job names prefixed with 'User'.  This allows me to set aside specific jobs for the user to execute.  I can filter on the list returned by piping the output of Get-ChildItem into a Select-Object CommandLet which extracts just the properties I am interested in. In turn, I pipe that into the Where-Object CommandLet which filters on the name property.  Note:  piping means that multiple items are being passed so the $_ is a reserved variable that we use to get the current instance, i.e. applies to each item.  In this case, we filter on name like 'User*' and pipe the results into Out-GridView which provides a nice GUI display list.  I want to call attention to the Out-GridView part of this line.  Let's look at the code below. 


out-gridview -Title "Job Run" -OutputMode Single


The -Title parameter gives a custom title to the grid view window.  The -OutputMode parameter controls whether multiple items can be selected or as we want here, just one item, i.e. Single. 


An interesting facet is that this line will halt execution of your script until the user closes the grid view window.  Whatever item they selected will be stored in $jobname.  If the user clicks Cancel, nothing is returned and the line below will check for that condition and exit the script.


If (!$jobname) { Break }


So !$jobname means they did not select a job and Break ends the script.


If a job was selected, we need to get an object reference to the job so we can submit it.  We do that as follows.




 $jobobject = Get-ChildItem | where-object {$_.name -eq $jobname.Name}



Again, the Get-ChildItem is piped into a Where-Object CommandLet allowing us to get the specific job we want.




Finally, we submit the job as follows.




$jobobject.start()


Summary

This example just touches on the possibilities.  You can also create, modify, delete and even schedule SQL Agent jobs from PowerShell scripts.  Note:  SQL Agent also supports calling PowerShell scripts.  PowerShell also has a built in job scheduler which can be used in combination with SQL Agent or independent of.  However, I think it is clear that the combination of PowerShell and SQL Agent is a marriage made in heaven. 


Thanks,


Bryan