How can I make a win32 command (sqlcommand) print its output to the console in PowerShell

Question

I have a script that eventually calls this line

& sqlcmd.exe -S $DbHost -d $DbSchema -Q "do some crazy db change here"  

Where "do some crazy db change here" will eventually be replaced by a dynamic bit of SQL/script.

When I run this whether it succeeds or not I see no output from sqlcmd.exe in my console. For user feedback I'd like to pipe this in real time to the console. How could I do that?

Answer 1

Here's how I have done it in the past, it makes use of events from StandardOut and StandardError. As these are executed asynchronously you are not entirely in control of the output (in terms of when it happens), but it should be close to what you need.

$SqlCommandArguments = @()
$SqlCommandArguments += "-S $DbHost"
$SqlCommandArguments += "-d $DbSchema"
$SqlCommandArguments += "-Q `"do some crazy db change here`""
ExecuteProcess -FileName "SqlCmd.exe" -CommandArguments $SqlCommandArguments -Verbose:$VerbosePreference

function ExecuteProcess
{
    [cmdletbinding()]
    param
    (
        [string]$FileName,
        [string[]]$CommandArguments
    )

    Write-Verbose "$FileName $CommandArguments"

    $startInfo = New-Object System.Diagnostics.ProcessStartInfo
    $startInfo.FileName = $FileName
    $startInfo.Arguments = $CommandArguments
    $startInfo.RedirectStandardError = $true
    $startInfo.RedirectStandardOutput = $true
    $startInfo.UseShellExecute = $false
    $startInfo.CreateNoWindow = $true

    $process = New-Object System.Diagnostics.Process
    $process.StartInfo = $startInfo

    $eventOutputDataReceived = Register-ObjectEvent -InputObject $process -EventName OutputDataReceived -MessageData $VerbosePreference -Action { 
        if ($($EventArgs.data))
        {
            Write-Verbose $EventArgs.data -verbose:$event.MessageData
        }
    }

    $global:standardError = New-Object System.Text.StringBuilder
    $eventErrorDataReceived = Register-ObjectEvent -InputObject $process -EventName ErrorDataReceived  -Action { 
        if ($($EventArgs.data))
        {
            $global:standardError.Append("$($EventArgs.data)`r`n")
            Write-Warning -message $EventArgs.data 
        }
    } 

    $process.Start() | Out-Null

    $process.BeginOutputReadLine()
    $process.BeginErrorReadLine()  

    $process.WaitForExit()


    Unregister-Event -SourceIdentifier $eventOutputDataReceived.Name 
    Unregister-Event -SourceIdentifier $eventErrorDataReceived.Name 

    $exitCode = $process.ExitCode
    if ($exitCode -ne 0) 
    {
        Write-Error $global:standardError.ToString()
        throw "$FileName Failed!"
    }
}