TechnoGist

Today I was looking at producing a report from a SQL2005 database and exporting the SQL data into a CSV file. Once completed I would be sending the CSV file to a member of my team via Email. As this is a common request I wanted to automate this with PowerShell.

I had posted previously on basic database connectivity with PowerShell here and using that code as a basis I wrote a PowerShell script that connects to the SQL database and extracts the data into an SQLDataAdapter. The required table is selected and imported into a hash table, and then exported to a CSV file. This file is then attached to an email using the .NET SMTP Mail object and sent on its way via SMTP.

Download Script at PoshCode.org

Code:

#Connection Strings
$Database = "Database"
$Server = "SQLServer"
#SMTP Relay Server
$SMTPServer = "smtp.domain.com"
#Export File
$AttachmentPath = "C:\SQLData.csv"
# Connect to SQL and query data, extract data to SQL Adapter
$SqlQuery = "SELECT * FROM dbo.Test_Table"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null
#Populate Hash Table
$objTable = $DataSet.Tables[0]
#Export Hash Table to CSV File
$objTable | Export-CSV $AttachmentPath
#Send SMTP Message
$Mailer = new-object Net.Mail.SMTPclient($SMTPServer)
$From = "email1@domain.com"
$To = "email2@domain.com"
$Subject = "Test Subject"
$Body = "Body Test"
$Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)
$Msg.IsBodyHTML = $False
$Attachment = new-object Net.Mail.Attachment($AttachmentPath)
$Msg.attachments.add($Attachment)
$Mailer.send($Msg)

Hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.

As I have been migrating over my scripts from VBScript to PowerShell I have had to convert over several scripts.
One example of this is a section of code that I use to pull data from Tables in SQL databases.
This code assumes you are using SQL authentication credentials.

#Authentication Strings
$account = “user”
$pw = “password”
$table = “table or view”
$db = “sql database”
$server = “sql server”

# Specify SQL Query
$SqlQuery = “Select * From $table”

# Create SQL connection object
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection

#Set the ‘Connection String’ property
$SqlConnection.ConnectionString = “Server = $server; Database = $db;user = $account; password = $pw”

# Create SQLCommand object
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

#Set SQLCommand Text property (SQL Query)
$SqlCmd.CommandText = $SqlQuery

#Set the Connection property
$SqlCmd.Connection = $SqlConnection

#Create SQL Adapter repository
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

#Run SQL Connection
$SqlAdapter.SelectCommand = $SqlCmd

#Create SQL DateSet object
$DataSet = New-Object System.Data.DataSet

#Populate $DataSet with Connection results
$nRecs = $SqlAdapter.Fill($DataSet)
$nRecs | Out-Null

#Populate Table object with required Table
$ objTable = $DataSet.Tables[0]

#Parse through the rows
ForEach($objRow In $objTable)
{
#Echo Column Names
Write-Host $objRow.column
}

In the above code you will need to supply replacements for, $account, $pw, $table, $db and $server. Depending on the database you are connecting to and the structure of the table you will want to modify the contents of $SqlQuery (SQL query string) and the $objRow.column would need to be changed to a valid column name for the table you are querying against.

I hope this helps.

All information is provided on an AS-IS basis, with no warranties and confers no rights.