SQL and sending emails with Powershell

Run SQL Command

Invoke-Sqlcmd -Query "SELECT COUNT(*) AS Count FROM Table" -ConnectionString "YourConnectionString"

Invoke commands in a script file and save the output in a text file

Invoke-Sqlcmd -InputFile "C:\ScriptFolder\TestSqlCmd.sql" | Out-File -FilePath "C:\ScriptFolder\TestSqlCmd.rpt"

Invoke sql script and write the results into CSV file.

Invoke-Sqlcmd -InputFile "C:\Users\Mehmet\Desktop\Users.sql" -ConnectionString "connection-string" | export-csv "C:\Users\Mehmet\Desktop\SqlResults.csv" 

Invoke sql script and write the results into JSON file.

Invoke-Sqlcmd -InputFile "C:\Users\Mehmet\Desktop\Users.sql" -ConnectionString "connection-string" | ConvertTo-Json | export-csv "C:\Users\Mehmet\Desktop\SqlResults.csv" 

Invoke a script and pass in variable values from a string

C:\> $StringArray = "MYVAR1='String1'", "MYVAR2='String2'"
C:\> Invoke-Sqlcmd -Query "SELECT `$(MYVAR1) AS Var1, `$(MYVAR2) AS Var2" -Variable $StringArray

Send Mail

$PSEmailServer = "smtp-mail-server"
$mypassword = ConvertTo-SecureString "your-password" -AsPlainText -Force
$mycredential = New-Object System.Management.Automation.PSCredential ("your-username", $secpasswd)

Send-MailMessage -From "from@mehmet.es" -To "to@mehmet.es" -Subject "Test email" -Body "This is a test email" -Credential $mycredential

A scenario: Retrieve data from a database and send it as a CSV file.

$CsvFileName = "./Users.csv"
$SqlScriptFile = "Users.sql"
$ConnectionString = "your-connection-string"
$SmtpAddress = "your-smtp"
$UserName = "username"
$Password = "password"
$FromEmail = "from"
$ToEmail = "to"
$EmailSubject = "your-subject"
$EmailBody = "hi there"

$PSEmailServer = $SmtpAddress
$mypassword = ConvertTo-SecureString $Password -AsPlainText -Force
$mycredential = New-Object System.Management.Automation.PSCredential ($UserName, $mypassword)

# Run sql command and create the file
Invoke-Sqlcmd -InputFile $SqlScriptFile  -ConnectionString $ConnectionString  | export-csv $CsvFileName 

# Send the file 
Send-MailMessage -From $FromEmail -To $ToEmail -Subject $EmailSubject -Body $EmailBody -Credential $mycredential -Attachments $CsvFileName

# Remove the file
Remove-Item $CsvFileName

"DONE!"

Note: Invoke-Sqlcmd does not work on Azure WebJobs, it is not installed.

#powershell #powershell-send-email #powershell-sql-commands #powershell-sql-to-csv

Advertisements