Octopus.Script exported 2020-11-20 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.
param(
[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)
Provided under the Apache License version 2.0.
To use this template in Octopus Deploy, copy the JSON below and paste it into the Library → Step templates → Import dialog.
{
"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//opt/buildagent/work/75443764cd38076d/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"
}
}
Page updated on Friday, November 20, 2020