Try/Catch in powershell running sql script

Question

I'm new to powershell and trying to use the try/catch command in handling the error message.

$Output = Invoke-SqlCmd -ServerInstance $dbinstance -username $dbusername -password $dbpassword -Database  $dbname -Query $Query

$isconnected = ''

if ($Output.uri -eq '0' ) {
$isconnected = $true
Write-Host -Foreground Green $isconnected

}else  {
$isconnected = $false
Write-Host -Foreground Red $isconnected
}

This is the error I get, but the result remains true even though the outcome should be error because I'm trying to figure out how to catch the error.

Santiago Squarzon · Accepted Answer · 2022-06-02 03:03:01Z

4

If you want to implement a Try / Catch in your script you would need to put your code to query the Database inside the Try block and how you want to handle the error in the Catch block:

try {
    $param = @{
        ServerInstance = $dbinstance
        Username       = $dbusername
        Password       = $dbpassword
        Database       = $dbname
        Query          = $Query
    }
    $output = Invoke-SqlCmd @param -ErrorAction Stop
    Write-Host 'Connected' -Foreground Green
    # rest of your code goes here
}
catch {
    Write-Warning $_.Exception.Message
}

You might also benefit from Splatting.

$_ in the context of the Catch block represents the caught error, which is an instance of ErrorRecord. You might want to check the other properties aside from Exception.

edited Jun 2, 2022 at 3:03

answered Jun 2, 2022 at 2:41

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Try/Catch in powershell running sql script

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related