none
Powershell Script to Check SQL Server Agent Jobs RRS feed

  • Question

  • Hello,

    I've encountered an issue while trying to check the status of some SQL Server Agent jobs. The script is executed under windows task scheduler with a service account. This account has a login in the database. While troubleshooting, the SA role was added to the account login and it was able to retrieve the job statuses but I would like to give the account the least privilege it needs. I then gave the account the SQLAgentReaderRole but this did not work. Any suggestions?

    Snippet of Powershell code checking for SQL Jobs:

    try
    {
        [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo");
        $srvr = New-Object "Microsoft.SqlServer.Management.Smo.Server" $env:computername;
        $body2 = $srvr.JobServer.Jobs | Where-Object {$_.IsEnabled -eq $TRUE -and $_.LastRunDate -gt $d} | Select Name, LastRunOutcome, LastRunDate 
        $results = $body2 
        "[$($results)]"
        
        foreach($result in $body2)
        {
           if($results.LastRunOutcome -eq "Failed")
           {
                $SQLFlag = $TRUE
           }
        }
        
        $results = $results | out-string
        if($results -eq "")
        {
            "No SQL jobs were found."
            $body2 = "No SQL jobs were found."
        }
        else
        {
            $body2 = $body2 | ConvertTo-HTML -head $a 
        }
    }
    catch
    {  
        if($_.Exception.Message -eq "Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: verify that the assembly containing this type is loaded.")
        {
            $body2 = "SQL server not installed."
        }
        elseif($_.Exception.Message -eq "Cannot find type [Microsoft.SqlServer.Management.Smo.Server]: make sure the assembly containing this type is loaded.")
        {
            $body2 = "SQL server not installed."
        }
        elseif($_.Exception.Message -eq "The following exception was thrown when trying to enumerate the collection: `"An exception occurred while executing a Transact-SQL statement or batch.`"")
        {
            $body2 = "No SQL Jobs setup"
        }
        else
        {
            $SQLFlag = $TRUE 
            "An error occurred trying to check the SQL log. Error message: [" + $_.Exception.Message + "]"
            $body2 = "An error occurred trying to check the SQL log. Error message: [" + $_.Exception.Message + "]"
        }
    }
    

    • Moved by Bill_Stewart Friday, July 7, 2017 7:03 PM This is not "fix/debug/rewrite my script for me" forum
    Friday, June 2, 2017 3:33 PM

All replies

  • You fail to not the error or what it is that didn't work.

    I would also suggest that posting in the SQLServer forum would be a better choice for SQS specific issues.


    \_(ツ)_/

    Friday, June 2, 2017 3:54 PM
  • I do not receive an error, it simply returns nothing. But if I run as a different user with admin rights, I get the jobs that ran and their statuses. 
    Friday, June 2, 2017 4:08 PM
  • You will have to modify the script to report errors.  We cannot guess at this since the script works with correct credentials. 

    If you are getting no email then the issue is likely that the account you are using does not have access to email. 

    If you did not write this script then you will have to ask the author for help.


    \_(ツ)_/

    Friday, June 2, 2017 4:20 PM