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:\temp\out.txt $bcpOutput = Get-Content c:\temp\out.txt | Out-String Remove-Item c:\temp\out.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:\Exports\SQL 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 Files\Microsoft SQL Server\90\Tools\Binn\bcp.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:\temp\out.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:\temp\out.txt | Out-String
# Remove the temporary file
Remove-Item c:\temp\out.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 \\sql\Exports"
# 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.
