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

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.

CodePudding user response：

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

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