Powershell - Invoke-SQLCmd: how to check results

Question

I am creating a script that will execute tSQLt Unit tests on the Dev server before a deployment o QA can proceed - this is part of Continuous Integration...

here is my Powershell script:

param(
[Parameter(Mandatory=$true)][string]$DatabaseName
, [Parameter(Mandatory=$true)][string]$UserName
, [Parameter(Mandatory=$true)][string]$Password

)

$filelocation = "c:\jenkinsdrops"
$DatabaseServer = "Server\DEV"

Invoke-Sqlcmd `
-Query "EXEC tSQLt.RUNALL" `
-ServerInstance $DatabaseServer `
-Database $DatabaseName `
-U $UserName `
-Password $Password `
-Verbose 4>"$Filelocation\$DatabaseName tSQLt Report.txt"`

the output in the console is:

and the (verbose) results output to the text file are like this:

Before we trigger the next step we have to ensure that all the unit tests passed, so the word Failure cannot appear in the output results file, and the word failed cannot be in the console output.

How can I check for that, or is there a better, more correct way to do this?

Answer 1

Just assuming from your console output and can't test it currently but a try-catch block could work.

try {
    Invoke-Sqlcmd ...
}
catch {
    # Output a error and exit script?
}

eventually you need to add a -ErrorAction Stop to the Invoke-Sqlcmd.