Create a Report from SQL and email it in Powershell

Create a Report from SQL and email it in Powershell

 #Connection Strings

    $Database = "MSSQL"

$Server = "SQL-SERVER"

    #SMTP Relay Server

    $SMTPServer = "relay.mailserver.org"

    #Export File

    $AttachmentPath = "C:\Scripts\report.csv"

    # Connect to SQL and query data, extract data. The SQL Query Commandlet will take any Query in sqlcmd

    $SqlQuery = "SELECT Item1, Item2, Item3, Item4    FROM sometable where TimeStamp >= GETDATE() - 1 "

    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection

    $SqlConnection.ConnectionString = "Data Source=$Server;Initial Catalog=$Database;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

    $nRecs = $SqlAdapter.Fill($DataSet)

    $nRecs | Out-Null

    #Populate Temporary Table

    $objTable = $DataSet.Tables[0]

    #Export Data to CSV File

    $objTable | Export-CSV $AttachmentPath

    #Send SMTP Message

    $Mailer = new-object Net.Mail.SMTPclient($SMTPServer)

    $From = "reports@yourdomain.com"

    $To = "managers@yourdomain.com"

    $Subject = "Daily Report"

    $Body = "Attached is the Daily Report"

    $Msg = new-object Net.Mail.MailMessage($From,$To,$Subject,$Body)

    $Msg.IsBodyHTML = $False

    $Attachment = new-object Net.Mail.Attachment($AttachmentPath)

    $Msg.attachments.add($Attachment)

    $Mailer.send($Msg)