SQL - Update Job
Octopus.Script exported 11/20/2020 by aqovia belongs to 'SQL Server' category.
Updates a MS SQL server job with provided ID to be enabled or disabled
Parameters
When steps based on the template are included in a project's deployment process, the parameters below can be set.
Job Id
JobId
The SQL server job id which is a GUID.
Job name
JobName
Optional job name to show on the logs instead of JobId
Job Status
JobStatus
Choose Enable to enable the job, and Disabled to disable the job
ConnectionString
ConnectionString
The connection string to connect to the target SQL Server
Script body
Steps based on this template will execute the following PowerShell script.
Show scriptparam(
[string]$ConnectionString,
[string]$JobId,
[string]$JobName,
[string]$JobStatus
)
$ErrorActionPreference = "Stop"
function Get-Param($Name, [switch]$Required, $Default) {
$result = $null
if ($OctopusParameters -ne $null) {
$result = $OctopusParameters[$Name]
}
if ($result -eq $null) {
$variable = Get-Variable $Name -EA SilentlyContinue
if ($variable -ne $null) {
$result = $variable.Value
}
}
if ($result -eq $null) {
if ($Required) {
throw "Missing parameter value $Name"
} else {
$result = $Default
}
}
return $result
}
function Execute-SqlQuery($query) {
$queries = [System.Text.RegularExpressions.Regex]::Split($query, "^\s*GO\s*$$", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)
$queries | ForEach-Object {
$q = $_
if (!(StringIsNullOrWhitespace($q)) -and ($q.Trim().ToLowerInvariant() -ne "go")) {
$command = $connection.CreateCommand()
$command.CommandText = $q
$command.ExecuteNonQuery() | Out-Null
}
}
}
& {
param(
[string]$ConnectionString,
[string]$JobId,
[string]$JobName,
[string]$JobStatus
)
$jobStatusText = ''
if ($JobStatus -eq '1') {
$jobStatusText = "Enabling"
} elseif ($JobStatus -eq '0') {
$jobStatusText = "Disabling"
}
$jobDisplayName = ''
if ($JobName) {
$jobDisplayName = $JobName
} else {
$jobDisplayName = $JobId
}
Write-Highlight "$jobStatusText SQL Server job: [$jobDisplayName]"
Write-Verbose "SQL Server Job Id: [$JobId]"
$query = @"
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_update_job @job_id=N'$JobId', @enabled=$JobStatus
GO
"@
$connection = New-Object System.Data.SqlClient.SqlConnection
$connection.ConnectionString = $ConnectionString
Register-ObjectEvent -inputobject $connection -eventname InfoMessage -action {
write-host $event.SourceEventArgs
} | Out-Null
Write-Verbose "Connecting"
try {
$connection.Open()
Write-Verbose "Executing script"
Write-Verbose $query
Execute-SqlQuery -query $query
}
catch [Exception]
{
Write-Verbose $_.Exception|format-list -force
throw $_
}
finally {
Write-Verbose "Closing connection"
$connection.Dispose()
}
} `
(Get-Param 'ConnectionString' -Required) `
(Get-Param 'JobId' -Required) `
(Get-Param 'JobName') `
(Get-Param 'JobStatus' -Required)To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.
Show JSON{
"Id": "91bbd24f-8975-4d0e-9f55-736587f945e9",
"Name": "SQL - Update Job",
"Description": "Updates a MS SQL server job with provided ID to be enabled or disabled",
"Version": 1,
"ExportedAt": "2020-11-20T12:26:07.817Z",
"ActionType": "Octopus.Script",
"Author": "aqovia",
"Packages": [],
"Parameters": [
{
"Id": "41a33da5-012d-4871-a3e8-983fa4a5dcbe",
"Name": "JobId",
"Label": "Job Id",
"HelpText": "The SQL server job id which is a `GUID`.",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "ba6bdd3b-ebfb-4c47-b214-f95044c8460e",
"Name": "JobName",
"Label": "Job name",
"HelpText": "Optional job name to show on the logs instead of JobId",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
},
{
"Id": "a2ea4eca-77e3-4733-9714-9fa2b87929e7",
"Name": "JobStatus",
"Label": "Job Status",
"HelpText": "Choose `Enable` to enable the job, and `Disabled` to disable the job",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "Select",
"Octopus.SelectOptions": "0|Disabled\n1|Enabled"
}
},
{
"Id": "0bdc16b0-0086-4597-9dcd-970ddbdda258",
"Name": "ConnectionString",
"Label": "ConnectionString",
"HelpText": "The connection string to connect to the target SQL Server",
"DefaultValue": "",
"DisplaySettings": {
"Octopus.ControlType": "SingleLineText"
}
}
],
"Properties": {
"Octopus.Action.RunOnServer": "false",
"Octopus.Action.Script.ScriptSource": "Inline",
"Octopus.Action.Script.Syntax": "PowerShell",
"Octopus.Action.Script.ScriptBody": "param(\n [string]$ConnectionString,\n [string]$JobId,\n [string]$JobName,\n [string]$JobStatus\n)\n\n$ErrorActionPreference = \"Stop\"\n\nfunction Get-Param($Name, [switch]$Required, $Default) {\n $result = $null\n\n if ($OctopusParameters -ne $null) {\n $result = $OctopusParameters[$Name]\n }\n\n if ($result -eq $null) {\n $variable = Get-Variable $Name -EA SilentlyContinue\n if ($variable -ne $null) {\n $result = $variable.Value\n }\n }\n\n if ($result -eq $null) {\n if ($Required) {\n throw \"Missing parameter value $Name\"\n } else {\n $result = $Default\n }\n }\n\n return $result\n}\n\nfunction Execute-SqlQuery($query) {\n $queries = [System.Text.RegularExpressions.Regex]::Split($query, \"^\\s*GO\\s*$$\", [System.Text.RegularExpressions.RegexOptions]::IgnoreCase -bor [System.Text.RegularExpressions.RegexOptions]::Multiline)\n\n $queries | ForEach-Object {\n $q = $_\n if (!(StringIsNullOrWhitespace($q)) -and ($q.Trim().ToLowerInvariant() -ne \"go\")) {\n $command = $connection.CreateCommand()\n $command.CommandText = $q\n $command.ExecuteNonQuery() | Out-Null\n }\n }\n}\n\n& {\n param(\n [string]$ConnectionString,\n [string]$JobId,\n [string]$JobName,\n [string]$JobStatus\n )\n\n $jobStatusText = ''\n if ($JobStatus -eq '1') {\n $jobStatusText = \"Enabling\"\n } elseif ($JobStatus -eq '0') {\n $jobStatusText = \"Disabling\"\n }\n\n $jobDisplayName = ''\n if ($JobName) {\n $jobDisplayName = $JobName\n } else {\n \t$jobDisplayName = $JobId\n }\n\n Write-Highlight \"$jobStatusText SQL Server job: [$jobDisplayName]\"\n Write-Verbose \"SQL Server Job Id: [$JobId]\"\n\n $query = @\"\nGO\nUSE [msdb]\nGO\nEXEC msdb.dbo.sp_update_job @job_id=N'$JobId', @enabled=$JobStatus\nGO\n\"@\n\n\t$connection = New-Object System.Data.SqlClient.SqlConnection\n $connection.ConnectionString = $ConnectionString\n Register-ObjectEvent -inputobject $connection -eventname InfoMessage -action {\n write-host $event.SourceEventArgs\n } | Out-Null\n\n Write-Verbose \"Connecting\"\n try {\n $connection.Open()\n\n Write-Verbose \"Executing script\"\n Write-Verbose $query\n Execute-SqlQuery -query $query\n }\n catch [Exception]\n {\n Write-Verbose $_.Exception|format-list -force\n throw $_\n }\n finally {\n Write-Verbose \"Closing connection\"\n $connection.Dispose()\n }\n\n } `\n (Get-Param 'ConnectionString' -Required) `\n (Get-Param 'JobId' -Required) `\n (Get-Param 'JobName') `\n (Get-Param 'JobStatus' -Required)"
},
"Category": "SQL Server",
"HistoryUrl": "https://github.com/OctopusDeploy/Library/commits/master/step-templates/sql-update-job.json",
"Website": "/step-templates/91bbd24f-8975-4d0e-9f55-736587f945e9",
"Logo": "iVBORw0KGgoAAAANSUhEUgAAAMgAAADICAMAAACahl6sAAAAGXRFWHRTb2Z0d2FyZQBBZG9iZSBJbWFnZVJlYWR5ccllPAAAAD9QTFRFlZ+r3DAr6p+dy8/V4G9t////5efp9M7NrLS+wCYm8/T1vcPK1tnd10xK+fn6/PLyUU5O+eXk3+Hk7O3u7/DxS2XoPwAADb9JREFUeNrsnYl6nbgOgMEYDHghGN7/Wa8k70B6s3AOZD5o2umcSaf+0S4bUbX/kat6QB6QB+QBeUAekAfkAXlAHpAH5AF5QB6QB+QBeUAekAckXMv4XwBZVCPVnwcZlZSNXRrzp0HGTkqplrY1zfKHQboGMZwoGvVXQUbVy152QaPUu3XrJJCl6Xsp1/SBfbdunQJiZd/3zVqqmfprIEb1iLHRpLF5s279FsQ0iCH3etQ03R8CQYyq74/MwTbN3wGxQFGRRJTaJiVL815z/wXIIiviQEunq2lsNyZhvdfcfw6iCMPavl9H20jkgV8gP1F2NRRJmvEvgIA4gAS0B8xkpexEYWB3F0ijAyOxfwAkcsBvHQk53QWW71HwGm8PIhJHazIS98HYdUqBar1TJD8EYQOABGNe+w0J0dj3iuSHIOMw6PRHOyDpdhggE2XvDmLYAChsDh4MSPI1g92DWkGaosbbey0kARbOyFCaTCYgDemioQWp3D+O9EO4NGNCRpIFMKQzjlG9TyS/iOwoE64jjeaVwICOzjeoGfgue38QshPRMV57lhpVjbNemZTMK7X+gaQRSRgQzaz2JDX9CjRiDvWV+gMgRniSltWMMV0TSo1fcIEjEAKUa7k/CDiomkjaeeAU8JEmoRAOuoLp/hWidTJp9RBiipkF07our9fj/Lpmn51MeM2TnAx5gnp/cRZj6P2aD6BdWoBu1QUeiESwWoCu8a10OBfzHUFaATIxoFssfjIxUKbZiJobkg/ibFSNny2aM/pa4Lt0y4eoWwJkQP9S11NQNoOmw18Ic0qDDsIIg59TiC517aTDa5a7OBDPLDjRBMemmbgTCIhjEINbNVpHLXzozzxAhI4mg9ETv7i4DwhYiHa6JfA2T9F6dPltaDwgBQifwgG5ZOAMlpNAZlrShEpW8ykG/mgkCaMmX40LXwX3uUBR21wLgoYxoMOtc22agpJlGBM5AYF5pcFUwOkXXr8Ty2n7IxrWgze4sIo6WrvD4LNx6pc8QDtzHVA0uwGIcJ6otO4IQhahfZLCtqYjYiUwsOlqEMMp8S31w4MIHrUKv1PvnZlhsUJjF4NAWHQ5PCRUIoGA5XutEpMJsquPFjvzX6GcB2I0Ybg45wWDpi/Iz7K07QPiOfZQEwtls7gShCL6kGe6U4tBg8Bmk7syfSjRpF0glOVCEDT3Mp0KQZyV+cxeswKEjur1baGcuc8O66bQsM10C0Wa6jy4oG2E7gXkXeAxdOdhmLkMBPxWSLJyFj5vBKJLURAGJ58m0NKNcuLh01UgLLvXU87CWSEQVlDUSOHu/gQp2xgaTSAidRFISICjl83UiyVYl3/NIdHiKQZy73pNEIq4BqTNzZht2w8sCISjXWjnqYtcEZtLwTBM9c2Qci5I+ouDYs2sQMGPZxH+Y5kGiFIE6nskp4LwEPcmTpaBd99MqZTiLHPK2wwRDAQq5sxVjeS+enMBSGhAzMRhQsTIUOK1Lz9w2cWHZqy+YSevkMiknWvSMRfZoGg2mX1ecBA6yHupCyRCEqDkasaqMYsYc/LGRwWUmdHd7j4dG/x4ukIiE3HQ382KVDF546NAN9XHSmQsWo65wkbmuFSdxcdCtQ7yKP2ZgzLdx9dc19kSEbFqF0mzdsYuDgydf/I/RW8m324jPGUgPPgsoTPz0Af5MNn0p5ZgZpDJ9F6QfI2ztxQf/TT3DS+2J8Hm8b/sYAJxmXeCzJukikdnpcUUG5BeKKzQnfpf0UJUX4gmpyaNdVoQJlWzYSGGG9I5Fz0mXtoJGEh9sPc70ZZErBrN+0AMyyTCkkEwr1BJe1hOwnfysEiQyl5dMWneqlp8iGGCstyI4YLIVKT4gwfDJmvMTHDrIUP44FWz4JbEe93vnIUJXlSHyUDi92rnps1c+/LcgBiG7OIghqu6KHHXYxZlMsLLfpAzlAGTfjB0ICzlgLq0jqO5rGbnIAudtU+KqpAfKiI25XghCM3cuYlvn34+D2Qil5rqKDZlWRY/BA97CkM4aWRb89Pz2+eBsIHMedab1smks62fogs0+JMSDmL+3RH080B8a9qDCJMVvXrehgiu6yiP+pRN0epEgQi3SeUkkgeXXUOuDmdWBn7Wbuh5Gz2U67JtgsvqomUdtw4RQnNx3hMNJ269QS2iXRN7DrmUmXXGIYr+48knBqoTLUR4xztTXzRU73OgSPvSmov27OscELCEQWBgQM1hrjqc2tR+EPx1ojgVZMJTc+hzQzXl2sCc0pVMFkDRLa85iHbWyQe0Xoau1rkrg0AMk5VU5pJCmeXOILR9CMGCJ7cL5TuDJCVReDe7Aoi5K8hUUwKYc4A0MoXCLRy/+vHOIKBYPnXnbVk7BY1KS78zCKPNJShmY/9pjo0ToJjW/PErtJHxniCCjjtAxMBds9LXcrYCIZjFau4PAqURxwg+bDvvuJ/WdeiiEGW8PYge9GSEL7yjMNxOlLGd87XjGi3jriC4k4tHY8H5Gn94GUtc56QiCBn5eGcQMHRB9epEe2yDE0boe4y2i0f8jUcBkPV2IHg2nmHDkwk+uAqD573Q1dps0WAqYPTLi0L7r0CAAXs4NR3vxy8mi+fDAKRQI0AZ7wgyD7j8AQ/O0bMjrDFL8cjeYu0m+KEDux2IyLo4qFM0Q6R4GKnbgbQ3BDE6UdRsXpxWdblIrN00p0fiuBfIpCMGbtIafHwS8UAkYaHG2uLpRHBcKzqvW4GM6Skxhs62a6R7fh0fPgyZripARnK8NwOJ8gh9UXz00K0fn5p2v1uUXXZp771AhN6cc8PZLt4ejFJ+3INV8fm3cQkl7nqngOj9le7jJ8ARAwgqF0HFhxDHDq775Vp0SgGb/308XEEjg5KLbUgmo1Kdx8hSlRuBOHlU2bPfBp8GzSIGPn1o246e3BvBB9usKLwPCHPHqPAx42C1thAIkTQKn80fF6tsNtHiTiB0imelAQlBIluBOJmAVPBRXWXL6QM3ATGYslPhKpNEmq1AnJ04kI2vvQnIxAftXWofQRYUyGZxOJMDOXZjd+4BYnU6mZdApOw3AulwcAWR2O2ib9EOEoNOSSCqFi1f4ViXbL2Lokki3ka2MrkDiKryg5IIgqePRpxRozYUjmQxi9o+Pb1e3/tVVTG1yaJuGZz2IHt/nGoEN9zQbBe1di53NOCEi3p3vbwbX8oD7n1PkzfwH5RljX7iDs7fMDQ5yHrrtrmpLFeDyKraqDbpFk6pkRKsO04NckYBJW8a5bZCpWh9s7HrXpMzfhVEVdX2RtLENhpJJSWNcUKMkBqqppgTBmKBPGVEVeu68UIQ4NjPLwtjtUg08KOx2dCK3eQ2SOQtSAMkciHIUlX9/tMmkRQUXiB7JwtlbpbPXwBiqqra3cZVxUlnSaPCHwCLPzo/jYp1JUi/U6yuwZltNH6uPxh8YuXRHKcRdMsCSHsViK0KjzUqWSWMvt8bj5EHY3LR3MfWdt1yGUiVCQRFUdGXBNWqjklU6KhkOmUpD4Yqq1uvAmkAZHVdBZrXBhQ0CXcBDmcm2y4c+uHCnGxIVJZNlfVWkIpcVgf330HY0e19UIqyODMpyUGzlkwYWb4FkfFFtv7/QSwtP0CYTFCUxq877VpzgWASmWXAdtN7fCdIUKcyUEBo6StSKU9i8s6Q7Lyboiw4a9JhfL8KpE/j/3Lr7WMzyJHEiqTzAjEuoy+cs/Nc14CYqjoK62AxMnnbPqTAVC+iQHBQOUbFctnYUjFXSYQU6yD36vNAntTL0sCzhvL57d03arfP8GaJVJu/fu03xUnn1KtznSGXCO/vPVYmS3uljWx1q/eRJQ/mfr6sT+ibIy+LFZZpr/VayyZE7lPCzk2XpQmznwxffulova/FkUIk3VFxAiWIT+jlZwOL15eOcftSZK+KpR94MaNkVmF9MggQQ7y5EERVpXKBoZfeyNhYmXjVOjYRTFXaC0G8SIKb2lbvnYzlFU2PX7y977TotZr1FZDFk7ipnoWhLzJUJqBO1BmiXpYfxVyuGzdNzKUglMgHmWQRfWloSDmkYW6BaZwppryeJenYi8eBfqn50ESZNMFARuUyYhnbV2qbBVuXpjQuczdF+nhVO6j3JIszENO4MCkzmx59C3VbpvuWtrUvHr/+9QZdcMPGyUJu2gtyN4U5erV1wZHlLx7H/NWWaRNAKK3fh2572IaIFkNiMXcACb4LKI5KCih8q+PH7QxVV0v36pHlX99WMLLaBfmi8D2I5ytOlZYY6ZtXv2rhOztWNghlp1gdvpxgr1ApnR9f/qaFb+0hRqFsh6tjMNmJIo+J9uWvI/nm9vQaUfIb3JQG0imXz2fRsHn5C2K+e2DArH1QsNhvGKuUR462OWhsr/Llbyf4yaEaGR2Yu83gsVaftLgMUtqN4b/hFR4/O69lk1iUsVTTG+VFofbbz+YN73776VFAH99dG1Iu7l09Uh1bdCdf/wqlXxyXHRML5sD/GBD/jpfx/fJsvOttu589vnXv2KhAIBgYQQNfNg//hBdyQcio+vCjxxpks1gLApmqj+rjox0/5G1BgteVfbaPhTjR6Okwl/kAFtl/9PcGyWqpPutEYFW1dM5CAARkcneJlDwLlVP+dVDhMNdHW8mP45TzriBZ7k+Xi4W9kbMS0v5JkDdeD8gD8oA8IA/IA/KAPCAPyAPygDwgD8gD8oA8IA/IA/IXr/8JMAAhf0RDrOWy2QAAAABJRU5ErkJggg==",
"$Meta": {
"Type": "ActionTemplate"
}
}Provided under the Apache License version 2.0.