Configuring Database Mail in Sql Server 2005

I want to send a e-mail using msdb.dbo.sp_send_dbmail but I don’t know how to configure Sql Server 2005 so that it will work.  That was my delimma.  Oh, I googled it.  But everything I read said “Click on Management and then click on Database Mail and follow the wizard.”  For those of us coming from a Oracle background that was a little vague.  The trouble was the answer was right in front of my face.  Microsoft Sql Server Management Studio has a view called Object Explorer.  It is the tree window on the right by default when Management Studio opens.  Towards the bottom there is a node labeled “Management”.  Well, what do you know.  I opened it and found Database Mail.  I walked through the wizard and set up my SMTP server.  I did the test offered by right clicking on Database Mail but no test e-mail came to my configured account.  I realized I had the wrong password and tried again.  No, luck.  It then occured to me that the wizard wasn’t updating the profile.  I deleted the profile put it in correctly and it worked.  Oh, and I have forgot to mention I had to use the Surface Area Configuration application to enable Database Mail.  Once that was all done.  I could look at the log of what was happening by right clicking on Database Mail and I could write queries on msdb like these to check how things were going include what was sent:

SELECT * FROM msdb.dbo.sysmail_allitems order by mailitem_id desc;

SELECT * FROM msdb.dbo.sysmail_event_log WHERE mailitem_id = 4 ;

It was frustating not finding the information I needed and being blind to the obvious.  I hope this helps other avoid my mistakes.  I’d be glad to answer any question if you will comment on the post.

Leave a Reply

Your email address will not be published. Required fields are marked *