☰

电子邮件中未收到查询结果

tag-icon • tag-icon mysql powershell
电子邮件中未收到查询结果

这是下面的 powershell 脚本。我试图将查询结果导出到电子邮件正文中。但是，电子邮件中除了表头外什么都没有。有人能帮忙找出哪里出了问题/不完整吗？

    # Create a DataTable
    $table = New-Object system.Data.DataTable "bugs"
    $col1 = New-Object system.Data.DataColumn bug_id,([string])
    $col2 = New-Object system.Data.DataColumn bug_status,([string])
    $col3 = New-Object system.Data.DataColumn resolution,([string])
    $col4 = New-Object system.Data.DataColumn short_desc,([string])
    $col5 = New-Object system.Data.DataColumn deadline,([string])
    $table.columns.add($col1)
    $table.columns.add($col2)
    $table.columns.add($col3)
    $table.columns.add($col4)
    $table.columns.add($col5)

    # This code defines the search string in the database table
    $SQLQuery = "SELECT bug_id, 
           bug_status, 
           resolution, 
           short_desc, 
           deadline 
    FROM   bugs 
    WHERE  ( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' ) 
             AND deadline BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 30 DAY) 
           ) 
            OR ( bug_status IN ( 'RESOLVED', 'VERIFIED', 'INTEST' ) 
                 AND deadline BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND 
                                      CURDATE() )

           ORDER BY deadline ASC 
    "

    # This code connects to the SQL server and retrieves the data
    $MySQLAdminUserName = 'user_name'
    $MySQLAdminPassword = 'password'
    $MySQLDatabase = 'mantis'
    $MySQLHost = '<HOSTNAME>'
    $ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase

    [void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\Devolutions\Remote Desktop Manager Free\MySQL.Data.dll")
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()

    $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($SQLQuery, $Connection)
    $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
    $DataSet = New-Object System.Data.DataSet
    $RecordCount = $dataAdapter.Fill($dataSet, "data")
    $DataSet.Tables[0]  

    # Create an HTML version of the DataTable
    $html = "<table><tr><td>bug_id</td><td>bug_status</td><td>resolution</td><td>short_desc</td><td>deadline</td></tr>"
    foreach ($row in $table.Rows)
    { 
        $html += "<tr><td>" + $row[0] + "</td><td>" + $row[1] + "</td></tr>" + "</td></tr>" + $row[2] + "</td></tr>"
    }
    $html += "</table>"

# Send the email
$smtpserver = "smtp.server.net"
$from = "[email protected]"
$to = "[email protected]"
$subject = "Hello"
$body = $DataSet.Tables[0] | convertto-html ;
Send-MailMessage -smtpserver $smtpserver -from $from -to $to -subject $subject -body $body -bodyashtml

运行脚本后出错-

Send-MailMessage : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'Body'. Specified method is not supported.
At C:\Users\ai\Desktop\testMail2.ps1:63 char:85
+ Send-MailMessage -smtpserver $smtpserver -from $from -to $to -subject $subject -body <<<<  $body -bodyashtml
    + CategoryInfo          : InvalidArgument: (:) [Send-MailMessage], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.PowerShell.Commands.SendMailMessage

答案1

您永远不会用任何数据填充 $table。您正在填充 $dataset。您将 $DataSet.Tables[0] 回显到屏幕，但返回使用 $table 构造 $body。编辑现在选择所需的列：

$body = $DataSet.Tables[0] | select col1, col2, col3 | convertto-html | out-string

将 col1、col2、col3 替换为您想要显示的列，省略返回的您不感兴趣的任何列名。

答案2

Send-MailMessage 需要字符串，而 $body 似乎是一个对象数组。要消除歧义，请将 的类型$body更改为[string]$body =。

虽然ConvertTo-Html应该返回一个字符串，但是您可以通过管道传输到 Get-Member 来检查，如下所示：

$DataSet.Tables[0] | ConvertTo-html | 获取成员

相关内容