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.