0

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?

aasenomad
  • 405
  • 2
  • 11
  • 1
    Does this answer your question? [Error detection from Powershell Invoke-Sqlcmd not always working?](https://stackoverflow.com/questions/23471600/error-detection-from-powershell-invoke-sqlcmd-not-always-working) – Thom A May 18 '22 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 May 18 '22 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](https://learn.microsoft.com/en-us/powershell/scripting/whats-new/cmdlet-versions). – Aaron Bertrand May 18 '22 at 16:21

1 Answers1

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

DVF
  • 1