Running invoke-sqlcmd and storing the print output into a variable

Question

I am running a SQL stored procedure via invoke-sqlcmd, having read the posts on Powershell Invoke-Sqlcmd capture verbose output and How to run a PowerShell script with verbose output?.

I have a working solution, however I am struggling with a tweak so that the solution can be complete. It currently prints onto the console, I only want the output to be in a variable and no console output. I have also toyed with the idea of setting $VerbosePreference = "SilentlyContinue" to no avail.

Here is what I have.

$null = invoke-sqlcmd -ServerInstance "DB_Server" -Database "DB" -Query "sp_storedProcedure_with_prints" -OutputSQLErrors $true -ErrorAction Stop -ErrorVariable error_var -verbose 4>&1 | Tee-Object -variable script_output_variable

It works, it does what I want it to do which is to save the script output in the variable $script_output_variable what is happening now is that the hundreds of lines that the stored procedure outputs is not required, especially when the script runs on thousands of servers.

Answer 1

You're redirecting verbose output to the success output stream (4>&1), which means a variable assignment ($var = ...) is sufficient to capture both the success output and the verbose output without producing display output.

By contrast, the very purpose of Tee-Object is to also produce display output / pass success output through.

Therefore the solution is not to use Tee-Object and to directly assign to the variable of interest:

$script_output_variable = invoke-sqlcmd -ServerInstance "DB_Server" -Database "DB" -Query "sp_storedProcedure_with_prints" -OutputSQLErrors $true -ErrorAction Stop -ErrorVariable error_var -verbose 4>&1

---

As an aside: In the event that you do want to capture output in a variable while also passing it through, enclosing an assignment in (...) is a simpler alternative to using Tee-Object; e.g.,
($var = Get-Date) is equivalent to - and faster than -
Get-Date | Tee-Object -Variable var

If your command produces many output objects and you want to retain streaming behavior, use the common -OutVariable (-ov) parameter; e.g.,
Get-ChildItem -ov var | ...

Tee-Object:

• is primarily useful when pass-through data is to also be captured in a file, e.g.
  Get-Date | Tee-Object -LiteralPath out.txt

• in PowerShell (Core) 7+ you may also use it to pass data through to the display - see this answer.