Activate/Disable Sql Server job

Hi everyone,

I have wrote this Powershell script

Param (
 
    [Parameter(Position = 0)]
    [string[]]
    $JobName,

    [Parameter(Position = 1)]
    [ValidateSet('activate', 'disable', 'start')]
    [string]
    $Action

)

Set-ExecutionPolicy RemoteSigned -Scope CurrentUser
$MachineName = $env:COMPUTERNAME
$instanceName = (Get-Service -ComputerName $MachineName -Exclude "MSSQLFDLauncher*" `
    | Where-Object { ($_.Status -eq "Running") -and (($_.Name -eq 'MSSQLSERVER') -or ($_.Name -like '*MSSQL$*')) } `
    | Select-Object @{Name = "ComputerName"     ; Expression = { $_.MachineName.ToUpper() } },
    @{Name = "InstanceName" ; Expression = { $_.ServiceName -replace "MSSQL\$" } },
    @{Name = "ServerName"   ; Expression = { If ($_.ServiceName -eq 'MSSQLSERVER') { $_.MachineName.ToUpper() }
            Else { $($_.MachineName.ToUpper() + '\' + $_.ServiceName -replace "MSSQL\$") } }
    }).ServerName


function activateJob($JobName, $instanceName) {
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server($instanceName)
    Write-Host -ForegroundColor Green "Paramaters : Instance name is :$instanceName, Job name is: $JobName"
    $sqljob = $server.JobServer.Jobs[$JobName]
    if($sqljob.IsEnabled){
        $actualStatus = "active"
    } else {
        $actualStatus = "disable"
    }
    Write-Host -ForegroundColor Green "The job name $JobName is $actualStatus"
    if ($sqljob) {
        $sqljob.IsEnabled = $true
        $sqljob.Alter()
    }
}

function disableJob($JobName, $instanceName) {
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server($instanceName)
    Write-Host -ForegroundColor Green "Paramaters : Instance name is :$instanceName, Job name is: $JobName"
    $sqljob = $server.JobServer.Jobs[$JobName]
   
    Write-Host -ForegroundColor Green "The job name $JobName is $actualStatus"
    if ($sqljob) {
        $sqljob.IsEnabled = $False
        $sqljob.Alter()
    }
}

function startJob($JobName, $instanceName) {
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server($instanceName)
    Write-Host -ForegroundColor Green "Paramaters : Instance name is :$instanceName, Job name is: $JobName"
    $sqljob = $server.JobServer.Jobs[$JobName]
    if ($sqljob) {
        $sqljob.start()
    }
}




if ($Action -eq 'activate') {
    TRY {
        Write-Host  "Trying to activate the SQL Server job: $JobName"
        activateJob -JobName $JobName -instanceName $instanceName
        Write-Host -ForegroundColor Green "The job $job was activated"
    }
    catch {
        Throw Write-Host -ForegroundColor Red "Activating the job $JobName was failed" -Exception $_.exception -PassThru
        break
    }
}
elseif ($Action -eq 'disable') {
    TRY {
         
        Write-Host  "Trying to disable the SQL Server job: $JobName"
        disableJob -JobName $JobName -instanceName $instanceName
        Write-Host -ForegroundColor Green "The job $job was disabled"
    }
    catch {
        Throw Write-Host -ForegroundColor Red "Disabling the job $JobName was failed" -Exception $_.exception -PassThru
        break
    }
}

elseif ($Action -eq 'start') {
    TRY {
        Write-Host  "Trying to start the SQL Server job: $JobName"
        startJob -JobName $JobName -instanceName $instanceName
        Write-Host -ForegroundColor Green "The job $job was started"
    }
    catch {
        Throw Write-Host -ForegroundColor Red "Starting the job $job was failed" -Exception $_.exception -PassThru
        break
    }
}

else {
    Write-Host -ForegroundColor Red "Invalid action parameter. Valid values are 'activate', 'disable', 'start'" 
}

when i execute it i get this error

PS D:\Scripts> .\manageSqlServerJob.ps1 'IndexOptimize - USER_DATABASES' disable
Trying to disable the SQL Server job: IndexOptimize - USER_DATABASES
Paramaters : Instance name is :MYSERV, Job name is: IndexOptimize - USER_DATABASES
The job name IndexOptimize - USER_DATABASES is active
Disabling the job IndexOptimize - USER_DATABASES was failed -Exception System.Management.Automation.RuntimeException: La propriété « IsEn
abled » est introuvable dans cet objet. Vérifiez qu’elle existe et qu’elle peut être définie.
   à System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   à System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   à System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   à System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) -PassThru
ScriptHalted
Au caractère D:\Scripts\manageSqlServerJob.ps1:93 : 9
+         Throw Write-Host -ForegroundColor Red "Disabling the job $Job ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], RuntimeException
    + FullyQualifiedErrorId : ScriptHalted

it said IsEnabled property not found but how if in this line i can get it

 if($sqljob.IsEnabled){
        $actualStatus = "active"
    } else {
        $actualStatus = "disable"
    }

and display it in the console

The job name IndexOptimize - USER_DATABASES is active

but when i would like set it to $true or $ false it said me the property not found, i have tested this on Sql Server 2019 standad edition and Sql Server 2017 entreprise edition and that fail
help please

It looks to me like in both your activateJob and disableJob functions the way you create your $sqlJob object is the same. I’m assuming you executed this example with $Action being “Disable” since the disableJob function fired and threw an error.
Have you verified that your activeJob function works either? The error says that the “IsEnabled” property doesn’t exist on that object.
Manually iterate through your code and get to the point where you’re creating the $sqljob variable and then pipe that variable to Get-Member and see if it has the IsEnabled property.
I’m betting you’ll find it’s not there.

Sure I have verified and th object contain that property
and you can see here in this output error the object with all properties

PS D:\Scripts> .\manageSqlServerJob.ps1 'IndexOptimize - USER_DATABASES' disable
Trying to disable the SQL Server job: IndexOptimize - USER_DATABASES
Paramaters : Instance name is :MYSERV, Job name is: IndexOptimize - USER_DATABASES
The job name IndexOptimize - USER_DATABASES is active


Parent                 : [MYSERV]
Category               : Database Maintenance
CategoryType           : 1
CurrentRunRetryAttempt : 0
CurrentRunStatus       : Idle
CurrentRunStep         : 0 (inconnu)
DateCreated            : 02/08/2022 11:20:59
DateLastModified       : 11/04/2024 19:29:35
DeleteLevel            : Never
Description            : Source: https://ola.hallengren.com
EmailLevel             : Never
EventLogLevel          : OnFailure
HasSchedule            : True
HasServer              : True
HasStep                : True
IsEnabled              : True
JobID                  : 9b28a88b-f9a8-41fd-be5a-431422a9b154
JobType                : Local
LastRunDate            : 26/05/2024 00:00:00
LastRunOutcome         : Failed
NetSendLevel           : Never
NextRunDate            : 02/06/2024 00:00:00
NextRunScheduleID      : 1030
OperatorToEmail        : 
OperatorToNetSend      : 
OperatorToPage         : 
OriginatingServer      : MYSERV
OwnerLoginName         : sa
PageLevel              : Never
StartStepID            : 1
VersionNumber          : 4
Name                   : IndexOptimize - USER_DATABASES
CategoryID             : 3
JobSteps               : {IndexOptimize - USER_DATABASES}
JobSchedules           : {EverySunday at 0AM}
Urn                    : Server[@Name='MYSERV']/JobServer/Job[@Name='IndexOptimize - USER_DATABASES' and @CategoryID='3']
Properties             : {Name=Category/Type=System.String/Writable=True/Value=Database Maintenance, 
                         Name=CategoryID/Type=System.Int32/Writable=True/Value=3, 
                         Name=CategoryType/Type=System.Byte/Writable=True/Value=1, 
                         Name=CurrentRunRetryAttempt/Type=System.Int32/Writable=False/Value=0...}
DatabaseEngineType     : Standalone
DatabaseEngineEdition  : Enterprise
ExecutionManager       : Microsoft.SqlServer.Management.Smo.ExecutionManager
UserData               : 
State                  : Existing

Disabling the job IndexOptimize - USER_DATABASES was failed -Exception System.Management.Automation.RuntimeException: La propriété « IsEn
abled » est introuvable dans cet objet. Vérifiez qu’elle existe et qu’elle peut être définie.
   à System.Management.Automation.ExceptionHandlingOps.CheckActionPreference(FunctionContext funcContext, Exception exception)
   à System.Management.Automation.Interpreter.ActionCallInstruction`2.Run(InterpretedFrame frame)
   à System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)
   à System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame) -PassThru
ScriptHalted
Au caractère D:\Scripts\manageSqlServerJob.ps1:94 : 9
+         Throw Write-Host -ForegroundColor Red "Disabling the job $Job ...
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : OperationStopped: (:) [], RuntimeException
    + FullyQualifiedErrorId : ScriptHalted

Ok. Without running the whole script and instead just selectively executing code in your IDE of choice (ISE, VS Code etc) run the following lines of code :

$Jobname = 'IndexOptimize - USER_DATABASES'
$MachineName = $env:COMPUTERNAME
$instanceName = (Get-Service -ComputerName $MachineName -Exclude "MSSQLFDLauncher*" `
    | Where-Object { ($_.Status -eq "Running") -and (($_.Name -eq 'MSSQLSERVER') -or ($_.Name -like '*MSSQL$*')) } `
    | Select-Object @{Name = "ComputerName"     ; Expression = { $_.MachineName.ToUpper() } },
    @{Name = "InstanceName" ; Expression = { $_.ServiceName -replace "MSSQL\$" } },
    @{Name = "ServerName"   ; Expression = { If ($_.ServiceName -eq 'MSSQLSERVER') { $_.MachineName.ToUpper() }
            Else { $($_.MachineName.ToUpper() + '\' + $_.ServiceName -replace "MSSQL\$") } }
    }).ServerName

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server($instanceName)
$sqljob = $server.JobServer.Jobs[$JobName]
$sqljob | Get-Member

This will print the output of Get-Member and then we can make sure that the “Isenabled” property actually exists by that name, and what type of property it is. I know your previous example showed the property in the output, but I can’t tell if that’s the actual name, or something that formatting has done, so we’re just being sure.

PS D:\Scripts>
$Jobname = ‘IndexOptimize - USER_DATABASES’
$MachineName = $env:COMPUTERNAME
$instanceName = (Get-Service -ComputerName $MachineName -Exclude “MSSQLFDLauncher*” | Where-Object { ($_.Status -eq "Running") -and (($_.Name -eq 'MSSQLSERVER') -or ($_.Name -like '*MSSQL$*')) }
| Select-Object @{Name = “ComputerName” ; Expression = { $.MachineName.ToUpper() } },
@{Name = “InstanceName” ; Expression = { $
.ServiceName -replace “MSSQL$” } },
@{Name = “ServerName” ; Expression = { If ($.ServiceName -eq ‘MSSQLSERVER’) { $.MachineName.ToUpper() }
Else { $($.MachineName.ToUpper() + '' + $.ServiceName -replace “MSSQL$”) } }
}).ServerName

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server($instanceName)
$sqljob = $server.JobServer.Jobs[$JobName]
$sqljob | Get-Member

TypeName : Microsoft.SqlServer.Management.Smo.Agent.Job

Name MemberType Definition


PropertyChanged Event System.ComponentModel.PropertyChangedEventHandler PropertyChanged(System.Object, System.Compon…
PropertyMetadataChanged Event System.EventHandler`1[Microsoft.SqlServer.Management.Sdk.Sfc.SfcPropertyMetadataChangedEventAr…
AddSharedSchedule Method void AddSharedSchedule(int scheduleId)
Alter Method void Alter(), void IAlterable.Alter()
ApplyToTargetServer Method void ApplyToTargetServer(string serverName)
ApplyToTargetServerGroup Method void ApplyToTargetServerGroup(string groupName)
Create Method void Create(), void ICreatable.Create()
DeleteJobStepLogs Method void DeleteJobStepLogs(datetime olderThan), void DeleteJobStepLogs(int largerThan)
Discover Method System.Collections.Generic.List[System.Object] Discover(), System.Collections.Generic.List[Sys…
Drop Method void Drop(bool keepUnusedSchedules), void Drop(), void IDroppable.Drop()
DropIfExists Method void DropIfExists(), void IDropIfExists.DropIfExists()
EnumAlerts Method System.Data.DataTable EnumAlerts()
EnumHistory Method System.Data.DataTable EnumHistory(Microsoft.SqlServer.Management.Smo.Agent.JobHistoryFilter fi…
EnumJobStepLogs Method System.Data.DataTable EnumJobStepLogs(), System.Data.DataTable EnumJobStepLogs(int stepId), Sy…
EnumJobStepsByID Method Microsoft.SqlServer.Management.Smo.Agent.JobStep EnumJobStepsByID()
EnumTargetServers Method System.Data.DataTable EnumTargetServers()
Equals Method bool Equals(System.Object obj)
ExecuteWithModes Method void ExecuteWithModes(Microsoft.SqlServer.Management.Common.SqlExecutionModes modes, System.Ac…
GetDomainRoot Method Microsoft.SqlServer.Management.Sdk.Sfc.ISfcDomainLite IAlienObject.GetDomainRoot()
GetHashCode Method int GetHashCode()
GetParent Method System.Object IAlienObject.GetParent()
GetPropertySet Method Microsoft.SqlServer.Management.Sdk.Sfc.ISfcPropertySet ISfcPropertyProvider.GetPropertySet()
GetPropertyType Method type IAlienObject.GetPropertyType(string propertyName)
GetPropertyValue Method System.Object IAlienObject.GetPropertyValue(string propertyName, type propertyType)
GetSqlServerVersionName Method string GetSqlServerVersionName()
GetType Method type GetType()
GetUrn Method Microsoft.SqlServer.Management.Sdk.Sfc.Urn IAlienObject.GetUrn()
Initialize Method bool Initialize(), bool Initialize(bool allProperties)
Invoke Method void Invoke()
IsExpressSku Method bool IsExpressSku()
IsSupportedProperty Method bool IsSupportedProperty(string propertyName)
PurgeHistory Method void PurgeHistory()
Refresh Method void Refresh(), void IRefreshable.Refresh()
RemoveAllJobSchedules Method void RemoveAllJobSchedules(), void RemoveAllJobSchedules(bool keepUnusedSchedules)
RemoveAllJobSteps Method void RemoveAllJobSteps()
RemoveFromTargetServer Method void RemoveFromTargetServer(string serverName)
RemoveFromTargetServerGroup Method void RemoveFromTargetServerGroup(string groupName)
RemoveSharedSchedule Method void RemoveSharedSchedule(int scheduleId), void RemoveSharedSchedule(int scheduleId, bool keep…
Rename Method void Rename(string newName), void IRenamable.Rename(string newname)
Resolve Method System.Object IAlienObject.Resolve(string urnString)
Script Method System.Collections.Specialized.StringCollection Script(), System.Collections.Specialized.Strin…
SetAccessToken Method void SetAccessToken(Microsoft.SqlServer.Management.Common.IRenewableToken token)
SetObjectState Method void IAlienObject.SetObjectState(Microsoft.SqlServer.Management.Sdk.Sfc.SfcObjectState state)
SetPropertyValue Method void IAlienObject.SetPropertyValue(string propertyName, type propertyType, System.Object value)
Start Method void Start(string jobStepName), void Start()
Stop Method void Stop()
ToString Method string ToString()
Touch Method void Touch()
Validate Method Microsoft.SqlServer.Management.Sdk.Sfc.ValidationState Validate(string methodName, Params Syst…
Category Property string Category {get;set;}
CategoryID Property int CategoryID {get;}
CategoryType Property byte CategoryType {get;set;}
CurrentRunRetryAttempt Property int CurrentRunRetryAttempt {get;}
CurrentRunStatus Property Microsoft.SqlServer.Management.Smo.Agent.JobExecutionStatus CurrentRunStatus {get;}
CurrentRunStep Property string CurrentRunStep {get;}
DatabaseEngineEdition Property Microsoft.SqlServer.Management.Common.DatabaseEngineEdition DatabaseEngineEdition {get;}
DatabaseEngineType Property Microsoft.SqlServer.Management.Common.DatabaseEngineType DatabaseEngineType {get;}
DateCreated Property datetime DateCreated {get;}
DateLastModified Property datetime DateLastModified {get;}
DeleteLevel Property Microsoft.SqlServer.Management.Smo.Agent.CompletionAction DeleteLevel {get;set;}
Description Property string Description {get;set;}
EmailLevel Property Microsoft.SqlServer.Management.Smo.Agent.CompletionAction EmailLevel {get;set;}
EventLogLevel Property Microsoft.SqlServer.Management.Smo.Agent.CompletionAction EventLogLevel {get;set;}
ExecutionManager Property Microsoft.SqlServer.Management.Smo.ExecutionManager ExecutionManager {get;}
HasSchedule Property bool HasSchedule {get;}
HasServer Property bool HasServer {get;}
HasStep Property bool HasStep {get;}
IsEnabled Property bool IsEnabled {get;set;}
JobID Property guid JobID {get;}
JobSchedules Property Microsoft.SqlServer.Management.Smo.Agent.JobScheduleCollection JobSchedules {get;}
JobSteps Property Microsoft.SqlServer.Management.Smo.Agent.JobStepCollection JobSteps {get;}
JobType Property Microsoft.SqlServer.Management.Smo.Agent.JobType JobType {get;}
LastRunDate Property datetime LastRunDate {get;}
LastRunOutcome Property Microsoft.SqlServer.Management.Smo.Agent.CompletionResult LastRunOutcome {get;}
Name Property string Name {get;set;}
NetSendLevel Property Microsoft.SqlServer.Management.Smo.Agent.CompletionAction NetSendLevel {get;set;}
NextRunDate Property datetime NextRunDate {get;}
NextRunScheduleID Property int NextRunScheduleID {get;}
OperatorToEmail Property string OperatorToEmail {get;set;}
OperatorToNetSend Property string OperatorToNetSend {get;set;}
OperatorToPage Property string OperatorToPage {get;set;}
OriginatingServer Property string OriginatingServer {get;}
OwnerLoginName Property string OwnerLoginName {get;set;}
PageLevel Property Microsoft.SqlServer.Management.Smo.Agent.CompletionAction PageLevel {get;set;}
Parent Property Microsoft.SqlServer.Management.Smo.Agent.JobServer Parent {get;set;}
Properties Property Microsoft.SqlServer.Management.Smo.SqlPropertyCollection Properties {get;}
StartStepID Property int StartStepID {get;set;}
State Property Microsoft.SqlServer.Management.Smo.SqlSmoState State {get;}
Urn Property Microsoft.SqlServer.Management.Sdk.Sfc.Urn Urn {get;}
UserData Property System.Object UserData {get;set;}
VersionNumber Property int VersionNumber {get;}

ok, that’s difficult to read without formatting your text, but it does look like “IsEnabled” is a boolean property that you can get, or set.

What happens when you manually try setting the property to $false i.e.:

$sqljob.IsEnabled = $false

If it doesn’t work inside your function, then it should also fail when you try it manually, but you’ll have the whole error right there.

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.