Problem: I need a stored procedure that checks bugs in the support system and find out if any of them have not been responded or completed in the scheduled time and send out emails to different people according to how urgent they are. The stored procedure is fairly easy to write except to work out the working days. Anyway, the focus here is how to send email from SQL Server.
Solution: The stored procedure xp_sendmail is not recommended to use in 2005, so now we use sp_send_dbmail. First, we need to enable database mail. Right-click on Management node and select Configure Database Mail option, you will be prompted to enable this option if it was not enabled, and setup through the wizard.
Now use sp_send_dbmail like the following, that’s probably the minimum you need to fill in to execute this stored procedure:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'DBA Team',
@recipients = 'emily@sample.com',
@body = 'and don't tell me those are features',
@subject = 'you have millions of bugs haven't been fixed yet, work harder!'
and of course you can compose a prettier HTML email, with the help of FOR XML PATH, you can have a fancy table in your email as well.
@body_format = 'html'
When I try to run this, an error comes up about my @profile_name been wrong or not exists, this is easy to solve, just right-click SQL Server Agent, and then click Properties. On Alert System, select Enable Mail Profile, in the Mail system list, select Database Mail, in the Mail profile list, select a mail profile for Database Mail and that’s it.
