I needed the ability to send emails from Azure SQL DB, which doesn't support it. The long-term goal is to use LogicApps or SendGrid or something like that, but while I wait for permissions and the like, let me share this instead. It acts like sp_send_dbmail, and will even run queries in the database (or other databases on that server!) and send them from your on-prem server.
This is based on others. They had the awesome parts, my parts are the crappy glue making it work. : )
The emailqueue table looks like this:
[Id] [INT] IDENTITY(1,1) NOT NULL,
[Recipients] [VARCHAR](250) NOT NULL,
[Cc_recipients] [VARCHAR](250) NULL,
[Email_Subject] [VARCHAR](250) NOT NULL,
[Email_body] [VARCHAR](MAX) NULL,
[Email_body_format] [VARCHAR](10) NULL,
[Query] [NVARCHAR](MAX) NULL,
[profile_name] [VARCHAR](250) NULL,
[QueueTime] [DATETIME2](7) NOT NULL,
[SentTime] [DATETIME2](7) NULL,
[Importance] [VARCHAR](10) NULL,
[attach_query_result_as_file] [BIT] NULL,
[query_attachment_filename] [VARCHAR](200) NULL,
[query_result_header] [BIT] NULL,
[query_result_width] [INT] NULL,
[query_result_separator] [VARCHAR](2) NULL,
[query_result_no_padding] [BIT] NULL,
[bcc_recipients] [VARCHAR](255) NULL,
[execute_query_database] [VARCHAR](255) NULL,
[from_address] [VARCHAR](MAX) NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[EmailQueue] ADD CONSTRAINT [Co_Importance] DEFAULT ('Normal') FOR [Importance]
GO
ALTER TABLE [dbo].[EmailQueue] ADD DEFAULT ((0)) FOR [attach_query_result_as_file]
GO
ALTER TABLE [dbo].[EmailQueue] ADD DEFAULT ((1)) FOR [query_result_header]
GO
ALTER TABLE [dbo].[EmailQueue] ADD DEFAULT ((0)) FOR [query_result_no_padding]
GO
And the SP:
CREATE PROCEDURE [dbo].[log_dbmail]
@profile_name sysname = NULL,
@recipients VARCHAR(MAX) = NULL,
@copy_recipients VARCHAR(MAX) = NULL,
@blind_copy_recipients VARCHAR(MAX) = NULL,
@subject NVARCHAR(255) = NULL,
@body NVARCHAR(MAX) = NULL,
@body_format VARCHAR(20) = NULL,
@importance VARCHAR(6) = 'NORMAL',
@sensitivity VARCHAR(12) = 'NORMAL',
@file_attachments NVARCHAR(MAX) = NULL,
@query NVARCHAR(MAX) = NULL,
@execute_query_database sysname = NULL,
@attach_query_result_as_file BIT = 0,
@query_attachment_filename NVARCHAR(260) = NULL,
@query_result_header BIT = 1,
@query_result_width INT = 256,
@query_result_separator VARCHAR(2) = ' ',
@exclude_query_output BIT = 0,
@append_query_error BIT = 0,
@query_no_truncate BIT = 0,
@query_result_no_padding BIT = 0,
@mailitem_id INT = NULL OUTPUT,
@from_address VARCHAR(MAX) = NULL,
@reply_to VARCHAR(MAX) = NULL
AS
INSERT INTO dbo.EmailQueue
(
Recipients,
Cc_recipients,
Bcc_recipients,
Email_Subject,
Email_body,
Email_body_format,
Query,
Execute_Query_Database,
attach_query_result_as_file,
query_attachment_filename,
query_result_header,
query_result_width,
query_result_separator,
query_result_no_padding,
profile_name,
QueueTime,
SentTime,
Importance
)
VALUES
( @recipients, -- Recipients - varchar(250)
@copy_recipients, -- Cc_recipients - varchar(250)
@blind_copy_recipients,
@subject, -- Email_Subject - varchar(250)
@body, -- Email_body - varchar(max)
@body_format, -- Email_body_format - varchar(10)
@query,
@execute_query_database,
@attach_query_result_as_file,
@query_attachment_filename,
@query_result_header,
@query_result_width,
@query_result_separator,
@query_result_no_padding,
@profile_name, -- profile_name - varchar(250)
CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Central Standard Time' AS DATETIME), -- QueueTime - datetime2
NULL, -- SentTime - datetime2
@importance
)
The powershell that pulls it all together:
| #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! | |
| } |
No comments:
Post a Comment