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?

Do you know for a fact that the exe is supposed to send output to stdout? Maybe it's designed to be silent. If it returns an error code, you could assign the output of the exe to a variable, then print the value of the variable. — David, Dec 06 '13 at 00:37
@David Sqlcmd.exe is not a silent one, as it is the command line tool for Sql Server. You have a point though, if DML statements do not provide output, there isn't much to be printed. — vonPryz, Dec 06 '13 at 11:17
Are you required to use `sqlcmd.exe`? There are ways of doing this natively with PowerShell. — alroc, Dec 06 '13 at 13:42

score 0 · Answer 1 · answered Dec 06 '13 at 12:25

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!"
    }
}

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

1 Answers1

Linked