SQL Mailer

As said, the functionality is limited but there are quite some use-cases where it will suffice. For example, to send notifications when essential data is modified or deleted. Or when you are running out of disk space.

To send email from SQL Server, it looks like you need to have the Standard Edition or higher. That is because you need to set up an Account and a Profile, and the Management Studio doesn't show that option with SQL Express.

Well, you may not have the fancy UI for that, but you can set it up "the hard way" using a few queries.

Setting up the Account and Profile

The first thing you need to do is set up the Account and a Profile is the MSDB database. First let's create the Account:

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLMailer',
@description = 'SQL Email Account',
@email_address = 'andre@andrespeek.com',
@display_name = 'Andre Speek',
@username='your_account@gmail.com',
@password='super_secret_password',
@mailserver_name = 'smtp.gmail.com'

Of course, don't forget to change the Username, Password and MailServer Name... ;-)

Once you have the Account, next thing to do is to add a Profile:

EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'SQLMailer',
@description = 'SQL Email Profile'

And once you have both the Account and a Profile, they need to be linked together. This profile will also be the Default profile for sending email:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'SQLMailer',
@account_name = 'SQLMailer',
@sequence_number = 1

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
@profile_name = 'SQLMailer',
@principal_name = 'public',
@is_default = 1

Now with the Standard Edition or higher you are ready to go. But with the Express Edition, this functionality is disabled.

No worries, if you want to use it, you can simply enable it!

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

With the Express Edition there is one additional condition. You need to log on to the Management Studio using the SA Account for it to work. Otherwise you will get this error: Failed to initialize sqlcmd library with error number 2147467259.

Let's send some Emails!

With everything set up now, let's send some Emails! First, you could use this to send a notification from a trigger or stored procedure. To do that, you can use this one:

exec msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailer',
@recipients = 'andre.speek@gmail.com',
@subject = 'Mail Test Notification',
@body = 'Mail Sent Successfully',
@body_format = 'text'

Just change the Subject and/or Body so you will be able to recognize why it was send. Oh, and be sure to use the Profile Name you created in the previous steps!

When you use it in a Stored Procedure ot Trigger, you can also build the Body in the query. You could use a cursor for example to create an HTML table to display the results. In that case, don't forget to set the Body Format to HTML instead of Text.

Results as an Attachment

You can also send the results of a Query as an Attachment. For example to get an overview of the data that was created today. Or records that might lack some information.

Or another example: I used it once with a CRM application where the Email field did not include a check on a valid email address. Since I needed a valid email address for another process, I send out a list with Customers that had an invalid email address. With a request to check and correct those Customers.

To send the results as an Attachment, you can use this one:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'SQLMailer',
@recipients = 'andre.speek@gmail.com',
@body = 'Organizations',
@query = 'select rtrim(Name) as Name, rtrim(City) as City from Organizations',
@execute_query_database = 'DBTest',
@attach_query_result_as_file = 1,
@subject = 'Query results as discussed',
@query_result_separator = ';',
@query_attachment_filename = 'organizations.csv',
@query_result_header = 0

As you can see there are quite some options:

execute_query_database
Since the Stored Procedure to send Email is in the MSDB database, you need to specify in which database the query should be executed.
attach_query_result_as_file
Setting this one to 1 takes care the results are attached as a file.
query_result_separator
By default the results are displayed as fixed lengths strings. If you want a CSV file, you can use this one to specify the character to be used as a Separator.
query_attachment_filename
SQL Server will come up with a generic File Name, but with this one you can specify that one yourself.
query_result_header
If you want column names, set this one to 1. If not, you can set it to 0.

A full list of options can be found here: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql.

For the query I always use column names. Not only to limit the results to just the columns I want, but also because the * (star) sometimes fails. Though I am not sure with which SQL Server versions or editions this fails, I try to avoid it all together.

That's it for Today!

Sending Email through SQL Server is easy to use and can be very usefull when no other tools are available. You need to hack a little bit, but it is even available in the SQL Server Express edition.

I am curious how you use this one in your projects, so let me know! By Email will do just fine! ;-)

You can leave a comment or send a question to andre@andrespeek.com. Let me know if you got inspired and stay safe and healthy always!