Submitted By: Ryan Rinehart

Retrieves the status of the last SQL Server job run on a set of database servers.

PowerShell
Edit|Remove
# ========================================================================================
# AUTHOR           :               Ryan M. Rinehart MCSE, MCDBA, MCTS:SQL Server 2005
#
# EMAIL            :               rrinehart77@hotmail.com
# 
# NAME             :               smoJobStatus.ps1
#
# VERSION          :               1.1 [5/23/2008]
#
# DATE             :               5/23/2008
#
# DESCRIPTION      :               Reads a List file of SQL Server Names and then retrieves the last run  
#                                  status of enabled jobs. Messages script user whether each job was failed.
#                                  or successful
#
# COMMENT(s)       :               Uses SQL 2005 SMO Objects
#                                  v1.1 - added section to load SMO dlls - requires SQL 2005 Tools installed??  
#                                  (mine are enterprise version).
# ==========================================================Functions=======================================================

#function Read-List takes input string of file path to a text file to read.
#returns an arraylist containing all the lines in the text file.
function Read-List
{
                param([string]$filePath)
                # ensure the file exists
                if(!([System.IO.File]::Exists($filePath)))
                {              
                                # File NOT found - Notify User.
                                Write-Host "The specified list file was not found.  Please verify that the file " `
                                "exists and you have access to it."
                }
                else
                {
                                # File Found - create an arraylist object to hold file contents
                                $list = New-Object([System.Collections.ArrayList])
                                # populate the list with the string array returned by the ReadAllLines 
                                # Method of the File Object.
                                $list = [System.IO.File]::ReadAllLines($filePath)
                                # Length of 0 indicates no lines were in the file? 
                                # notify user if no lines OR return the arraylist object to the caller
                                if($list.Length -eq 0)
                                {
                                                Write-Host "Error accessing list contents, or file has no lines."
                                }
                                else
                                {
                                                Return $list
                                }
                }
}
#function Get-LastSqlJobStatus takes input string SQL Server Name
#lists success or failure of the last run of each job on the server.
function Get-LastSqlJobStatus
{
                #input parameter of SQL Server Name to Test - type string
                param([string]$sqlServerName)
                # new-up a SQL Server SMO Server object reference to the Name passed in by the caller
                $sqlServer = New-Object Microsoft.SqlServer.Management.Smo.Server($sqlServerName)
                # iterate through the jobs on the server object using JobServer.Jobs Collection property
                foreach($job in $sqlServer.JobServer.Jobs)
                {
                                # only test enabled jobs
                                if($job.IsEnabled -eq $true)
                                {
                                                # test last run out come for each job.  Report success or failure of job
                                                if($job.LastRunOutCome -eq "Failed")
                                                {              
                                                                # report failure of jobX
                                                                Write-Host $job.Name $job.LastRunDate "FAILED!!!!!!!!!!"
                                                }
                                                else 
                                                {              
                                                                # report success of jobX
                                                                Write-Host $job.Name $job.LastRunDate "success..."
                                                }
                                }
                }
}
# ==========================================================Variables==========================

$smoPath = [string] "C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies"                                                                                                     # Path to the SDK assemblies - SQL Tools/SDK

$listPath = 'c:\Scripts\PS\SMO\sqlSrv.txt'     # Path to the server list file

# ==========================================================Main Script Logic=================

# load the assemblies needed for SQL SMO from the SDK
[void][System.Reflection.Assembly]::LoadFile($smoPath + "\Microsoft.SqlServer.ConnectionInfo.DLL") 
[void][System.Reflection.Assembly]::LoadFile($smoPath + "\Microsoft.SqlServer.SMO.DLL") 
[void][System.Reflection.Assembly]::LoadFile($smoPath + "\Microsoft.SqlServer.SMOEnum.DLL") 
Write-Host "==============================================================================================="
$date = Get-Date
Write-Host "Beginning SQL Server Management Objects (SMO) Job Status Report on:" $date
Write-Host "==============================================================================================="
$myList = Read-List $listPath                                                                                                                                                                                                       
# ArrayList of server names gathered from the list file specified
# iterate through the collection of Server Names Returned.
for ($count = [int]0; $count -lt $myList.Length; $count++)
{
                # Get a reference to the current Server in the list and query the job results using 
                # Get-LastSqlJobStatus
                $serverName = $myList[$count]
                Write-Host "====================" $serverName "JOB STATUS REPORT ========================="
                Get-LastSqlJobStatus $serverName

                Write-Host "`r"

}

Write-Host "=============================================================================================="
$date = Get-Date
Write-Host "SMO Job Status Report Complete!!!" $date
Write-Host "=============================================================================================="