Powershell script to get output through Sql query in table format attached in email?

Question

The below script is resulting in the error below when attempt to send mail is made.

New-Object : A positional parameter cannot be found that accepts argument '='. At line:22 char:18 + ... onnection = New-Object System.Data.SqlClient.SqlConnection $SqlCon ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [New-Object], ParameterBindingException + FullyQualifiedErrorId : PositionalParameterNotFound,Microsoft.PowerShell.Commands.NewObjectCommand

Exception calling "Fill" with "1" argument(s): "Login failed for user ''." At line:29 char:1 + $SqlAdapter.Fill($DataSet) + ~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : SqlException

Send-MailMessage : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'Body'. Specified method is not supported. At line:44 char:17 + -BodyAsHtml $html_table ` + ~~~~~~~~~~~ + CategoryInfo : InvalidArgument: (:) [Send-MailMessage], ParameterBindingException + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.PowerShell.Commands.SendMailMessage

$Servers = (Import-Csv -Path "D:\Scripts\input.csv").ComputerName    
$SQLDBName = "ReportServer"    
$SQLQuery = @"
    SELECT Distinct
        RL.RoleName,
        USR.UserName
    FROM  
        Catalog C
        INNER JOIN Policies PL 
                ON C.PolicyID = PL.PolicyID
        INNER JOIN PolicyUserRole PUR
                ON PUR.PolicyID = PL.PolicyID
        INNER JOIN Users USR
                ON PUR.UserID = USR.UserID
        INNER JOIN dbo.Roles RL
                ON RL.RoleID = PUR.RoleID    
        WHERE RoleName = 'Content Manager' 
        ORDER BY USR.UserName
"@

# This code connects to the SQL server and retrieves the data    
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection    $SqlConnection.ConnectionString = "Server = $Servers; Database = $SQLDBName;"    
$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 System.Data.DataSet    
$SqlAdapter.Fill($DataSet)    
$SqlConnection.Close()               

# This code outputs the retrieved data
$html = $DataSet.Tables[0] | ConvertTo-Html -fragment    
$results = $DataSet.Tables | format-table -autosize | out-string    
$mail_body = $results

# Send the email    
$html_table = $dt | sort-object "Status" | ConvertTo-Html -Fragment  

Send-MailMessage `
    -From "Reporting.Services@accenture.com" `
    -To 'aditi.m.singh@accenture.com' `
    -Subject 'Sending the Attachment' `
    -BodyAsHtml $html_table `
    -SmtpServer 'AMRINT.SMTP.ACCENTURE.COM'

I'd suggest adding more formatting to your code. (The block beginning with ```$Servers = ...```) — derekbaker783, Apr 30 '20 at 12:53
@derekbaker783 the email part in bottom is saying as "The string is without terminator" — Aditi Singh, Apr 30 '20 at 13:17
@derekbaker783 Under $Servers i am passing the input csv file with the database server name and under $sqldbname i am passing the db name and inside $SQLQuery i am passing the sql query . — Aditi Singh, Apr 30 '20 at 13:27
@derekbaker783 After running the above formatted script this error is coming — Aditi Singh, Apr 30 '20 at 13:39
you can edit your question to resolve any issues caused by the edit. It was edited because the original format was very hard to read. — derekbaker783, Apr 30 '20 at 13:42
@derekbaker783 the edits are fine it is throwing error at line 22,29 and 44 — Aditi Singh, Apr 30 '20 at 13:48
See if [this](https://www.reddit.com/r/PowerShell/comments/6tgo82/getting_error_the_string_is_missing_the_terminator/) or [this](https://stackoverflow.com/questions/20706869/powershell-is-missing-the-terminator) helps. — Vivek Kumar Singh, Apr 30 '20 at 13:49
@derekbaker783 How to compare the input csv file with the output from sql query and if the values are not matching then trigger a mail in the below script. Please help — Aditi Singh, May 05 '20 at 06:39

score 1 · Accepted Answer · answered Apr 30 '20 at 14:35

This should work for you. One issue you had is that the variable $dt was never initialized in your script.

param(
    $emailFrom = 'Reporting.Services@accenture.com',
    $emailTo = 'aditi.m.singh@accenture.com',
    $emailSubject = 'Sending the Attachment',
    $smtp = 'AMRINT.SMTP.ACCENTURE.COM',
    $Server = "$Env:ComputerName\MSSQLSERVER01",
    $SQLDBName = 'Master',
    $SQLQuery = @"
        SELECT Distinct
            RL.RoleName,
            USR.UserName
        FROM  
            Catalog C
            INNER JOIN Policies PL 
                    ON C.PolicyID = PL.PolicyID
            INNER JOIN PolicyUserRole PUR
                    ON PUR.PolicyID = PL.PolicyID
            INNER JOIN Users USR
                    ON PUR.UserID = USR.UserID
            INNER JOIN dbo.Roles RL
                    ON RL.RoleID = PUR.RoleID    
            WHERE RoleName = 'Content Manager' 
            ORDER BY USR.UserName    
"@
)

# This code connects to the SQL server and retrieves the data    
$SQLConnection = New-Object System.Data.SqlClient.SqlConnection    
$SqlConnection.ConnectionString = "Server = $Server; Database = $SQLDBName; Integrated Security=true;"    
$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 System.Data.DataSet    
$SqlAdapter.Fill($DataSet)    
$SqlConnection.Close()               

$data = $DataSet.Tables[0]
$html = $data `
    | Select-Object -Property RoleName, UserName `
        | ConvertTo-Html -fragment `
            | Out-String

Send-MailMessage `
    -From $emailFrom `
    -To $emailTo `
    -Subject $emailSubject `
    -BodyAsHtml $html `
    -SmtpServer $smtp

One minor thing with this answer is that the $SQLDBName should be = 'ReportServer' for the given SQL Query instead of Master. — Chad Rexin, Jun 11 '21 at 13:39

Powershell script to get output through Sql query in table format attached in email?

1 Answers1