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