In my daily job as a SQL Server consultant I go to a lot of places, many days on the road, and many nights at different hotels. All these things are a part of the job, so of course I am not complaining.

What some times can be a challenge is to have access to the same SMTP server again and again, and especially when I am testing something with Database Mail, SQL Server alerts, Operators or something else that uses a SMTP server from SQL Server to send out an email.

Because of these challenges I normally use a Gmail account to send mails from, Gmail requires authentication to send mails via their SMTP servers, so an account type like this can be used from anywhere.

So many people have blogged about this before me, including myself actually – I blogged some thing like this a few years back – in DANISH. I always struggle to find my script to configure these things, so that is why I am blogging about this again – to refresh my brain, and to share my code with You.

Before we can start playing with Database mail, we have to enable the feature, that is done with the following code:

EXEC sp_Configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO

With the feature enabled, all there is left to do before sending mails from SQL Server, is to changes the following script so that is takes you account info, and not the bogus info that I have entered:

-- Create a Database Mail account
EXEC msdb.dbo.sysmail_add_account_sp
      @account_name = 'MyMailAccount'
    , @email_address = 'me@gmail.com'
    , @display_name = 'My Servers Database Mail Account'
    , @description = 'MyMailAccount'
    , @mailserver_name = 'smtp.gmail.com'
    , @port = 587
    , @username = 'me@gmail.com'
    , @password = 'mYpASSWORD'
    , @enable_ssl = 1
-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'MyPublicProfile',
    @description = 'My Public Profile';
-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'MyPublicProfile',
    @account_name = 'MyMailAccount',
    @sequence_number = 1;
-- Configuring global profile, and setting default mail profile
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'MyPublicProfile',
    @principal_name = 'public',
    @is_default = 1;
GO

After execution this script, we are ready to send out an test mail, to check that everything is working, here is the code to do that:

EXECUTE msdb.dbo.sp_send_dbmail
    @subject = 'Test Database Mail Message',
    @recipients = 'me@myemail.com',
    @query = 'set nocount on;SELECT Getdate() as ServerTime';

Wait one second and you should receive an email from you SQL Server. In the next blog post we’ll look at how to monitor and debug Database mail – stay tuned for that next week.

Before I finish this blog post totally, I’ll just show you a few functions and views that you can use to look at the profiles and accounts created. And another god thing to have handy is how to delete the account/profile just created.

exec msdb.dbo.sysmail_delete_account_sp @account_name = 'MyMailAccount'
exec msdb.dbo.sysmail_delete_profile_sp @profile_name = 'MyPublicProfile'
select * from msdb.dbo.sysmail_account
select * from msdb.dbo.sysmail_profile