Powershell导出sqlserver数据并发送邮件脚本

用Python创建的邮件服务比较麻烦，后来网上找到可以通过Powershell来连接数据库并导出SQL查询结果为CSV文件，并通过邮箱发送到指定邮箱。

$Date = Get-Date (get-date).AddDays(-1) -uformat "%Y%m%d"
$yDate = Get-Date (get-date).AddDays(-8) -uformat "%Y%m%d"
$fileName = "D:\aa-$Date.csv"
$yfileName = "D:\aa-$yDate.csv"
$Database = '数据库名'
$Server = '192.168.20.33'
$UserName = 'kf'
$Password = '********'
#数据库查询脚本
$SqlQuery = "查询语句"
function Export_Excel {
# Accessing Data Base
$SqlConnection = New-Object -TypeName System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;user id=$UserName;pwd=$Password"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$set = New-Object data.dataset
# Filling Dataset
$SqlAdapter.Fill($set)
# Consuming Data
$Table = $Set.Tables[0]
$Table | Export-CSV $fileName
#$Table
}
function SendMail {
#发邮件
$smtpServer = "smtp.163.com"
$smtpUser = "dxlyw_2015"
$smtpPassword = "********"
#$smtp.Send($mail)
$ss=ConvertTo-SecureString -String "$smtpPassword" -AsPlainText -force
$ss|Write-Host
$cre= New-Object System.Management.Automation.PSCredential("$smtpUser",$ss)
Send-MailMessage -to "******@163.com" -from "[email protected]" -Subject "Call Record" -SmtpServer "$smtpServer" -Credential $cre -Attachments $fileName
}
Export_Excel
Start-Sleep -Seconds 60
SendMail
echo "remove-item -Force $fileName"
remove-item -Force $fileName #删除7天前的文件