Export Data From SQL Server Table and Email an Attachment



#Connection Strings
$Database = "test"
$Server = "myserver1"

 
#Export File
$AttachmentPath0 = "C:\temp\SQLData0.csv"
$AttachmentPath = "C:\temp\mySQLData.csv"

#  get data
$SqlQuery = "SELECT * FROM [dbo].[dataNew]"
$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 $AttachmentPath0  
(gc $AttachmentPath0) | % {$_ -replace '"', ""} | out-file $AttachmentPath -Fo -En ascii
$dt = Get-Date -Format yyyyMMddHHmmss

#Start-Sleep -s 10

#SMTP Relay Server
$SMTPServer = "smtp.company.com"
$From = "no_reply@company.com"
$To = "jli@company.com"
$Subject = "This is Subject"
$Body = "This is Body Text"

$Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)
$SMTPClient = New-Object Net.Mail.SmtpClient($SMTPServer, 25) 
$SMTPClient.EnableSsl = $false 
$SMTPClient.Credentials = New-Object System.Net.NetworkCredential("no_reply", "mypa$$word"); 


$Msg.IsBodyHTML = $False
$Attachment = new-object Net.Mail.Attachment($AttachmentPath)
$Msg.attachments.add($Attachment)
$SMTPClient.Send($Msg)



 

Refrences:
http://poshcode.org/1662
http://blogs.technet.com/b/heyscriptingguy/archive/2011/11/02/remove-unwanted-quotation-marks-from-csv-files-by-using-powershell.aspx

Advertisements


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