Export and Email SQL View as CSV Spreadsheet Using Powershell

I’ve been looking for an easy way to export and email the query results from a SQL View as a Spreadsheet (CSV). I’ve finally found a decent solution on Windows using tools provided with either the operating system or the database server. Previously I’ve used other tools like Python to accomplish this but this latest combination of built-in tools gets the job done nicely without added dependencies.

The primary feature needed for this was introduced with Microsoft SQL Server 2008 as the sqlps.exe utility. It has since been enhanced in SQL Server 2012 to be an importable Powershell Module also known as “SQLPS.” Here is a brief overview of the steps used to accomplish this task.

  • Review your query results (usually in SQL Server Management Studio) to make sure a simple query of a View or Table(s) will return the necessary data formatted to your specifications.
  • Create a simple Powershell script that runs the desired query and saves the output to a unique CSV file then attaches and mails the file using the Send-MailMessage cmdlet. Save this scripts as a *.ps1 file.
  • Verify that your Powershell ExecutionPolicy permits you to run scripts. If you have local admin privileges, you might want your LocalMachine policy set to RemoteSigned. Review with “Get-ExecutionPolicy -List”
  • If you want this script to run on a schedule, create a Task within Windows Task Scheduler – make sure that this task runs under an account that has enough privileges to run your SQL query and save the output file to the desired folder.

Here is some sample code for a Powershell script that demonstrates the SQL Server 2008 (sqlps.exe) capabilities to make this quick CSV solution a reality. If you have SQL Server 2012, the script will be a little cleaner because you can use “Import-Module SQLPS” followed by direct use of the “Invoke-Sqlcmd” cmdlet.

$dirName = "C:\Users\YOURUSERNAME\Documents\Powershell"
$fileName = $(Get-Date -Format "yyyy-MM-dd_HHmm") + "_YOURFILENAME.csv"
$outFile = Join-Path $dirName $fileName

SQLPS -Command {
	Param ($myPath)
	$mySQL = "SELECT * FROM yourViewName ORDER BY someColumn, anotherColumn"
	Invoke-Sqlcmd -Database "yourDBName" -Query $mySQL | `
	Export-Csv -NoTypeInformation -Path $myPath -Encoding UTF8
} -Args $outFile

Send-MailMessage `
	-To "yourEmail@yourDomain.com" `
	-Cc "alternateEmail@yourDomain.com","alternate2@yourDomain.com" `
	-Subject "Put Email Subject Here" `
	-From "yourEmail@yourDomain.com" `
	-Body "Put Email Body Here." `
	-SmtpServer "yourSMTPServer.yourDomain.com" `
	-Attachments $outFile

This post was inspired in part by the following:

Advertisements

About notesbytom

Keeping technology notes on WordPress.com to free up my mind to solve new problems rather than figuring out the same ones repeatedly :-).
This entry was posted in Database and tagged , , . Bookmark the permalink.

One Response to Export and Email SQL View as CSV Spreadsheet Using Powershell

  1. Pingback: Task Scheduler PowerShell Script Log File | Notes by Tom

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s