I'm now a big fan of database mail in SQL Server 2005 - it is so much better than SQLMail. For starters, it is now cluster-aware (customers have been asking for this for a while now), supports SMTP and takes advantage of service broker. When database mail is configured, it is at the user database level. Various objects are installed in the database being enabled for mail. The stored procedure, sp_send_dbmail, is one of these objects and it takes a variety of parameters e.g. queries can be used and the results can be attached as a file. The following script provides a simple example:
There are also two DMV's which can be queried to look at the metadata and determine when the last message was sent; these are sysmail_mailitems & sysmail_log. Please feel free to post any comments on your thoughts regarding database mail.
Hi everyone, just thought I'd update you all regarding SQL Server 2005 CTP releases and some interesting things I picked up at SQLPass 05 in Munich (it's always nice to speak to partners/customers about their thoughts and experiences).
As you probably know, the April CTP (Community Technology Preview) of SQL Server 2005 is now available for download from MSDN. The build quality is very very good. There will be a new CTP out this month although I'm not sure on specific dates. My interest in SQL Server 2005 Integration Services (SSIS) is growing, especially after I saw some very cool demos by Darren Green & Alan Mitchell (they run the www.sqldts.com and www.sqlis.com websites). I particulary like the ability to recover/restart a package from the point of failure using checkpointing. I also saw some some impressive SSIS performance figures on 32 bit and 64 bit which blow away DTS in SQL Server 2000.
There are some nice additions in the tools arena, for example, the ability to see graphical displays of deadlock detection and showplan in Profiler. SQLMail is no more; it has been replace by Database Mail which supports SMTP and is cluster-aware. I also like the Surface Area Configuration Tool (I wish the marketing guys thought of a better name) which allows you to enable/disable various features e.g. xp_cmdshell, SQLMail legacy support, sp_OACreate etc
I should also mention that there will be a lot of excellent SQL Server 2005 material presented at this years TechEd conference in Europe - I'm also planning to be there helping out in the Ask the Experts area. Hope you can make it - go here for more information http://www.mseventseurope.com/teched/05/pre/content/default.aspx.