Email Attachment with Exported CSV File from a Select and Remove Double Quotes in the File with Powershell



Import-Module -Name 'SQLPS' -DisableNameChecking  
$dt = Get-Date -Format yyyyMMddHHmmss
$workdir='C:\temp'

$svrname = "MC047012\MSSQL2014"
 $db ='test1'

 
#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname 
$svr.ConnectionContext.StatementTimeout = 0

 

$myPath0 = "$env:temp\myfile$($dt)0.txt"
$myPath = "$env:temp\myfile$($dt).txt"
 
 
$q = @"
select * from  
dbo.table1
"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -Path $myPath0 
gc $myPath0 | % {$_ -replace '"', ""} | out-file $myPath -Fo -En ascii


 
#Start-Sleep -s 10
 
#SMTP Relay Server
$SMTPServer = "info.company.com"
$From = "no_reply@company.com"
$To = "jli@comany.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", "my$Secret"); 
 
 
$Msg.IsBodyHTML = $False
$Attachment = new-object Net.Mail.Attachment($myPath)
$Msg.attachments.add($Attachment)
$SMTPClient.Send($Msg)



 
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