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

woyoudn

由于每天要将一些数据发到领导邮箱，故写了一个powershell脚本实现每天自动导出数据并发送邮件
将此脚本复制到**ps1文件里，修改相应的参数即可使用，需要修改的地方已标为红色

• $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 "dxlyw_2015@163.com" -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天前的文件