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

Shortcut for Directory – PowerShell

It’s been for a while since I started using PowerShell for some operations. It might seem complicated at the beginning but if you be patient and keep learning, it makes so many tasks easy for you, especially for .NET developers.

To create aliases for the commands:

New-Alias show Get-ChildItem 

And when you use it

 show 

It will list the files & folders in the current directory, just like DIR or LS.

When you close the current PowerShell session, this alias will be removed. In order to make it permanent, you will need to create a file with the name Microsoft.PowerShell_profile.ps1 under PSConfiguration folder under  Windows PowerShell profile folder. To learn your Windows PowerShell profile folder, use

Get-Variable profile | Format-List

So the location of the new file would be like  >

Windows PowerShell profile folder/PSConfiguration/Microsoft.PowerShell_profile.ps1

If you create/edit this file and add the new aliases to it, they will be available for all sessions.

There is one issue about creating an alias for navigating folders – it is not possible. Aliases are just for command, in order to navigate a directory, you will need to create a function in the Microsoft.PowerShell_profile.ps1 file.

function myproject { set-location "C:\Users\Dude\Desktop\MyImportanProject" }
function desktop { set-location "C:\Users\Dude\Desktop\" }

After you save this functions in the file and reopen PowerShell, you can use the commands by just typing 

myproject

desktop

Information provided here is a summary from official support page here >> https://technet.microsoft.com/en-us/library/ee176913.aspx

#command-line, #directory-alias-powershell, #powershell, #powershell-basics, #powershell-for-developers, #shorcut-directory-powershell