Lastexitcode after invoke-sqlcmd in powershell process step fails

Legacy Uncategorised
#1

I have a powershell process step to run a series of scripts in a directory using invoke-sqlcmd as below.
The idea is to allow all scripts to run and fail the step if one failed.
On the target machine the powershell (variables expanded) it runs fine.
However when run in Octopus $lastexitcode does not appear to work properly after the invoke-sqlcmd line.
$exitcode = $lastexitcode does not set $exitcode to anything, hence every script logs as failed even when they ran properly.
Also the logged error has no value for $exitcode.

Any help would be greatly appreciated.

Run .sql scripts from SQL Views folder

foreach ($f in Get-ChildItem -path “#{PathWKSQLViews}” -Filter *.sql | sort-object)
{
“rnRunning script " + $f.name.split(”.")[0] >> $logfile
invoke-sqlcmd -InputFile $f.fullname -Verbose -OutputSqlErrors $true 4>&1 2>&1 >> $logfile
$exitcode = $lastexitcode
if ( $exitcode -ne 0 )
{
# allow later commands to run, but note error for exit
$failed = $true
# note error
"Error Running script " + $f.fullname + “rnExitCode was $exitcode” >> $logfile
}
else
{
“Script completed OK.rn” >> $logfile
}
}

return failure if any script failed

if ( $failed )
{ exit (1) }
else
{
exit (0)
}

#2

Hi Tom,

Thanks for reaching out. This is more a Powershell question than an Octopus one, but I’m gonna give you a hand anyways :slight_smile: . The below link does a good way explaining what $LastExitCode actually does, and why (in your case) you should use $? instead

http://techibee.com/powershell/what-is-lastexitcode-and-in-powershell/1847

Another good thing to do instead of relying on these variables, would be to use Try/Catch/Finally for all your error handling.

Hope that helps!
Dalmiro

#3

Many thanks - basic Powershell issue. $? works properly with invoke-sqlcmd, but $lastexitcode does not.

#5