How to catch sql query error in powershell

Question

I have a query like this in my PowerShell Script

try{
    $Query = "SELECT ID / 0 FROM Tables1"
    Invoke-Sqlcmd -Query $Query -ServerInstance $Global:Server -Database $Global:Database
}
catch {
    "error when running sql $Query "
    $error=  $_
}

The problem I'm having is when I run this query in Azure Data Studio then I get an error and that look fine but

SELECT ID / 0 FROM Table1

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

when I run the query inside my trycatch block ins my PowerShell script then some reason it's not outputting the error even though I wrap it inside trycatch block.

So I'm just wondering how could I catch the sql statement errors in my PowerShell Script?

Does this answer your question? Error detection from Powershell Invoke-Sqlcmd not always working? — Thom A
– Thom A ♦, Commented May 18, 2022 at 15:56
If your intent is to rethrow, add throw as the last command in your catch block. If you don't want to rethrow, just print the error message (e.g. Write-Host $_.Exception.Message). — Dan Guzman
– Dan Guzman, Commented May 18, 2022 at 15:59
Are you really still using PowerShell 3.0? Why? It's over 10 years old and not even listed on the history pages anymore. — Aaron Bertrand
– Aaron Bertrand, Commented May 18, 2022 at 16:21

DVF · Accepted Answer · 2023-05-11 13:01:50Z

0

This is due to your -command- not failing (invoke-sql...) therefor there is nothing to "catch" according to powershell , because the command worked.

Sure , the SQL query that got fired , that one failed , but it fails inside SQL server , and returns a msg to your powershell command.

As far as powershell is concerned ,everything is honky-dorey. (im trying to find the solution myself , as i dont have it yet , but likely will be somewhere in the ballpark of checking return msg for 1/0 or -contains error

answered May 11, 2023 at 13:01

DVF

1

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

Collectives™ on Stack Overflow

How to catch sql query error in powershell

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related