QuickBlog: Use PowerShell/SQLPS to get SQL Agent Jobs statuses…

Here’s another few PowerShell short script to be executed under SQLPS.exe and/or if you already have the SQL Server provider loading on your WindowsPowerShell folder under your user profile. Look for the script “Microsoft.PowerShell_profile.ps1”.

Requirement to run script:

  1. 1. Must have SQL Server 2008 in order the use the SQL Server provider.
  2. 2. You could run this script in SQLPS.exe
  3. 3. Optional-> load your SQL provider in your PowerShell V2 profile. Here’s how: http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx (Add this code in your WindowsPowerShell profile)
  4. 4. Results will be display on screen but you could add code to save result to a file. (" | Out-File …")

This script will list all my SQL Agent jobs on one server or more servers. Remember you can expand this script to do more. This is a teaser code:

   1: ## Sample Listing SQL Agent jobs results
   2: cd SQLSERVER:\SQL\YourServername\YourDefaultOrInstanceName\jobserver\jobs\
   3: $SQLjob = $null ; $jobHist = $null ; $jobName = $null
   4: $SQLjob = dir
   6: foreach($job in $SQLjob){
   7:     $jobHist += $job | select Parent, name, lastRunDate, lastRunOutcome | ft -auto
   8:     foreach($jobName in $job){
   9:         $jobFolder = "$jobName" + '.0\Jobsteps'
  10:         cd $jobFolder
  11:         $jobHist += dir | select SubSystem, Parent, name, lastRunDate, lastRunOutcome | ft -auto
  12:     }
  13:     cd SQLSERVER:\SQL\YourServername\YourDefaultOrInstanceName\jobserver\jobs\
  14: }
  15: cd\

Sample results:

More hints: (only if the SQL Provider is loaded)

1. Include the Instance-name or use “Default”.

2. With the right permission use cmdlet: cd SqlServer:\SQL\ Your-SQLServerName to move between SQL servers from one PowerShell console session.

3. This script will run under Windows Authentication.

4. MOST Important… While using both SQL Provider and/or SQLPS.exe you need to close/reopen the PowerShell session to refresh the information.


About maxt2posh

I’m from Puerto Rico, have been working with computers since 1979. Throughout many years, I have worked with SQL Server Technologies, provided support to Windows Servers/Client machines, Microsoft Virtualization Technologies, and build some Visual Studio solutions. I’m a Microsoft PowerShell MVP since 2009 and MVP SAPIEN Technologies since 2010. I speak in most of the SQLSaturday, IT Pro and .NET camps events around the Florida’s State. Also founder of the Florida PowerShell User Group which meeting every 3rd Tuesday evening of the month.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s