CODEWITHSUNDEEP
sqlpowershell
Aditi Singh
Aditi Singh

Reputation: 31

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

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 "[email protected]" `
    -To '[email protected]' `
    -Subject 'Sending the Attachment' `
    -BodyAsHtml $html_table `
    -SmtpServer 'AMRINT.SMTP.ACCENTURE.COM'

Upvotes: 0

Views: 3686

Answers (1)

derekbaker783
derekbaker783

Reputation: 9581

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

param(
    $emailFrom = '[email protected]',
    $emailTo = '[email protected]',
    $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

Upvotes: 1

Related Questions