Did you know you can hook up your SQL server (or Managed SQL on Azure) to an SMTP server and use it to send email. Terrible idea? Yes, probably. I really encourage people not to build business logic that might require creating an email into stored procs. Required for legacy code? Yes, certainly.
You first need to tell SQL server how to talk to the mail server. This is done using the sysmail_add_Account_sp
With that in place you can set up a mail profile to use it
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
SELECT @sequence_number = COALESCE(MAX(profile_id),1) FROM msdb.dbo.sysmail_profile;
-- Create a mail profile EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'Database Mail Profile', @description = 'Sends email from the db'; -- Add the account to the profile EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Database Mail Profile', @account_name = 'Database Mail Account', @sequence_number = @sequence_number; -- Grant access to the profile to the DBMailUsers role EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'Database Mail Profile', @principal_id = 0, @is_default = 1 ;
You can then make use of sp_send_dbmail to send email
1 2 3 4 5
EXEC msdb.dbo.[sp_send_dbmail] @profile_name = 'Database Mail Profile', @recipients = 'firstname.lastname@example.org', @subject = 'Testing db email', @body = 'Hello friend, I''m testing the database mail'
You can check the status of the sent email by querying
select * from msdb.dbo.sysmail_allitems
If things fail then checking the event log may be helpful