Intro to Database Mail in SQL 2005

  • Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/cBunch/introtodatabasemailinsql2005.asp

  • Folks,

    I cannot check this out until I get back to the office but, is database mail included with the express edition?

    If so it really saves my @ss.

    Thanks

    Mick

  • First let me say that the author did a good job of showing the advantages of the new mail system.

    With that said, though, as someone who has fought through SQL 2000 e-mail to the point where there isn't a killer reason to move over (this includes even queing email when the the SQL Server cannot reach the Exchange Server, logging, and auditing), the big issue for me is:

    "unless of course for backward compatibility, legacy applications, etc."

    Yesterday we heard that complex DTS packages will probably require work and don't hold one's breath for continued DTS support. What are the issues with Mail and what does the future hold?

    Sincerely yours,

    A late adopter if not skipping 2005 completely



    Everett Wilson
    ewilson10@yahoo.com

  • Very nice article on initial Database Mail configuration.  I wanted to point out that there are a few more steps required to have SQL Server Agent be able to use Database Mail for jobs, alerts, etc.  This is from SQL Server 2005 BOL, "Using Database Mail with SQL Server Agent":

    1. Enable Database Mail.

    2. Create a Database Mail account for the SQL Server Agent service account to use.

    3. Create a Database Mail profile for the SQL Server Agent service account to use and add the user to the DatabaseMailUserRole in the msdb database.
    4. Set the profile as the default profile for the msdb database.
    5. Choose Database Mail as the mail system for SQL Server Agent. For more information, see How to: Configure SQL Server Agent Mail to Use Database Mail (SQL Server Management Studio).
    6. Restart SQL Server Agent.

    (I struck through the steps that were covered in the article, assuming that the account and profile created are Ok to use for SQL Server Agent Mail.)

    It's also important to note that SQLMail issues typically fell into a couple of major categories:

    • Reliability problems
    • Configuration & maintenance overhead

    With SQLMail you could spend a lot of time installing Outlook, creating operators, assigning notifications to jobs, alerts, etc., on each and every SQL Server (Config overhead)... and even then it may or may not work (Reliability)  

    By taking MAPI/SQLMail out of the mix and going to SMTP with failover Microsoft has addressed biggest element of the "reliability" issue.  However, there is still the fact that Database Mail is a distributed system -- if there is some problem with it on one or more SQL Servers, then you may not receive your notifications for those servers.  In other words, although each individual Database Mail instance is inherently more reliable than SQLMail when properly configured and maintained, there is still no outside-looking-in process making objective decisions on the health of Database Mail on all servers, which goes towards "enterprise reliability".

    In terms of maintenance overhead, I am sure many of those familiar with setup of SQLMail will look at the Database Mail setup & config process and think, "Well, it sure is different... and easier in some respects... but it's still quite a bit of work".

    Database Mail still needs to be configured and tested on each SQL Server, operators need to be created, and the DBA must remember to assign notifications to all jobs & alerts, including new jobs & alerts added after initial setup.  When you start multiplying this by 50, 100, 250 or more SQL Servers as is commonplace now for many organizations, a migration from 2000/SQLMail to 2005/Database Mail starts to get a bit scary.

    Microsoft has certainly made a big move in the right direction with Database Mail, but there is still a ways to go before it can truly be considered an "enterprise friendly" notification solution.

    Greg Gonzalez

    sqlSentry

  • Actually, if SQLMail is set up use a queuing system the reliability issue can be avoided. After playing around with variuos tests the best solution turned out to be rather straight-forward. Once the email is in the queue send the email, if an error is reported then keep the email in the queue and shut down processing of the queue. In three+ years of production no email has been lost despite network/Exchange problems.



    Everett Wilson
    ewilson10@yahoo.com

  • I would add #7 to Greg's list:

    7. Issue a statement that send an email.

    I use Database Mail and for some reason it works. I am using a statement like that:

    EXEC dbo.sp_send_dbmail @recipients='yelena.varshal@philips.com',

        @subject = 'Subject of my Email,

        @query ='Select * from ##MyTempTable'

        @profile_name = 'DBAemail',

     @attach_query_result_as_file = 0

    I would also add #1.5 if applicable:

    1.5 Specify the database.

    In my CTP the next screen after you click Setup on the first screen tell you to select a certain database to install Messaging Objects. I do understand that this may change in the release that I am in the process of installing now.

     

    Regards,Yelena Varsha

  • "In my CTP the next screen after you click Setup on the first screen tell you to select a certain database to install Messaging Objects" - I suggest you check out the RTM. One change from early CTPs is that Database Mail is now msdb only. The ability to install multiple Database Mail instances has gone in RTM, and probably before, I don't recall exactly.

     

    Darren Green
    SQLDTS.com   |   SQLIS.com   |   Konesans Ltd

  •  

    Darren,

    Thanks for your explanations. I am in the process of installing RTM.

    Regards,Yelena Varsha

  • Thanks for the information.  I look forward to 2005 the more I read.

  • Great feedback from ewilson & Greg....clearly the organization's needs must be #1. If a lot of time has been invested in implementing SQL Mail, then clearly sticking with that infrastructure would be best, especially the larger the organization....I had hoped my catch-all "unless of course for backward compatibility, legacy applications, etc" captured that. Also, there are indeed extra steps to ensure that job alerts are using database mail...thanks Greg.

  • There was one thing missing in the implementation of 2005 database mail: Dynamic Profiles.

    The company I work for sends lots of email on behalf of our reseller customers (impersonating them).

    It took a ton of work to figure out how to do this and to supress some of the errors that were raised.

    There was also some big security settings that needed to be figured out before I could implement this functionality.

    since I can't attach the scripts directly, I'll add seperate posts for the steps involved.

     

     

     

     

  • Here's the security setup that's required:

    --create the role in msdb

    USE [msdb]

    GO

    /****** Object:  DatabaseRole [CanSendEmail]    Script Date: 11/03/2005 09:31:08 ******/

    CREATE ROLE [CanSendEmail] AUTHORIZATION [dbo]

    grant select on sysmail_account to CanSendEmail

    grant select on sysmail_profile to CanSendEmail

    grant select on sysmail_profileaccount to CanSendEmail

    grant execute on sysmail_add_principalprofile_sp to CanSendEmail

    grant execute on sysmail_add_profileaccount_sp to CanSendEmail

    grant execute on sysmail_add_account_sp to CanSendEmail

    grant execute on sysmail_add_profile_sp to CanSendEmail

    grant execute on sp_send_dbmail to CanSendEmail

    --create the login

    CREATE LOGIN [emailonlyuser] WITH <A href="mailtoASSWORD=N's@'">PASSWORD=N'somepassword', DEFAULT_DATABASE=[yourdatabase], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    use yourdatabase

    GO

    exec sp_adduser 'emailonlyuser'

    Grant execute on prcSendmail to emailonlyuser

    GO

    Use MSDB

    GO

    exec sp_adduser 'emailonlyuser','emailonlyuser','CanSendEmail'

    GO

     

  • Here is the text of the stored procedure:

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

     

    /* THIS IS A GENERAL SEND MAIL STORED PROCEDURE

    Written by Ron West (ronw@ronwest99.com)

    Dynamically creates accounts and profiles for Database

    mail in SQL 2005

    Make sure you run the permissions script before you create this proc.

    */ 

    Create PROCEDURE [dbo].[prcSendMail]  

     

    @Address varchar(max), 

    @Subject varchar(255), 

    @Body varchar(max), 

    @From varchar(50) = 'ops@yourdomain.com'

    @FromName varchar(100) = 'SQL Server', 

    @ReplyTo varchar(100) = 'ops@yourdomain.com'

    @BCC varchar(max) = null, 

    @cc varchar(max) = null,  

    @Attachment varchar(250) = null, 

    @HTML bit = 0, 

    @Sensitivity varchar(12)='Normal', 

    @Importance varchar(6)='Normal', 

    @Debug bit=0

    AS 

    BEGIN 

    SET NOCOUNT ON 

    Declare @body_format varchar(20),@mailitem_id int 

    declare @Success bit  

    declare @response varchar(255),@Name varchar(130) 

    declare @ProfileName sysname 

    Declare @AccountID int,@ProfileID int,@mailItemId int 

     

    if @Importance not in ('Low','Normal','High') 

     Set @Importance ='Normal' 

     

    if @Sensitivity not in ('Normal','Personal','Private','Confidential') 

     Set @Sensitivity='Normal' 

     

    if ltrim(rtrim(@FromName))='' or @FromName is null 

     set @FromName=@From 

     

    --this will be the key for the Profile AND Account 

    Set @ProfileName =@From + '/' + @FromName 

     

    if @HTML = 1  

     Set @body_format='HTML' 

    Else 

     Set @body_format='TEXT' 

     

     

    /************************************************** 

    Make sure the account exists and it's part of this profile 

    **************************************************/ 

    SELECT @AccountID=Account_ID FROM msdb.dbo.sysmail_account WHERE [name]=@profilename 

    SELECT @ProfileID=Profile_ID FROM msdb.dbo.sysmail_profile WHERE [name]=@profilename 

     

    if @Debug=1 

    Begin 

     Print '@AccountID=' + isnull(cast(@AccountID as varchar),'was null') 

     Print '@ProfileID=' + isnull(cast(@ProfileID as varchar),'was null') 

    End 

     

    --create the profile if needed 

    if @ProfileID is null 

    Begin 

     EXEC msdb.dbo.sysmail_add_profile_sp 

        @profile_name = @ProfileName, 

        @description = @ProfileName, 

        @profile_id =@ProfileID output 

     

     --give everybody access to use this profile 

     EXEC msdb.dbo.sysmail_add_principalprofile_sp 

      @profile_id = @ProfileID, 

      @principal_name = 'public', 

      @is_default = 0 ; 

    End 

     

     

    --create the account (same name as profile) if needed 

    If @AccountID is null 

     EXEC msdb.dbo.sysmail_add_account_sp 

        @account_name = @ProfileName, 

        @description = @From, 

        @email_address = @From, 

        @replyto_address = @ReplyTo, 

        @display_name = @FromName, 

        @mailserver_name = 'mail.yourdomain.com', 

        @Account_Id=@AccountID OUTPUT; 

     

    --create the relationship of account to profile 

    If not exists (Select * from msdb.dbo.sysmail_profileaccount where <A href="mailtorofile_Id=@ProfileID">Profile_Id=@ProfileID and Account_Id=@AccountID

     exec msdb.dbo.sysmail_add_profileaccount_sp  

       @profile_id =@ProfileID

       @account_Id=@AccountID, 

       @sequence_number=1; --first in line 

     

    if @Debug=1 

    Begin 

     Print '@AccountID=' + isnull(cast(@AccountID as varchar),'was null') 

     Print '@ProfileID=' + isnull(cast(@ProfileID as varchar),'was null') 

    End 

     

    /********************************************************** 

    Now, send the mail using the specific account 

    **********************************************************/ 

    EXEC msdb.dbo.sp_send_dbmail 

        @profile_name = @ProfileName, 

        @recipients = @Address, 

        @subject = @Subject, 

     @body = @Body, 

     @Importance=@Importance, 

     @Sensitivity =@Sensitivity

     @copy_recipients=@CC, 

     @blind_copy_recipients=@BCC, 

     @body_format=@body_format, 

     @file_attachments=@Attachment, 

     @mailitem_id = @mailitem_id OUTPUT 

     

    Return(isnull(@mailitem_id, 0)) 

    END 

     

     

  • Really expected more from a professional. Although the content of the article was good, it was considerably deficient in providing any substantial content beyond 'point, click and turned on'. OK, well, what next?? . Had it not been for the reply posts, there really was not much to learn from.

    THAT SAID: I do appreciate the fact that all of you, including the author, are willing to spend the time to create these posts. But please keep in mind, these posts don't benefit professionals, they benefit those who are learning.

    Thank you,

  • I appreciated Ron West's followup posting of dynamically setting up email profiles/accounts via TSQL. I was recently questioned on sql05 abilities of producing emails with varible originating names which I know can be done manually.  We have an CRM application and the challenge was to create an email from a triggered activity with the contactee's name in the "from" box.  Rather than manage hundreds of profiles/accounts manually, the "on-the-fly" approach was the better scenario.  Ron's code, with some mods, is just what I'm looking for!

    Much Thanks!

    Todd Wilson


    Td Wilson

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply