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.