Last active
May 11, 2023 20:26
-
-
Save mbourgon/e7180f8c975d159c653091f50f64030b to your computer and use it in GitHub Desktop.
Azure SQL Mail sp_send_dbmail replacement with onprem - code to pull from emailqueue table, query databases, send emails with attachments++
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#2022/10/17 mdb - the goal is to be able to run this on-prem and send emails from azure. | |
# This needs to be replaced with a function at some point, but I need email functionality right now. | |
# Tries to support as many of the standard parameters as possible. | |
#2023/01/06 mdb - adding maxcharlength at 200k to handle larger emails | |
#2023/01/17 mdb - handling double quotes in the query - can't use replace on a null, trying REPLACE in the initial query_to_run didn't work, wound up moving it after the null check for query block | |
#2023/01/19 mdb - what happens if the query is empty? No file created, so it fails. Fixed. | |
$ErrorActionPreference="Stop" | |
#First, get what's in the table to perform/mail | |
$HOME_DATABASE = 'mydatabase' #need this so that we can query other databases if need be. Probably of limited usefulness. | |
$params = @{ | |
'Database' = $HOME_DATABASE | |
'ServerInstance' = 'mydatabase.database.windows.net' | |
'Username' = 'myuser' | |
'Password' = 'mypassword' | |
'Query' = 'select *, @@servername as servername from EmailQueue where senttime is null' | |
} | |
$mails_to_send = Invoke-Sqlcmd @params -MaxCharLength 20000 #otherwise fields lop off at 4k | |
#Second, for each email, we need to get the variables, run the query if there is one, format things, send email, mark as sent | |
$mails_to_send | %{ | |
$From = if ([DBNull]::Value -eq $_.from_address){"do_not_reply <Do_not_reply_$($_.servername)@null.com>"} else {$_.from_address} | |
$To = $_.recipients #can't do this here, we need to split it to an array | |
$CC = $_.cc_recipients | |
$BCC = $_.bcc_recipients | |
$ID = $_.id #queue table mail id | |
$Subject = $_.email_subject | |
$SMTPServer = "mysmtp.null.com" | |
$SMTPPort = "25" | |
$Body = if ([DBNull]::Value -eq $_.email_body){" "}else{$_.email_body} #$_.email_body | |
$query_to_run = $_.query | |
$email_body_format = $_.email_body_format | |
$query_attachment_filename = $_.query_attachment_filename | |
$query_result_separator = if ([DBNull]::Value -eq $_.query_result_separator){" "}else{$_.query_result_separator} | |
$attach_query_result_as_file = $_.attach_query_result_as_file | |
$execute_query_database = $_.execute_query_database | |
$priority = if ($_.importance -eq $null){"Normal"}else{$_.importance} #no, I don't know why that handles differently. Need to test more. | |
#how to overwrite part of the splat: $params.query = 'select 1' | |
#splitting comma/semicolon separated email addressess into an array so that it works with send-mailmessage - appears to work | |
$array_TO = @() | |
$to.split(";",[StringSplitOptions]::RemoveEmptyEntries) | Foreach {$array_TO += "$_"} | |
if ([DBNull]::Value -eq $CC){}else{ | |
$array_CC = @() | |
$cc.split(";",[StringSplitOptions]::RemoveEmptyEntries) | Foreach {$array_CC += "$_"} | |
} | |
if ([DBNull]::Value -eq $BCC){}else{ | |
$array_BCC = @() | |
$bcc.split(";",[StringSplitOptions]::RemoveEmptyEntries) | Foreach {$array_BCC += "$_"} | |
} | |
#blank line here- if you need to run the foreach to set variables, end here | |
$queryresults = @() | |
#RUN THE QUERY and send the results - about half the reason we're doing this from powershell instead of the linked server | |
#https://zeleskitech.com/2015/03/02/null-vs-dbnull-powershell/ | |
if ([DBNull]::Value -eq $query_to_run) {} else { #invoke-sqlcmd returns a DBNull, not a null, and -ne didn't work, so we do an if..else | |
#run the query | |
$params.query = $query_to_run.replace('"',"'") | |
#note that this changes the database context, so we need to put it back at the end when we update | |
if ([DBNull]::Value -eq $execute_query_database) {} else {$params.Database = $execute_query_database} | |
$queryresults = invoke-sqlcmd @params -MaxCharLength 100000 #-OutputAs DataRows #that may be a way to force it so that one field comes up as a field, but unsure of other effects so leaving for now | |
#now reset it for later. | |
$params.database = $HOME_DATABASE | |
<# | |
#I don't know if we need to actually set it to anything for purposes of attachment. The code I have will send empty on | |
#$queryresults.length | |
#if it's empty, set it to something? | |
if($null -ne $queryresults) {} else {$queryresults = $null} | |
#different way to invoke it to try and get results - still doesn't work. | |
#https://stackoverflow.com/questions/23522309/must-specify-an-object-on-piping-variable-to-get-member | |
#if ((Get-Member -InputObject $queryresults -MemberType NoteProperty | measure).Count -eq 0) | |
#{ | |
##do something where we set it to an array or make a custom object or something | |
#} | |
#trying to handle if there's only one object returned, since convertto-html thinks it's a text string and uses * | |
#if (($queryresults| Get-Member -MemberType NoteProperty | measure).Count -eq 0) | |
#{ | |
#do something where we set it to an array or make a custom object or something | |
#} | |
#> | |
#handle attachments - if it's 1, then we need to create a file, write the file, attach the file, then destroy the file. | |
if ($attach_query_result_as_file -eq 1) { | |
#this converts it to a CSV with no extra quotes, and "`t" makes it a tab. | |
#do we even want/need that? the files I see us making are tab-separated files. | |
#$queryresults | ConvertTo-CSV -Delimiter "$query_result_separator" -NoTypeInformation ` | |
# | % {$_ -replace ` | |
# '\G(?<start>^|,)(("(?<output>[^,"]*?)"(?=,|$))|(?<output>".*?(?<!")("")*?"(?=,|$)))' ` | |
# ,'${start}${output}'} ` | |
# | Out-File c:\temp\$($query_attachment_filename) -fo -en ascii ; | |
#this is a basic one that works and sends it, but there's just a one-line header and the results, no dashed line like I see from sp_send_dbmail. | |
#$queryresults |ConvertTo-Csv -NoTypeInformation -delimiter "$query_result_separator" | ` | |
# ForEach-Object {$_ -Replace('"','')} | ` | |
# Out-file c:\temp\$($query_attachment_filename) -fo -en ascii | |
#we'll need to handle padding later, as well as how-to-skip-headers, which is a parameter (query_result_header) | |
#$queryresults | Format-Table -AutoSize -Wrap -HideTableHeaders | |
#This one includes headers and the line of hyphens/dashes. | |
#https://stackoverflow.com/questions/74117598/how-to-invoke-sqlcmd-and-export-with-headers-and-dashes-hyphens-but-tab-delimite | |
$queryresults | | |
ConvertTo-Csv -NoTypeInformation -Delimiter "`t" | | |
ForEach-Object { | |
$_ -replace '"' # output with " chars. removed | |
# If it was the first, i.e. the *header* row that was just output, | |
# construct and output the desired separator row. | |
if ($i++ -eq 0) { | |
($_ -replace '"' -split "`t").ForEach({ '-' * $_.Length }) -join "`t" | |
} | |
} |set-content c:\temp\$($query_attachment_filename) | |
$attachment_filepath = @() #needs to be an array for send-mailmessage | |
$attachment_filepath += 'c:\temp\' + $query_attachment_filename | |
} | |
else { | |
#if it's already formatted, pass it along, otherwise format it | |
#only doing this if it's not a file! | |
if ($queryresults -like "*<body>*" -or $queryresults -like "*<html>*") { | |
$queryresults_for_sending = $queryresults|select * -ExcludeProperty rowerror,rowstate,table,itemarray,haserrors|out-string | |
} | |
else | |
{ | |
#bug if only one field returned, since it returns text. Need to create custom psobject, but unsure how to do that if field name returned is dynamic | |
#https://dba.stackexchange.com/questions/266717/powershell-script-to-send-an-email-with-sql-results-in-html-format | |
$queryresults_for_sending = $queryresults|select * -ExcludeProperty rowerror,rowstate,table,itemarray,haserrors|convertto-html|out-string | |
$queryresults_for_sending = $queryresults_for_sending.Replace("</head>", "<style> TD {padding-left: 2mm}</style></head>") #add prettier spacing | |
} | |
$body = $body + $queryresults_for_sending | |
} | |
} | |
#setting up code for attachment, since it's a flag that may or may not need to be sent. | |
#https://stackoverflow.com/questions/14560270/send-mailmessage-attachments-will-not-accept-null-for-any-variables | |
$attachments = @() | |
if ($attach_query_result_as_file -eq 1) { | |
if ([DBNull]::Value -eq $queryresults -or $queryresults -eq "" -or $queryresults -eq $null){} else | |
{$attachments += 'c:\temp\' + $query_attachment_filename} | |
} | |
# Repeat for each potential parameter | |
$mail_params = @{} | |
if ($attachments.Length -gt 0) { | |
$mail_params['Attachments'] = $attachments | |
} | |
#Send-MailMessage @params -BodyAsHtml –From Monitoring@CorporateActions -Priority $Priority # Other parameters | |
# Repeat for each potential parameter | |
if ($array_cc.Length -gt 0) { | |
$mail_params['CC'] = $array_cc | |
} | |
if ($array_bcc.Length -gt 0) { | |
$mail_params['BCC'] = $array_bcc | |
} | |
#unsure why this doesn't seem to work | |
if ($priority.Length -gt 0) { | |
$mail_params['Priority'] = $priority | |
} | |
#need code for email body - if it's html, use "-bodyashtml". Maybe figure it out when it's about to run, see if there are tags in the email? | |
#doing it the crap way for now and just duplicating this block. DEADLINES. | |
if ($email_body_format -eq 'html' -or $body -like "*<body>*" -or $body -like "*<html>*" -or $body -like "*<table*") { | |
Send-MailMessage @mail_params -From $From -to $array_TO -Subject $Subject -Body $Body -SmtpServer $SMTPServer -port $SMTPPort -BodyAsHtml | |
#and now see if it succeeded | |
if (! $?) | |
{ | |
$logmsg = "ERROR: Cannot send email" | |
throw "ERROR: Cannot send email" | |
} | |
else | |
{ | |
#if it succeeds, mark as done | |
#overwriting the existing query so we can keep that pretty @params block up at the top intact. | |
$params.query = "update EmailQueue set senttime = getdate() where id = $ID" | |
invoke-sqlcmd @params | |
} | |
} | |
else { #aka we do need to send it as html | |
Send-MailMessage @mail_params -From $From -to $array_TO -Subject $Subject -Body $Body -SmtpServer $SMTPServer -port $SMTPPort | |
#and now see if it succeeded | |
if (! $?) | |
{ | |
$logmsg = "ERROR: Cannot send email" | |
throw "ERROR: Cannot send email" | |
} | |
else | |
{ | |
#if it succeeds, mark as done | |
#overwriting the existing query so we can keep that pretty @params block up at the top intact. | |
$params.query = "update EmailQueue set senttime = CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME) where id = $ID" | |
invoke-sqlcmd @params | |
} | |
} | |
#delete email attachment at this point | |
if ($attach_query_result_as_file -eq 1) { | |
if (Test-Path "c:\temp\$query_attachment_filename") { | |
remove-item "c:\temp\$query_attachment_filename" -verbose}} #needs double quotes for the substitution to work! | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment