Running Sql Scripts with Invoke-SQLCMD

[Thomas_Chambers]

I am using a Custom Powershell script to run a set of SQL Scripts to upgrade our database. These SQL Scripts have code in them that raises a SQL error if the SQL Script has been run on that database before. I want to have these errors logged in Octopus but for the script to keep running. For some reason, no matter what I try Octopus stops on the first SQL Script to error.

Running the same script in powershell itself, the script will continue running all the scripts. I have looked far and wide for solutions, but I’m stuck. I just want the octopus deployment to keep running through all the sql scripts and just log the SQL Error output somewhere (either octopus or a file)

logs attached below, specific powershell info here:

function Execute-SqlQuery($fileName) 
{
    Write-Host "Executing scripts in file '$fileName'"

    Invoke-Sqlcmd -ConnectionString $paramConnectionString -InputFile $fileName -OutputSqlErrors $true -QueryTimeout $paramCommandTimeout 2>&1 | Out-Null

    Write-Host "Executed '$fileName'" 
}

Write-Host "Executing scripts in folder '$paramPathToScripts'"

$databaseFolders = Get-ChildItem $paramPathToScripts | Sort-Object {
    [version] $_.Name.replace('Database - ','')
}

$scriptError = $false

foreach($f in $databaseFolders) {
    Write-Host "Upgrading from Folder $f"

    $scripts = Get-ChildItem $f.FullName *sql

    foreach($s in $scripts) {
        Write-Host "Executing: $s"
        Execute-SqlQuery -fileName $s.FullName
        if($? -ne 0) {
            $scriptError = $true
        }
    }
}

if($scriptError) {
    Write-Host "There was script errors"
    exit(0)
}
else {
    Write-Host "All scripts executed succesfully"
    exit(0)
}

The Problem output I get is:

Executing scripts in file 'F:\UCTemp\Octopus\databasePackage4.1.0-from-4.0.0\Database - 4.1.0\0.0.a Key Alignment.sql' 
December 12th 2018 15:45:59
Info
Executed 'F:\UCTemp\Octopus\databasePackage4.1.0-from-4.0.0\Database - 4.1.0\0.0.a Key Alignment.sql' 
December 12th 2018 15:45:59
Info
Executing: UC_V3_Additional Indexes.sql 
December 12th 2018 15:45:59
Info
Executing scripts in file 'F:\UCTemp\Octopus\databasePackage4.1.0-from-4.0.0\Database - 4.1.0\UC_V3_Additional Indexes.sql' 
December 12th 2018 15:45:59
Info
Executed 'F:\UCTemp\Octopus\databasePackage4.1.0-from-4.0.0\Database - 4.1.0\UC_V3_Additional Indexes.sql' 
December 12th 2018 15:45:59
Info
Executing: UC_V4_0_0_1066.various changes.sql 
December 12th 2018 15:45:59
Info
Executing scripts in file 'F:\UCTemp\Octopus\databasePackage4.1.0-from-4.0.0\Database - 4.1.0\UC_V4_0_0_1066.various changes.sql' 
December 12th 2018 15:45:59
Error
Invoke-Sqlcmd : This script 1066.various changes.sql has already been run on  
December 12th 2018 15:45:59
Error
this database  
December 12th 2018 15:45:59
Error
 Msg 50000, Level 18, State 1, Procedure , Line 4. 
December 12th 2018 15:45:59
Error
At C:\Octopus\Tentacle\Work\20181212044554-563-1154\Script.ps1:17 char:5 
December 12th 2018 15:45:59
Error
+     Invoke-Sqlcmd -ConnectionString $paramConnectionString -InputFile ... 
December 12th 2018 15:45:59
Error
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 
December 12th 2018 15:45:59
Error
    + CategoryInfo          : InvalidOperation: (:) [Invoke-Sqlcmd], SqlPowerS  
December 12th 2018 15:45:59
Error
   hellSqlExecutionException 
December 12th 2018 15:45:59
Error
    + FullyQualifiedErrorId : SqlError,Microsoft.SqlServer.Management.PowerShe  
December 12th 2018 15:45:59
Error
   ll.GetScriptCommand 
December 12th 2018 15:45:59
Error
 
December 12th 2018 15:45:59
Fatal
The remote script failed with exit code 1 

Lastly if I change -OutputSQLErrors to $false in powershell, the issue goes away and it will run all the scripts successfully. However obviously this does’t let me log the sql errors anywhere.

Octopus Version: Octopus v2018.9.15

uc-process.json (81.7 KB)

OctopusTentacle.txt (33.9 KB)

ServerTasks-563.log.txt (113.9 KB)

[nskerl (Nathan Skerl)]

Check out the section on error handling here:

Octopus Documentation - Octopus Deploy

You might just need to set set $ErrorActionPreference to Continue at the top of your script.

[Michael_Compton]

Hi,

Thanks for reaching out with the issue, and thanks Nathan for the answer!

The docs Nathan linked to are the right place to read about errors, logging and scripts. If you set $ErrorActionPreference to continue, you’ll get some nice red errors when viewing the script result, but by also setting the exit code to 0 the step itself with be green.

Hope that’s sorts out your issue. If not, please get back in touch.

Michael