Results 1 to 6 of 6

Thread: Database mail

  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Unanswered: Database mail

    I have enabled database mail and created an SMTP address for the mail messages to go to. I also created myself as an operator. When I created my maintenance plan I selected the correct profile and requested that notifications be sent on the backup jobs. In SQL Server Agent I enabled the mail session and also enabled the Fail-safe operator (myself) to receive the notices, but I have not received anything. When I do a test email through database mail I receive the test email. When I check the log files there is an error message stating: The Messenger service has not been started - NetSend notifications will not be sent. Can someone please tell me what I am doing wrong?

  2. #2
    Join Date
    Feb 2010
    Posts
    6
    I am using SQL 2005 by the way.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Stop and start SQLAgent
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Feb 2010
    Posts
    6
    I did that and it didn't seem to help. I restarted SQL Server Agent through SMSS, should I have gone into the server services and done it there instead?

  5. #5
    Join Date
    Feb 2010
    Posts
    6
    I have stopped and restared SQLAgent multiple times and this has not fixed the issue. Any more ideas?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I have made some changes to this recently but not tested it. As such, use at your own risk:
    Code:
    USE msdb
    go
    
    --------------------------------------------------------------------------------------
    --      PREP - ENABLE DATABASE MAIL & PREPARE ALL VARIABLES
    --------------------------------------------------------------------------------------
    EXEC sp_configure 'Database Mail XPs', 1
    RECONFIGURE
    GO
    
    DECLARE   @account_name     AS SYSNAME          = N'pootle flump'
            , @profile_name     AS SYSNAME          = N'pootle flump'
            , @operator_name    AS SYSNAME          = N'pootle flump'
            , @email_address    AS NVARCHAR(128)    = N'pootle.flump@flumptowers.co.uk'
            , @mailserver_name  AS NVARCHAR(128)    = N'checkdamails'
            , @display_name     AS SYSNAME          = N'pootle flump'
            , @job_name         AS SYSNAME          = N'testing testing 1 2 3'
            , @service_account  AS NVARCHAR(100)
    /*
        Stopping and starting the agent after enabling Database Mail in SQL Server Agent has been found
        combat a bug where emails are not sent once job completes despite all settings being correct.
        http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/32a2c09a-156f-43b2-9076-b11ba9ccdafb/
    */
            , @agent_on         AS SYSNAME          = N'net start SQLSERVERAGENT'
            , @agent_off        AS SYSNAME          = N'net stop SQLSERVERAGENT'
    /*
        For SQL Server Service account to start and stop SQLAgent, we need to grant permissions (without making it local admin!)'
        http://www.microsoft.com/downloads/details.aspx?FamilyID=E8BA3E56-D8FE-4A91-93CF-ED6985E3927B&displaylang=en
    */
            , @service_command  AS NVARCHAR(1000)   = N'"C:\Program Files\Windows Resource Kits\Tools\SUBINACL.exe" /SERVICE SQLServerAgent /GRANT=[@service_account]'
            , @test_emails      AS BIT              = 1
    
    --Get SQL Server service account
    EXECUTE master.dbo.xp_instance_regread
              N'HKEY_LOCAL_MACHINE'
            , N'SYSTEM\CurrentControlSet\Services\MSSQLSERVER'
            , N'ObjectName'
            , @service_account OUTPUT
            , N'no_output'
            
    SET     @service_command    = REPLACE(@service_command, '[@service_account]', @service_account)
    --------------------------------------------------------------------------------------
    --      END PREP
    --------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------
    --      CLEAN UP
    --------------------------------------------------------------------------------------
    IF EXISTS (SELECT NULL FROM dbo.sysmail_account WHERE name = @account_name) 
    BEGIN
    
        EXECUTE msdb.dbo.sysmail_delete_account_sp
                @account_name   = @account_name
    
    END
    
    IF EXISTS (SELECT NULL FROM dbo.sysmail_profile WHERE name = @profile_name) 
    BEGIN
    
        EXECUTE msdb.dbo.sysmail_delete_profile_sp
                @profile_name   = @profile_name
    
    END
    
    IF EXISTS (SELECT NULL FROM dbo.sysoperators WHERE name = @operator_name) 
    BEGIN
    
        EXECUTE msdb.dbo.sp_delete_operator
                @name           = @operator_name
    
    END
    --------------------------------------------------------------------------------------
    --      END CLEAN UP
    --------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------
    --      DATABASE MAIL OBJECTS
    --------------------------------------------------------------------------------------
    -- Create a Database Mail account
    EXECUTE msdb.dbo.sysmail_add_account_sp
              @account_name             = @account_name
            , @description              = ''
            , @email_address            = @email_address
            , @replyto_address          = @email_address
            , @display_name             = @display_name
            , @mailserver_name          = @mailserver_name
            , @use_default_credentials  = 1;
    
    -- Create a Database Mail profile
    EXECUTE msdb.dbo.sysmail_add_profile_sp
              @profile_name     = @profile_name
            , @description      = '';
    
    -- Add the account to the profile
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
              @profile_name     = @profile_name
            , @account_name     = @account_name
            , @sequence_number  = 1;
    
    -- Grant access to the profile to all users in the msdb database
    EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
              @profile_name     = @profile_name
            , @principal_name   = 'public'
            , @is_default       = 1;
    --------------------------------------------------------------------------------------
    --      DATABASE MAIL OBJECTS
    --------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------
    --      SQL SERVER AGENT OBJECTS & PROCESSES
    --------------------------------------------------------------------------------------
    -- Add operator to SQL Server Agent
    EXECUTE dbo.sp_add_operator
              @name             = @operator_name
            , @enabled          = 1
            , @email_address    = @email_address;
    
    
    -- Enable Database Mail in SQL Server Agent
    EXECUTE master.dbo.xp_instance_regwrite 
              N'HKEY_LOCAL_MACHINE'
            , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
            , N'DatabaseMailProfile'
            , N'REG_SZ'
            , N''
    
    EXECUTE master.dbo.xp_cmdshell @service_command, no_output 
    EXECUTE master.dbo.xp_cmdshell @agent_off, no_output 
    EXECUTE master.dbo.xp_cmdshell @agent_on, no_output 
            
    -- Enable Database Mail in SQL Server Agent
    EXECUTE master.dbo.xp_instance_regwrite 
              N'HKEY_LOCAL_MACHINE'
            , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
            , N'DatabaseMailProfile'
            , N'REG_SZ'
            , @operator_name
    
    EXECUTE master.dbo.xp_cmdshell @agent_off, no_output 
    EXECUTE master.dbo.xp_cmdshell @agent_on, no_output
    
    -- Scrub then add the operator to the job notification alert thingy
    IF EXISTS (SELECT NULL FROM dbo.sysjobs WHERE name = @job_name AND notify_email_operator_id <> 0) 
    BEGIN
    
        EXECUTE msdb.dbo.sp_update_job 
              @job_name                     = @job_name
            , @notify_email_operator_name   = N''
    
    END
    
    EXECUTE msdb.dbo.sp_update_job 
              @job_name                     = @job_name
            , @notify_email_operator_name   = @operator_name
            , @notify_level_email           = 3--Always
    
    --Remove service restart permissions ** CHECK    YOU WANT THIS REMOVED **
    SELECT    @service_command = REPLACE(@service_command, '/GRANT', '/REVOKE')
    EXECUTE master.dbo.xp_cmdshell @service_command, no_output
    --------------------------------------------------------------------------------------
    --      SQL SERVER AGENTS OBJECTS & PROCESSES
    --------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------
    --      TESTING OPERATOR AND DBMAIL
    --------------------------------------------------------------------------------------
    IF @test_emails = 1 
    BEGIN
    
        EXECUTE msdb.dbo.sp_send_dbmail
              @profile_name     = @profile_name
            , @recipients       = @email_address
            , @body             = 'This is a test of msdb.dbo.sp_send_dbmail'
            , @subject          = 'Test email (sp_send_dbmail)'
            , @body_format      = 'HTML'
        
        EXECUTE msdb.dbo.sp_notify_operator
             @profile_name      = @profile_name
           , @name              = @operator_name
           , @body              = N'This is a test of msdb.dbo.sp_notify_operator'
           , @subject           = N'Test email (sp_notify_operator)'
    
    END
    --------------------------------------------------------------------------------------
    --      END TESTING OPERATOR AND DBMAIL
    --------------------------------------------------------------------------------------
    
    --------------------------------------------------------------------------------------
    --      RUN THE JOB & CHECK FOR AN EMAIL
    --------------------------------------------------------------------------------------
    EXECUTE msdb.dbo.sp_start_job 
         @job_name           = @job_name
    --------------------------------------------------------------------------------------
    --      RUN THE JOB & CHECK FOR AN EMAIL
    --------------------------------------------------------------------------------------
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •