PowerShell从远程连接SQLSERVER导出CSV文件后转Excel后发送到指定邮箱

经过几天的折腾,终于搞定了这个功能,同时也搞定了通过计划任务程序发送乱码的问题,大家可以按照以下脚本进行相关设置,这里需要补充一下,由于查询结果中某些字段会有回车符,需要做一下替换,我替换了2次,保证了文件在一行中。
REPLACE(replace(字段名称 ,CHAR(13),','),CHAR(10),',') as '字段显示名称'

PowerShell代码:

$Subject ="SubjectName-$((Get-Date).ToString("yyyyMMdd_HHmmss"))"
$Directory="设定文件生成的目录需带盘符"
$csvfilename="csv文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).csv"
$xlsxfilename="xlsx文件名-_$((Get-Date).ToString("yyyyMMdd_HHmmss")).xlsx"
$Sendfile="$Directory$xlsxfilename"
$Database = '数据库名称'
$Server = '数据库服务器地址'
$UserName = '数据库用户名'
$Password = '数据库密码'

#数据库查询脚本
$SqlQuery = "SQL查询脚本"
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 -encoding utf8 -NoTypeInformation $Directory$csvfileName
#$Table
}

function To_Excel {
### Create a new Excel Workbook with one empty sheet
$excel = New-Object -ComObject excel.application 
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

### Build the QueryTables.Add command
### QueryTables does the same as when clicking "Data ? From Text" in Excel
$TxtConnector = ("TEXT;" + "$Directory$csvfileName")
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
#$delimiter = "\r\n" #Specify the delimiter used in the file
### Set the delimiter (, or ;) according to your regional settings
$query.TextFileOtherDelimiter = $Excel.Application.International(5)

### Set the format to delimited and text for every column
### A trick to create an array of 2s is used with the preceding comma
$query.TextFilePlatform = 65001
#$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType  = 1
$query.TextFileColumnDataTypes = ,2 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

### Execute & delete the import query
$query.Refresh()
$query.Delete()

### Save & close the Workbook as XLSX. Change the output extension for Excel 2003
$Workbook.SaveAs("$Directory$xlsxfilename",51)
$excel.Quit()
}

function SendMail {
#发邮件
$smtpServer = "smtp.126.com"
$smtpUser = "SMTP用户名"
$smtpPassword = "SMTP密码"
#$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 收件人用逗号间隔 -from 自动邮件<发送邮箱地址> -Subject $subject -SmtpServer "$smtpServer" -Port 25 -Encoding UTF8 -Attachments $Sendfile -Credential $cre
}
Export_Excel
To_Excel
Start-Sleep -Seconds 10
SendMail
echo "remove-item -Force $csvfileName"
remove-item -Force $Directory$csvfileName,$Directory$xlsxfilename #删除两个临时文件

Powershell导出sqlserver数据保存为EXCEL文件并发送邮件脚本

$subject ="邮件主题-$((Get-Date).ToString("yyyyMMdd_hhmmss"))"   
$Database = '数据库名称'
$Server = '数据库地址'
$UserName = '数据库用户名'
$Password = '数据库密码'

#数据库查询脚本
$SqlQuery = "加入SQL查询脚本"
#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
$DataSet = New-Object data.dataset
# Filling Dataset
$SqlAdapter.Fill($DataSet)
$DataSetTable = $DataSet.Tables["Table"];

## ———- Working with Excel ———- ##

## – Create an Excel Application instance:
$xlsObj = New-Object -ComObject Excel.Application;

## – Create new Workbook and Sheet (Visible = 1 / 0 not visible)
$xlsObj.Visible = 0;
$xlsWb = $xlsobj.Workbooks.Add();
$xlsSh = $xlsWb.Worksheets.item(1);

## – Build the Excel column heading:
[Array] $getColumnNames = $DataSetTable.Columns | Select ColumnName;

## – Build column header:
[Int] $RowHeader = 1;
foreach ($ColH in $getColumnNames)
{
$xlsSh.Cells.item(1, $RowHeader).font.bold = $true;
$xlsSh.Cells.item(1, $RowHeader) = $ColH.ColumnName;
$RowHeader++;
};

## – Adding the data start in row 2 column 1:
[Int] $rowData = 2;
[Int] $colData = 1;

foreach ($rec in $DataSetTable.Rows)
{
foreach ($Coln in $getColumnNames)
{
## – Next line convert cell to be text only:
$xlsSh.Cells.NumberFormat = "@";

## – Populating columns:
$xlsSh.Cells.Item($rowData, $colData) = $rec.$($Coln.ColumnName).ToString();
$ColData++;
};
$rowData++; $ColData = 1;
};

## – Adjusting columns in the Excel sheet:
$xlsRng = $xlsSH.usedRange;
$xlsRng.EntireColumn.AutoFit();

## ———- Saving file and Terminating Excel Application ———- ##

## – Saving Excel file – if the file exist do delete then save
$xlsFile = "带有路径的文件名_$((Get-Date).ToString("yyyyMMdd_hhmmss")).xls";

if (Test-Path $xlsFile)
{
Remove-Item $xlsFile
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
}
else
{
$xlsObj.ActiveWorkbook.SaveAs($xlsFile);
};

## Quit Excel and Terminate Excel Application process:
$xlsObj.Quit(); (Get-Process Excel*) | foreach ($_) { $_.kill() };

## – End of Script – ##

function SendMail {
#发邮件
$smtpServer = "SMTP服务器地址"
$smtpUser = "SMTP用户名"
$smtpPassword = "SMTP密码"
#$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 aa@gmail.com,bb@gmail.com -from 发件人显示名称<cc@gmail.com> -Subject $subject -SmtpServer "$smtpServer" -Port 25 -Encoding UTF8 -Attachments $xlsFile  -Credential $cre
}
#Export_Excel
Start-Sleep -Seconds 30
SendMail
echo "remove-item -Force $xlsFile"
remove-item -Force $xlsFile  #删除当前生成的文件

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 "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天前的文件

一个Macrovision未知的服务FlexNet Licensing Service 64

在windows中发现一个不知名的服务——FlexNet Licensing Service 64,网上找了一下也没有具体的说明,找到文件所在目录,看了一下目录下的文件,发现是Tableau安装之后自动安装的服务,再搜索了一下Macrovision,发现这个工具背后也是一个监控的,类似苏州马丁流氓公司一样会给你来律师函的(传送门),赶紧停了服务,并且将相关文件删除了。

C:\Program Files\Common Files\Macrovision Shared\FlexNet Publisher\FNPLicensingService64.exe

Debian 9, Debian 10 以及Ubuntu 关闭IPv6

安装的debian 和 ubuntu,默认都会开启IPv6,但是很多服务商并不分配IPv6,因此大部分的时候我们需要将IPv6 关闭。主要有两种办法,一个是修改sysctl.conf, 或者是在/etc/sysctl.d 目录下创建一个.conf 文件

方法1:
编辑/etc/sysctl.conf 文件,在文件的最末尾添加下面的entry:

net.ipv6.conf.all.disable_ipv6 = 1

如果仅想关闭某一网卡的ipv6,比如说ens4, 那就可以添加下面的entry:

net.ipv6.conf.ens4.disable_ipv6 = 1

让命令生效:

sysctl -p

方法2:
在/etc/sysctl.d 目录下创建70-disable-ipv6.conf

nano /etc/sysctl.d/70-disable-ipv6.conf

添加下面的entry:

net.ipv6.conf.all.disable_ipv6 = 1

如果仅仅想关闭某一网卡,比如说ens4, 添加下面的entry:

net.ipv6.conf.ens4.disable_ipv6 = 1

立刻生效:

sysctl -p -f /etc/sysctl.d/70-disable-ipv6.conf

就是这么简单