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

Web Automation with PowerShell aka the Ghost at the Machine

PowerShell is good at running and manipulating Windows applications.  For example, I once had a client that needed a process to log on to a web site, fill in a number of text boxes and click a button to the download a file.  I was able to accomplish this quickly with PowerShell.


In this blog, I want to demonstrate how  to automate applications such as Internet Explorer using PowerShell. I have a script below that uses a web site that provides a map with zip codes for a specified location.  The PowerShell script will navigate to the site, enter a location into the address text box and click the button to look display the map. 


Before we begin I need to point out that the script uses a free module called WASP which provides a number of convenient automation CommandLets such as sending key strokes to an application.  Typically a module adds extra support for a specific type of functionality.  Unless you have already downloaded WASP, you will need to do so.  You can download it from http://wasp.codeplex.com/.  After you download it, extract the file WASP.DLL and copy it to a folder where PowerShell looks for modules. 


To determine where PowerShell searches for modules, enter the following command.

$env:PSModulePath

In the output, you should see a list of folder paths separated by semi colons.  This environment variable holds the places PowerShell will look for modules.  Pick one of the folders, ideally one under your documents folder, and create a new folder named WASP in it.  Then paste the unzipped WASP.dll in that folder.  A module must have its own folder. 

Now we are ready.  Let's review the Web Automation script below.

Import-Module WASP

$url = "http://maps.huge.info/zip.htm"

stop-process -processname iexplore*   #  End any other Explorer sessions.

start-sleep 2  # Wait above to finish.

$ie = New-Object -comobject InternetExplorer.Application

$ie.visible = $true

$ie.silent = $true

$ie.Navigate( $url )

while( $ie.busy){Start-Sleep 1}

Select-Window "iexplore" | Set-WindowActive

$txtArea=$ie.Document.getElementsByName("address")

Foreach($element in $txtArea)
    {
        $element.value = "Boston MA"
    }

$btnFind=$ie.Document.getElementsByName("BUTTON")

Foreach($btn in $btnFind)
    {
        $btn.Click()
    }

Let's review this code in detail.  First we need to import the WASP module which do with the line below.

Import-Module WASP

The next line simply stores the URL we are going to use into a variable as shown below.

$url = "http://maps.huge.info/zip.htm"

Then we need to stop any other Internet Explorer processes so our program won't get confused when it tries to being up an IE window.

stop-process -processname iexplore*   #  End any other Explorer sessions.

Now let's review several lines of  related code below.

$ie = New-Object -comobject InternetExplorer.Application

$ie.visible = $true

$ie.silent = $true

$ie.Navigate( $url )

The first line above creates an instance of Internet Explorer and places a reference to it into the variable $ie.  The line "$ie.visible = $true"  makes the IE application visible.  The line "$ie.silent = $true "  says to suppress messages.  Finally the line "$ie.Navigate( $url )" tells IE to go to the URL specified in the $url variable.

IE needs time to finish the above tasks which we give it with the line below.

while( $ie.busy){Start-Sleep 1}

Since IE is in the background, we need to bring it activate and select it using WASP CommandLets as shown below.

Select-Window "iexplore" | Set-WindowActive

Ok. The screen is up and waiting for input.  To fill in the address field, we need to get a reference to it first which we do as follows.

$txtArea=$ie.Document.getElementsByName("address")

Now we can use the reference to fill in the text box.  Here's where each web automation script will be different.  To navigate around the web page, we use DOM but to do that you need to know what the web page source code is.  In IE, you can right mouse click on the page and select View Source to see it.  Then you need to review it to see what the elements are and how they are identified.  If the element has a unique id, you can use the GetElementByID method to get a reference to it, i.e. $myelement = $ie.GetElementByID.  In this case, all we have is a name.  Unfortunately, GetElementsByName returns an array of elements even if there is only one which is true here.  So we need to loop through the array to enter the text as shown below.

Foreach($element in $txtArea)
    {
        $element.value = "Boston MA"
    }

In the code above, the Foreach iterates over the $txtArea array storing each item into $element.  We use this in the loop to set the value, i.e.   $element.value = "Boston MA".  Ok.  It's a bit awkward but Web Automation can be like that since you don't usually have any control over the HTML of the page.

Ok.  The location is entered, now we need to click the Find button.  As before, there is no unique ID so we'll use GetElementsByName and loop through the collection. 

$btnFind=$ie.Document.getElementsByName("BUTTON")

Foreach($btn in $btnFind)
    {
        $btn.Click()
    }

In the above code, we are using the button click method to execute the find.

If it worked, you see the page with a map showing Boston, Massachusetts and the zip codes for the area. 

Internet Explorer is only one of many applications that can be automated.  Office applications, Win form applications, and many more applications can be automated in a similar manner.  This feature can help automate a routine manual task to save you time or be integrated into a production job.  It's another reason why PowerShell is such a valuable tool.

Thanks,

Bryan