SQL Server Express Edition (EE) is great for users who want to run small- and medium-sized corporate applications with solid database background without the need to spend money for full SQL Server package, however for me this free version had some lacks which made it less functional in some specific cases. One of the most important features which I missed was the ability to send e-mails directly from SQL Server EE so I googled a bit and found a solution for this problem. It seems that the Express Edition of SQL Server 2008 comes with everything needed to set up sending e-mails except the nice looking GUI interface which lets the user to easily configure it, so I thought this short guide will be helpful for users looking for solution of this problem, because you’ll find everything what’s needed in one place, without the need to scroll through hundreds of web pages.
So, let’s get to the work!
What we need to do first is to enable Database Mail in our SQL Server EE instance. To do it we need to execute following query:
USE msdb GO --Enable Database Mail sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Database Mail XPs', 1; GO RECONFIGURE; GO
If the above code will execute without any error we can start configuring our Database Mail. First let’s add “fake” mail account which will be used as a sender of each mail we generate in our database. We can do it by executing following query:
USE msdb --Configure mail account insert into sysmail_account values ('account name', 'account description', 'account@address', 'display name', 'email@example.com', '2012-01-01 00:00:00', 'created by...') --Check what you've just done select * from sysmail_account
Next we need to create a profile which will be used when sending e-mails:
USE msdb --Configure profile insert into sysmail_profile values ('profile name', 'profile description', '2012-01-01 00:00:00', 'created by') select * from sysmail_profile
In the next step we need to link our profile with account created in first step. We do this by executing following query:
USE msdb --Link mail account with profile insert into sysmail_profileaccount values (1, 1, 1, '2012-01-01 00:00:00', 'created by') select * from sysmail_profileaccount
As a last configuration step we need to add a mail server which will be used to send our e-mails:
USE msdb --Configure server insert into sysmail_server values (1, 'SMTP', 'smtp.server.address', port, user.or.null, pass.or.null, 1, 0, 0, 60, '2012-01-01 00:00:00', 'created by') select * from sysmail_server
After making all the configuration we can check our results by trying to send some test e-mail. We can do it by executing:
send mail EXEC sp_send_dbmail @profile_name='profile name', @firstname.lastname@example.org', @subject='Test message', @body='If you see this in your mailbox that means Database Mail has been correctly set up. Enjoy!'
After a while you should check your mail box. There’s something waiting for you.
As a last word I want to attach a link to MSDN documentation concerning sp_send_dbmail stored procedure. You can find it here.
That’s all! I hope you enjoyed this article!