Automated birthday email

I have a SQL server 2005 database. I want to send a user an email when it's his birthday. His birthdate is stored in the "birthdate" column...
How can I automate this?

You can use xp_sendmail to send the birthday emails. Please follow these steps to configure:

1. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Surface Area Configuration'

2. Click 'Surface Configuration for Features'

3. Select the SQL2005 instance (MSSQLSERVER in my testing); explore 'Database Engine'->go to 'OLE Automation'-> select the 'Enable OLE Automation' checkbox; enable SQL Mail in the same way; Click 'OK' to save changes (seeServerConfig.jpg)

4. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Configuration Manager'

5. Go to 'SQL Server 2005 Services, double click on 'SQL Server (InstanceName)', in the 'Log On' panel, choose 'This account' to use a domain account to logon (fareast\t-leijie in my testing); Click 'OK' to save change (see ConfigLogonAccount.jpg)

6. Restart SQL2005 instance service.

7. Install a default mail client if you do not have yet (OutLook is good), make a mail profile ('iori' in my testing) using the domain account to send emails, make sure emails can be sent using successful the account.

8. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'SQL Server Management Studio'

9. Choose your instance, explore 'Management'->'Legacy'->'SQL Mail', double click to set Properties, choose the profile used for SQLMail; Click 'OK' to save change.

And you also need a continous running script to detect the "birthdate" column and send mail, such as:

DECLARE @.email varchar(100)
WHILE(1=1)
BEGIN
If exists(select email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
BEGIN
select @.email=email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = N'Happy Birthday my dear firend!';
END
END

|||Thanks for the explanation!

You made such a detailed list and still I don't completely understand it...;)

A few more questions:

1. I want the script to run once every day at 05:00 AM (to keep db load to a minimum)
2. I want to use a format in HTML for my message, this must contain something like: dear <name>, we like to congratlate you with your birthday. Also this entire email must be made up nicely with tables and pictures (which can be just static). Should I just put my entire email in 1 string and replace the <name> part with the username or can I import a .html file and THEN replace the <name> tag with the username? I like the second solution better...but do you know how to achieve this?

Wow, long questions...:p...hope you can help me out!

Thanks!|||It's my pleasure to discuss with you:)

1. You can schedule a job at 05:00 AM every day to accompish this, just open Enterprise Manager, explorerMySQLInstance->'Management'->'SQL Server Agent'->right click 'Jobs'->'New Job', then go to 'Steps' pannel to new a step. Choose 'T-SQL' as step type, and copy your T-SQL script used for sending email to the 'Command' field (as you'll schedule the script to run once a day, let's remove the 'WHILE(1=1)' loop). Then go to 'Schedules' pannel to schedule your job step. You can press F1 for detailed help. Sorry I wrote a wrong script in my previous post, for the case that there may be more than 2 persons have same birhday:

declare @.email varchar(100), @.name varchar(100)

select email,name into #tbl
from tbl_Birthday
where month(birthdate)=month(getdate())
and day(birthdate)=day(getdate())
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
WHILE (@.email is not null)
BEGIN
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = @.name,
@.attachments='c:\happyBirth.html'--you have use attachment if you want HTML feature
DELETE FROM #tbl whereemail=@.email
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
END
DROP TABLE #tbl

2. See the script above. You can customize the email message by adding the name, but need attchments to add HTML feature.