locked
Get SQL Agent Job EnumHistory via Powershell RRS feed

  • Question

  • Hi guys!!

    First of all, I am not sure how this question should be categorized.  It may be in the wrong forum :-P

    Ok so here it is...

    I am using Powershell through DollarU to execute SQL Agent job (all steps) or one specific step at a time.  So far so good, everything is working!!  However, when it gets to the point where I want to get the history information, I can't get what I want.

    Here is my program:  

    param ([parameter (Mandatory = $true)][string]$ServerName,  
    
           [parameter (Mandatory = $true)][string]$JobName, 
    
           [string]$StepName = "")
    
     
    
    write-host "Starting SQL Agent Job $($JobName) on Server $($ServerName)"
    
    $date=Get-Date
    
    write-host "It is now: $($date)" 
    
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
    
    $srv = New-Object Microsoft.SqlServer.Management.SMO.Server("$ServerName")
    
    $job = $srv.jobserver.jobs["$JobName"] 
    $jobstart="No" 
    
    if (($job))
    {         
    
       if ($StepName -ne '') 
          {
            $job.Start($StepName)
            $jobstart="Yes"
            Start-Sleep -s 5  # Pause for 5 seconds (optional) - was 30 seconds (v1); v2=5
          }
    
       else 
          {
            $job.Start()
            $jobstart="Yes"
            Start-Sleep -s 5
          }
    }         
    
    else 
    {
       $jobstart="Not found"
    }  
    
    if ($jobstart -eq "Yes")
    { 
       write-host "Job $($JobName) on Server $($ServerName) started"
       $i=0
    
       do 
       { 
         $job.Refresh();   
         $iRem = $i % 5;
         $jobrunning=$job.CurrentRunStatus.ToString();
    
         if ($iRem -eq 0) 
         {
           $date=Get-Date
           write-host "Job $($JobName) Processing--Run Step:$($job.CurrentRunStep) Status:$($job.CurrentRunStatus.ToString())... at $($date)"
         } 
    
         Start-Sleep -s 10; # Pause for 10 seconds  - was 60 seconds (v1); v2=10
         $i++;
       }        
    
       while ($job.CurrentRunStatus.ToString() -ne "Idle") 
         if ($job.LastRunOutcome -ne "Cancelled")  
         {
           write-host "Job Processing done"
         }
         else
         {
           write-host "Job Processing cancelled/aborted"
         }                          
    
         #   $jobRunning="TRUE"   
         write-host "$($srv.name) $($job.name)"
         write-host "Last job outcome $($job.LastRunOutcome)"
         write-host "Last job outcome $($job.LastRunDate)"          
    
            if ($job.EnumHistory().Rows[0] -ne $null)
            {
               write-host "xxxx $($job.EnumHistory().Rows[0].Message)"
            }                
    
            if ($job.EnumHistory().Rows[1] -ne $null)
            {
               write-host "yyyyy $($job.EnumHistory().Rows[1].Message)"
            }
                            
    
            $LastRun=$job.LastRunOutcome 
    
            if ($StepName -ne '') 
            {     
               $JobStep = $Job.JobSteps[$StepName]
               Write-Host "Name: $($JobStep.Name) RunDate: $($JobStep.LastRunDate) Status: $($JobStep.LastRunOutCome)"
            } 
    
            else
            {
              $StepCount = $job.JobSteps.Count - 1        
    
            for ($i=0;$i -le $StepCount;$i++)
            {
             $m = $job.JobSteps[$i].LastRunDate
             write-host "Name: $($job.JobSteps[$i].Name) RunDate: $($job.JobSteps[$i].LastRunDate) Status: $($job.JobSteps[$i].LastRunOutCome)" 
    
             if ($job.LastRunDate -gt $m) 
                 {
                       $LastRun="FailedOrAborted"
                 }  
           }
         }
    
    
         if ($LastRun -eq "Failed")
         {
           write-host "Job returned with Failed status"
           exit 2
         } 
    
         if ($LastRun -ne "FailedOrAborted")
         {
           if ($LastRun -ne "Cancelled")
               {
                 exit 0
               }
    
           else
               {
                 write-host "Job Cancelled xxxxx"
                 exit 3
               }
            }                  
    
         else 
         {
                 write-host "Job Failed or Aborted"
                 exit 2
          }
    }          
    
    else
    {
      write-host "Unable to Start Job $($JobName) on Server $($ServerName)"
      write-host "Reason: Job may not exist or not enabled."
      exit 1
    } 
    
     

    When there are no specific step to execute, I get the proper information.  When it is only a specific step, the second line (YYYY) returns information from past executions.

    Here is an example:

    In this case, I ran step one at 10:42.

    In the following log, you will see that it returned the first line of the 10:42 execution, and the second line of the 10:09 execution!!!

    C00-SUPPORT-SITE DEBUT
    C00-SUPPORT-SITE WSPGM-CODNOEUD F6BRDJS01
    _!================================================
    _!**
    $!** PROCEDURE .. : BR_A_BC_FRAME.UPD_CRITICAL.LIST_EXECUTE.SSISPKG
    $!** VERSION .....: 000
    $!** EXECUTION .. : 0091911
    _!**
    $!** SESSION .... : BR_S_BC_FRAME.UPD_CRITICAL.LIST
    $!** VERSION .... : 000
    $!** EXECUTION .. : 0006727
    _!**
    _!** PROCESS DATE : 00/00/0000
    _!**
    _!------------------------------------------------
    $!** PARAM  S_P01 = CriticalList.xls
    _!------------------------------------------------
    _!**  VARIABLES
    SERVER              :%BRSQLAgentServerName%                           
    JOBNAME             :BC_FrameUpdate_CriticalList_Mylene               
    FILENAME            :%S_P1%                                           
    _!------------------------------------------------
    $!**  INHERITANCE VARIABLES
    _!================================================
    Date is 20160815
    Time is AM.1042
    Starting...
    Extention = .xls  StepName = ExecuteCriticalAndSRP_xlsFormat
    Call D:\DollarUScripts\STCDEV\SQLAgent\SQLAgentJobMerged.bat brdsqldev01 BC_FrameUpdate_CriticalList_Mylene ExecuteCriticalAndSRP_xlsFormat
    Start job check ..
    Mon 08/15/2016
    10:42 AM
    ServerName= brdsqldev01
    JobName= BC_FrameUpdate_CriticalList_Mylene
    StepName= ExecuteCriticalAndSRP_xlsFormat
    ExecuteCriticalAndSRP_xlsFormat
    Job BC_FrameUpdate_CriticalList_Mylene to run on server brdsqldev01
    changing ownership
    You already own this job, no change required.
    changed ownership
    powershell.exe -noprofile "D:\DollarUScripts\STCDEV\SQLAgent\SQLAgentJobMerged.ps1"  -ServerName brdsqldev01  -JobName BC_FrameUpdate_CriticalList_Mylene -StepName ExecuteCriticalAndSRP_xlsFormat
    Starting SQL Agent Job BC_FrameUpdate_CriticalList_Mylene on Server brdsqldev01
    It is now: 08/15/2016 10:42:01
    Job BC_FrameUpdate_CriticalList_Mylene on Server brdsqldev01 started
    Job BC_FrameUpdate_CriticalList_Mylene Processing--Run Step:1 (ExecuteCriticalAndSRP_xlsFormat) Status:Executing... at 08/15/2016 10:42:07
    Job Processing done
    brdsqldev01 BC_FrameUpdate_CriticalList_Mylene
    Last job outcome Succeeded
    Last job outcome 08/15/2016 10:42:00

    xxxx The job succeeded.  The Job was invoked by User STATCAN\BrBpmsDev.  The last step to run was step 1 (ExecuteCriticalAndSRP_xlsFormat).  The job was requested to start at step 1 (ExecuteCriticalAndSRP_xlsFormat).

    yyyyy Executed as user: STATCAN\BR_SQLDEV. Microsoft (R) SQL Server Execute Package Utility  Version 10.50.6000.34 for 32-bit  Copyright (C) Microsoft Corporation 2010. All rights reserved.    Started:  10:09:23 AM  Error: 2016-08-15 10:09:33.82     Code: 0xC0209302     Source: CriticalSRP Load Connection manager "\\fld6filer\brd-redesign$\Solution_Development\4.Software development\Survey Interface Subsystem\MyleneTestingFiles\CriticalList\CriticalList.xlsx"     Description: SSIS Error Code DTS_E_OLEDB_NOPROVIDER_ERROR.  The requested OLE DB provider Microsoft.ACE.OLEDB.12.0 is not registered. Error code: 0x00000000.  An OLE DB record is available.  Source: "Microsoft OLE DB Service Components"  Hresult: 0x80040154  Description: "Class not registered".  End Error  Error: 2016-08-15 10:09:33.82     Code: 0xC020801C     Source: Load XLSX Data into StagingTable 1 1 XLSX DB Source [665]     Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "\\fld6filer\brd-redesign$\Solution_Development\4.Software development\Survey Interface Subsystem\MyleneTestingFiles\CriticalList\CriticalList.xlsx" failed with error code 0xC0209302.  There may be error messages posted before this with more information on why the AcquireConnection method call failed.  End Error  Error: 2016-08-15 10:09:33.82     Code: 0xC0047017     Source: Load XLSX Data into StagingTable 1 1 SSIS.Pipeline     Description: component "XLSX DB Source" (665) failed validation and returned error code 0xC020801C.  End Error  Error: 2016-08-15 10:09:33.82     Code: 0xC004700C     Source: Load XLSX Data into StagingTable 1 1 SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2016-08-15 10:09:33.82     Code: 0xC0024107     Source: Load XLSX Data into StagingTable 1 1      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  10:09:23 AM  Finished: 10:09:33 AM  Elapsed:  10.828 seconds.  The package execution failed.  The step failed.

    Name: ExecuteCriticalAndSRP_xlsFormat RunDate: 08/15/2016 10:42:01 Status: Succeeded
     Job stopped running
    10:42 AM
    Error: Return code is 0
    End.
    Date is 20160815
    Time is AM.1042
            1 file(s) copied.        

    ____________________________________________________________

    Can somebody help me with this?

    Thanks,
    Mylene                                

     


    Mylene Chalut

    Monday, August 15, 2016 6:03 PM

