Powershell invoke-sqlcmd Printing The Wrong Output

I have a SQL query that is running as expected however when I try to use it in PowerShell 'Invoke-SqlCmd' module, the output comes out different than when querying the database. I noticed that there are quite a few questions regarding this module but I couldn't find one that is applicable to my case.

Query:

    $SQLServer = "localhost"
    $query = "SELECT Groups.[Name] AS AGname FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id WHERE primary_replica = @@Servername"

    $HAGName = Invoke-Sqlcmd  -query $query -ServerInstance $SQLServer -Database 'database' 

    if ($HAGName = !$null) {
        
        write-host "Availability group name is $HAGName"
        exit 0
    }
    else {

        write-host "Failed to retrieve High Availability group name = [$HAGName]"
        exit 1
    }

Output in PowerShell: 'Availability group name is True'

Like I mentioned, when querying SQL Server directly I get the correct output. I tried using the 'OutputAs' switch but it didn't help.

Any help will be greatly appreciated.

Solution

All the pointers are in the comments on the question, but let me break it down systematically:

!$null is always $true in PowerShell: ! / -not, the logical NOT operator coerces $null to a Boolean, and since [bool] $null is $false, ! $null is $true.
$HAGName = !$null, due to using =, the assignment operator, therefore assigns $true to variable $HAGName.
- To instead perform an equality comparison, use -eq, the equality operator.

Therefore, $null -eq $HAGName is what you meant to use (placing the $null on the LHS, for robustness - see the docs).

However, given PowerShell's implicit to-Boolean coercion rules (see the bottom section of this answer), you could simplify to if ($HAGName) { ... } in this case.

Therefore, a more PowerShell-idiomatic reformulation of your code is:

$SQLServer = 'localhost'
$query = 'SELECT Groups.[Name] AS AGname FROM sys.dm_hadr_availability_group_states States INNER JOIN master.sys.availability_groups Groups ON States.group_id = Groups.group_id WHERE primary_replica = @@Servername'

$HAGName = Invoke-Sqlcmd -Query $query -ServerInstance $SQLServer -Database database

if ($HAGName) {
    Write-Verbose -Verbose "Availability group name is: "
    # Output the System.Data.DataRow instance as-is,
    # which also results in proper for-display formatting.
    # If you just want the value of the .AGname property (column), use
    #  $HAGName.AGname instead.
    $HAGName 
    exit 0
}
else {
    Write-Warning "Failed to retrieve High Availability group name."
    exit 1
}

Note:

The success case implicitly outputs the result, to the success output stream.
- Write-Host is typically the wrong tool to use, unless the intent is to write to the display only, bypassing the success output stream and with it the ability to send output to other commands, capture it in a variable, or redirect it to a file. To output a value, use it by itself; e.g., $value instead of Write-Host $value (or use Write-Output $value, though that is rarely needed); see this answer
- I've used a Write-Verbose call (whose output is quiet by default, here I've used -Verbose to force it to show) to provide optional supplemental / status information.
- $HAGName now (implicitly) outputs the [System.Data.DataRow] instance returned by the Invoke-SqlCmd call as-is, which also results in proper display formatting - such instances do not stringify meaningfully when used in an expandable (interpolating string); they unhelpfully stringify to their type name, i.e. to verbatim System.Data.DataRow.
  - However, if you access a specific property (column) of the row, its value may stringify meaningfully, depending on its data type; in your case: `"Availability group name is $($HAGName.AGname)"
  - To include the usual for-display formatting inside a string - use something like "Availability group name is $($HAGName | Out-String)"