CODEWITHSUNDEEP
powershellerror-handlingsqlcmd
Obsidian Phoenix
Obsidian Phoenix

Reputation: 4155

Executing command doesn't result in script exception

We have a powershell script that deploys a database script. However, if the database script fails, the output doesn't throw exceptions to the powershell script.

Below is an example of the .ps1 file:

function Publish-DatabaseProject 
{
    sqlcmd -S . -b -v DatabaseName=Integration -q "alter table xx add test Varchar(10)"
}

function Add-Timestamp {
    process {
        if ($_.GetType() -eq [string]) {
            "[$(Get-Date -Format o)] $_"
        } else {
            $_
        }
    }
}

function Write-LogFile {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory=$true)] [string] $Path,
        [Parameter(Mandatory=$true, ValueFromPipeline=$true)] [object[]] $InputObject
    )

    begin {
        $root = Split-Path -Path $Path -Parent
        if ($root -and !(Test-Path -Path $root)) { New-Item -Path $root -Type Directory 
                              | Out-Null }
    }

    process {
        $InputObject |
            Add-Timestamp |
            Tee-Object -File $Path -Append
    }
}

Publish-DatabaseProject -ErrorVariable DeployError 2>&1 |
    Write-LogFile -Path "C:\output.log"

if ($DeployError -and $DeployError.Count -ne 0) 
{
    Write-Output "Failed"
} else 
{
    Write-Output "Succeeded"
}

The query in question is executing against a non-existent table. The text output shows:

[2015-12-11T14:42:45.1973944+00:00] Msg 4902, Level 16, State 1, Server ABDN-DEV-PC1, Line 1

[2015-12-11T14:42:45.2053944+00:00] Cannot find the object "xx" because it does not exist or you do not have permission s.

Succeeded

I am expecting the last line to read: Failed.

If you run the sqlcmd line on its own, and follow it up with $LastExitCode, it does spit out a non-zero exit code.

> sqlcmd -S . -b -v DatabaseName=Integration -q "alter table xx add test Varchar(10)"
Msg 4902, Level 16, State 1, Server ABDN-DEV-PC1, Line 1
Cannot find the object "xx" because it does not exist or you do not have permissions.
> $LastExitCode
1

For various reasons, we cannot use Invoke-SqlCmd, and need to stick with SQLCMD.exe.

How can we make exceptions from within SQLCMD bubble out correctly to the calling script?

Upvotes: 1

Views: 211

Answers (1)

FoxDeploy
FoxDeploy

Reputation: 13537

Your -ErrorVariable DeployError statement would only get triggered if the Publish-DatabaseProject cmdlet itself fails to execute. As that function is mostly a wrapper around sqlcmd.exe, there isn't any intelligence to bubble up this error. We can wrap it though by using the $LastExitCode automatic variable.

function Publish-DatabaseProject 
{
    sqlcmd -S . -b -v DatabaseName=Integration -q "alter table xx add test Varchar(10)"
    if ($LastExitCode -ne 0)
       {
    Write-error $LastExitCode

    throw $LastExitCode
      }
}

Now, PowerShell will catch this error from the .exe, and you can use -ErrorVariable.

Update

So, since you want to keep running and not abandon ship when enountering an error, we need to wrap your Publish-DataBaseProject function with a try{}catch{} block, to catch the error we're generating, without stopping execution.

try {Publish-DatabaseProject -ErrorAction STOP -ErrorVariable DeployError 2>&1 |
    Write-LogFile -Path "C:\output.log" }


 catch{
      Write-Output "Current job $($_), failed"
      Write-Output "Exception $($Error.ExceptionID)"
      }

Now we're properly generating an exception from a CMD, bubbling it up through our Function, and handling it as if it were a normal function, all using Native PowerShell. I believe this is what you were seeking to do. If not, post a gist of the whole script and I'll help you in a more targeted fashion.

Upvotes: 3

Related Questions