I’m sure there’s a better way of doing this, but here’s how I captured the output of SQL’s bcp.exe in order to email it and the CSV that we were creating automatically on a schedule overnight.

I ended up piping the executable to a temp file, and then grabbing the contents of that same temp file to populate the email body. Here are the lines in question:

&$exe $arg1 $arg2 $arg3 $arg4 $arg5 $arg6 > c:tempout.txt

$bcpOutput = Get-Content c:tempout.txt | Out-String
Remove-Item c:tempout.txt

Here’s the script in its entirety:

# This needs to be set, otherwise Send-MailMessage doesn’t have a server to send through$PSEmailServer ="mailserver.local"
$PSEmailServer ="mailserver.local"

$emailRecipient = "Daniel <daniel@contoso.com>"

# Some date and filename related stuff. This part's not important
$d = Get-Date
$yesterday = $d.AddDays(-1)
$filepath = "C:ExportsSQL Export"
$filename = "SQL-DailyExport-{0}.{1}.{2}.csv" -f $yesterday.Day,$yesterday.Month,$yesterday.Year
$fullpath = $filepath + $filename

# Path to the executable
$exe = "C:Program FilesMicrosoft SQL Server90ToolsBinnbcp.exe"

# arguments, as required
$arg1 = "DBName..ViewName"
$arg2 = "out"
$arg3 = $fullpath
$arg4 = "-c"
$arg5 = "-t,"
$arg6 = "-T"

# Run the executable with the arguments, and pipe the STDOut output to a text file
&$exe $arg1 $arg2 $arg3 $arg4 $arg5 $arg6 > c:tempout.txt

# An intro line for the body of the email
$bcpOutput = "SQL daily export process information: `r`n"

# Get the contents of the temporary file that we created earlier
$bcpOutput += Get-Content c:tempout.txt | Out-String

# Remove the temporary file
Remove-Item c:tempout.txt

# Build up our subject line. This part's not important
$subjectText = "SQL export for {0}/{1}/{2}" -f $yesterday.Day,$yesterday.Month,$yesterday.Year

# Append some more information to the end of the body text. Again, not important
$bcpOutput += "`r`nThe output file is attached, and can also be found in sqlExports"

# Send the email with the file attached and the body text as we've built it up
Send-MailMessage -From "SQL Export <Process@sql.server>" -To $emailRecipient -Subject $subjectText -Body $bcpOutput -Attachments $fullpath

This results in an email that contains the output from bcp.exe as well as the actual SQL export file attached to the email.