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

$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  
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 = ""
$From = ""
$To = ""
$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)


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s