Answers

  • I would like to suggest you to raise a question in PowerShell Forum, maybe you will get more professional powershell scripts advice.
    • Marked as answer by MyleneC Wednesday, November 9, 2016 6:58 PM
    Thursday, August 18, 2016 2:37 AM

All replies

  • I am not familiar with powershell, but do you mean that there should be more records after time 10:42? 
    Tuesday, August 16, 2016 1:49 AM
  • Excel driver has a limitation the package needs to run in 32 bit mode on a 64 bit machine you do need this setting applied.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/4011eb8c-4dda-4186-a5ec-b79c4dcc78fa/developed-packages-in-32bit-and-tryin-to-run-on-64-bit-error-on-aquiring-excel-connection-manager?forum=sqlintegrationservices


    Please click Mark As Answer if my post helped.

    Tuesday, August 16, 2016 2:45 AM
  • Sorry... English is not my primary language. Let me try to explain again...

    There are absolutely no problems with executing the task with a .xls or .xlsx.  The problem is getting back the right lines of information from the History log.

    In the following picture, you will see the lines that I am trying to get from my last execution:

     

    To do that, I am using the following lines of code:

    if ($job.EnumHistory().Rows[0] -ne $null)
       {
          write-host "xxxx $($job.EnumHistory().Rows[0].Message)"
       }                
    
    if ($job.EnumHistory().Rows[1] -ne $null)
       {
          write-host "yyyyy $($job.EnumHistory().Rows[1].Message)"
       }
    

    The lines of information that are being returned from the History log are the followings (from first example stated at the beginning of this post):


    Mylene Chalut

    Tuesday, August 16, 2016 12:08 PM
  • Maybe I should add the stepName somewhere to get the proper information?

    Mylene Chalut

    Tuesday, August 16, 2016 12:09 PM
  • I tried this...

    if ($job.EnumJobStepLogs($StepName).Rows[0] -ne $null)
       {
          write-host "xxxx $($job.EnumJobStepLogs($StepName).Rows[0].Message)"
       }                
    
    if ($job.EnumJobStepLogs($StepName).Rows[1] -ne $null)
       {
          write-host "yyyyy $($job.EnumJobStepLogs($StepName).Rows[1].Message)"
       }

    It doesn't return anything!!!


    Mylene Chalut

    Tuesday, August 16, 2016 12:45 PM
  • I tried this...

    if ($job.EnumHistory($stepName).Rows[0] -ne $null)
            {
               write-host "xxxx $($job.EnumHistory($stepName).Rows[0].Message)"
            }                
    
    if ($job.EnumHistory($stepName).Rows[1] -ne $null)
            {
               write-host "yyyyy $($job.EnumHistory($stepName).Rows[1].Message)"
            }

    Nope!!!


    Mylene Chalut

    Tuesday, August 16, 2016 12:56 PM
  • I would like to suggest you to raise a question in PowerShell Forum, maybe you will get more professional powershell scripts advice.
    • Marked as answer by MyleneC Wednesday, November 9, 2016 6:58 PM
    Thursday, August 18, 2016 2:37 AM
  • I had an idea that I was in the wrong forum. I will get back anyway to post the answer... if I get one from the Poweshell forum!!! :-)

    Mylene Chalut

    Thursday, August 18, 2016 12:49 PM