Sending out Emails from your database

To send an email in SQL Server, all that’s required is a fairly simple call of a stored procedure created by Microsoft.

EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'sherpa@sherpaofdata.com',
    @body = 'The stored procedure finished successfully.',
    @subject = 'Automated Success Message';

Now, there are several more parameters you can use. Below are some of my favorites that I use often.

• @profile_name – if the Email Profile I need to use is not the default.
• @body_format – if I need to send an email in HTML format. Thankfully, the value is ‘HTML’ in that case.
• @query_result_no_padding – if you are forced to send emails in text format with query results, this can be your friend. It “can” clean up the resultset so that it’s fairly readable instead of something like the following:

EmployeeId    EmployeeName                                                
           ActiveFrom      
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------- ----------------
         1 Howard Johnson                                                   
                 2004-03-25
         2 Milton Waddams                                                          
                 2006-01-11
         3 James McGillicutty                                           
                 2007-01-01
         4 Jackson Hewitt                                                    
                 2007-04-01
         5 Robert Grant                                            
                 2007-06-02

For the many other parameters you can use with sp_sendmail, please refer to the Microsoft Documentation found here.

After running this stored procedure, SQL Server queues the email to be sent. From there, you should check often to see if it was actually sent. (And yes, it can get stuck there forever.)

With, Snowflake, it’s actually fairly close to the same basic principle as SQL Server. You just use the following system call – SYSTEM$SEND_EMAIL – to do your dirty work of sending emails.

CALL SYSTEM$SEND_EMAIL 
(        'EmailNotificationIntegrations',
         'sherpa@sherpaofdata.com',
         'Automated Success Message',
         'The stored procedure finished successfully.');

As always, there are a few parameters:

• The first parameter – EmailNotificationIntegrations – is the name of the integration we set up last time.
• The second – sherpa@sherpaofdata.com – is a comma-separated list of the emails to send to. Notice if an email listed is not set up as part of the integration, then it will not be sent an email.
• The third parameter is the subject of the email. And no, you cannot provide an empty string, so don’t even think about it.
• The fourth parameter is the body of the email. And no, you cannot provide an empty string, so don’t even think about it.
• There is a fifth optional parameter for the email’s MIME type. The default is ‘text/plain’ for a plain text email, while ‘text/html’ is for an HTML email.

And with that, just like magic, you can now send emails from either SQL Server or Snowflake. Hope you make good use of this knowledge – at least until an RGE occurs.