How does one get the output from |Write-SqlTableData

Question

I'm using

## Import CSV into SQL
$CsvData = Import-Csv $CsvPath -Delimiter $CsvDelimiter 

$CsvData | Write-SqlTableData `
  -ServerInstance $ServerName `
  -DatabaseName $DatabaseName `
  -SchemaName $TableSchema `
  -TableName $TableName `
  -Force  `
  -ConnectionTimeout(0) `
  -OutVariable $Out `
  -iv $Info 

I'm trying to get the error message and or the return codes into a variable. None of usual ways seem to work .

All of the output variables specified above - $out and $info - seem to always be empty.

Answer 1

Mathias R. Jessen has provided the crucial pointer:

The self-chosen variable you designate as the target of one of the common -*Variable parameters, such as the common -OutVariable (-ov) parameter, -ErrorVariable (-ev), and -InformationVariable (-iv), must be specified by name only, i.e. without the $ prefix:

Therefore, in order to capture success output in variable $out, use -OutVariable out, not -OutVariable $out.

Analogously, use -iv info (-InformationVariable info), not -iv $info, to capture the information-stream output.

To capture (non-terminating) errors in, say, variable $err, use -ErrorVariable err
(or -ev err).

---

What -OutVariable $out, for instance, does is to use the value of variable $out as the name of the target variable, and if $out isn't defined, the reference evaluates to $null, which is the same as not specifying a target variable for -OutVariable, i.e. the parameter has no effect - which is what you saw.

Note that PowerShell has no concept of return codes; PowerShell commands produce output via its system of output streams.

Additionally, there is an abstract success indicator in the form of the automatic $? variable, which contains $false if at least one error occurred in the most recently executed command, and $true otherwise.

Error details must be gleaned from the error records (objects) emitted via the error output stream (whose number is 2), and are also logged session-wide in the automatic $Error variable.

---

Optional reading: How to capture output from commands that don't support common parameters:

• What the common -*Variable parameters do is to capture a given output stream's content in a self-chosen variable without interfering with that output stream; that is, the output stream's content is still passed through, and the target variable also captures it, for later use.

• However, use of common parameters is only an option if the command is either a cmdlet, or a (cmdlet-like) advanced function or script.

---

For other commands except external (native) programs, you can wrap the call in a Invoke-Command call and use the latter's common parameters; e.g.:

# Define a simple (non-advanced) function that emits both success 
# and error output.
function SimpleFunc {
  'hi!'             # same as: Write-Output 'hi!'
  Write-Error 'err' # emit a non-terminating error
}

# Wrap the call in Invoke-Command and use the latter's common parameters.
# The output streams are passed through, and the success-output / error stream
# content is recorded in $out / $err
Invoke-Command -OutVariable out -ErrorVariable err { SimpleFunc }

Self-chosen variable $out now contains 'hi!' and self-chosen variable $err contains an error record with message 'err'.

---

For external (native) programs, there is only a suboptimal solution:

• Unless the output streams of external programs - stdout and stderr - are explicitly redirected or captured, PowerShell passes them straight through to the host (terminal). Therefore, the Invoke-Command solution above wouldn't capture them.

  • Arguably, however, the use of -OutVariable should implicitly act like the request to capture output from external programs too; see GitHub issue #5758 for a discussion.

• You can force this capturing to happen simply by piping the external-program call to Write-Output (which effectively just relays the output lines), but only stdout output is by default passed through the pipeline.

  • Note that this may surface character-encoding issues that wouldn't necessarily surface in direct-to-host output: capturing involves decoding the output streams based on [Console]::OutputEncoding, which must match the actual encoding used by the external program - see this answer for background information.

• In order to also capture stderr output, you must redirect it into the success stream, using 2>&1, but this invariably means that you can only capture the combination of the stdout and stderr streams, in a single variable, passed to -OutVariable

  • However, because PowerShell records stderr lines wrapped in [ErrorRecord] instances, you can separate the stream output afterwards, using the intrinsic .Where() method, as shown below.

# Capture the *combined* stdout and stderr output in $outAndErr
Invoke-Command -OutVariable outAndErr { 
  cmd /c 'ver & dir nosuch' 2>&1 | Write-Output
}

# Now split the captured lines into those that come from stdout vs. stderr.
$out, $err = $outAndErr.Where({ $_ -is [string] }, 'Split')

Note:

• If you don't need to also pass the output streams through, you can capture the output streams directly, as follows:

  $out, $err = (
    cmd /c 'ver & dir nosuch' 2>&1
  ).Where({ $_ -is [string] }, 'Split')
  

• Possibly providing a mechanism analogous to -OutVariable for external-program calls has been suggested in GitHub issue #4332, with syntax such as 2>variable:err