Database mail is cool…

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:

EXEC dbo.sp_send_dbmail

@profile_name = 'YUKON01_Database_Mail',

@recipients = '<email address>',

@body = 'This is a query test',

@subject = 'Query Test',

@query = '<T-SQL Statement>',

@attach_query_result_as_file = 1 ;

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. 

Comments (2)
  1. sbekker says:

    We decided to not allow SMTP from the SQL server for the following reasons

    • We want to keep the SQL server as a database server rather than a application server.  

    • If we do not allow port 25 traffic, there’s less attack service.

Comments are closed.

Skip to main content