Should I use PowerShell subexpression when Invoke-Sqlcmd in Azure runbook?

Question

We are using Azure Automation powershell runbook for Azure Sql Maintenance similar as it’s described in https://www.2azure.nl/2020/07/28/how-to-use-azure-automation-to-maintain-sql-indexes-and-statistics/ (and in Azure Automation Powersell Runbook fails: 'Invoke-Sqlcmd' is not recognized as the name of a cmdlet answer)

$SQLOutput = $(Invoke-Sqlcmd -ServerInstance $AzureSQLServerName 
  -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password
  -Database $AzureSQLDatabaseName -Query $sql 
  -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1
Write-Output $SQLOutput

But we found, that in case of error(in particular when username was wrong) the job ended with success, even if the error was logged in the output. Does it mean that subexpression $(command) silently catch exceptions? It is not mentioned in https://ss64.com/ps/syntax-operators.html.

Another issue with saving subexpression to $SQLOutput is that we can see output only after $(Invoke-Sqlcmd) is completed and not during process. If the $(Invoke-Sqlcmd) took over 3 hours, job stopped by Azure and we could not see what was processed and what wasn’t.

Should I not use subexpression at all and call Invoke-Sqlcmd directly?

Invoke-Sqlcmd -ServerInstance $AzureSQLServerName 
  -Username $Cred.UserName -Password $Cred.GetNetworkCredential().Password 
  -Database $AzureSQLDatabaseName -Query  $sql 
  -QueryTimeout 65535 -ConnectionTimeout 60 -Verbose) 4>&1

Will it has any disadvantages compare to subexpression?

Answer 1

To report error during Invoke-Sqlcmd I’ve added 2 parameters

 -ErrorAction Stop   

From Powershell Try Catch invoke-sqlcmd

 -AbortOnError  

From Error detection from Powershell Invoke-Sqlcmd not always working?

Still didn’t confirm, is any benefits in saving subexpression to $SQLOutput