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
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.
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 :
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.