Database mail and operator not working together

Question

Database mail and operator not working together

lijusmail

Default port

Points: 1486
More actions
October 14, 2009 at 12:06 am

#227012

Hi
I have set up DBMail for sending out mails during success of a job.but when i run the job and it succeeds, it does not send the mail.
Setup.
1. Enabled Database Mail Stored proc for the instance of DB.
2. Configured Mail by setting up the account and the profile for sending the mails.
3. Did a test mail and the mail is reaching me.(So i know it is working fine).
4. Created an operator and set that as my mail id.
4.Created a job for doing database backup everynight and then in notifications gave email option as the operator i have created.
5. Ran the job and is success, but i dont receive the mail.
I have setup the same on another instance and is working fine.dont know why on this one it is not working.
please help

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

Next step: Configure SQL Server Agent to use Database Mail.

Right-click SQL Server Agent in the Object Explorer, select properties and setup the alert properties to use Database Mail. Once done, stop and start SQL Server Agent.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

lijusmail Default port Points: 1486 More actions · Answer 2

Thank you so much....that did it....it was hiding there...

appreciate your help...

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 3

You are welcome - glad I could help.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

marsh_peter SSC Veteran Points: 247 More actions · Answer 4

marsh_peter

SSC Veteran

Points: 247

April 25, 2013 at 4:23 am

#1609853

Fixed it for me too !

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

I use the script below for setting up Database Mail. It's useful if you regularly build new servers or set up Database Mail on existing ones. You should be able to tweak it to make it work in your environment:

DECLARE @account sysname

DECLARE @email nvarchar(128)

SET @account = CAST(SERVERPROPERTY('ServerName') AS sysname)

SELECT @account = REPLACE(@account,'\','_')

,@email = REPLACE(@account,'\','.')+'@MyCompany.co.uk'

-- Create a Database Mail account

EXEC msdb.dbo.sysmail_add_account_sp

@account_name = @account,

@email_address = @email,

@replyto_address = 'MyAddress@MyCompany.co.uk',

@display_name = @account,

@mailserver_name = 'MyExchangeServer.MyCompany.co.uk',

@port = 25 ;

-- Create a Database Mail profile

EXEC msdb.dbo.sysmail_add_profile_sp

@profile_name = @account ;

-- Add the account to the profile

EXEC msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = @account,

@account_name = @account,

@sequence_number = 1 ;

-- Grant the DBMailUsers role access to the profile

EXEC msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = @account,

@principal_name = 'public',

@is_default = 1 ;

-- Add users to role to allow them to use sp_send_dbmail

DECLARE @sql varchar(4000)

SELECT @sql = 'USE msdb' + CHAR(13)

SELECT

@sql = @sql + 'EXEC sp_addrolemember ''DatabaseMailUserRole'', ''' + [name] + '''' + CHAR(13)

FROM syslogins

WHERE isntgroup=1 AND sysadmin=1

EXEC (@sql)

-- Enable the profile in SQL Server Agent

EXEC master.dbo.xp_instance_regwrite

@rootkey= N'HKEY_LOCAL_MACHINE'

,@key= N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'

,@value_name = N'DatabaseMailProfile'

,@type= N'REG_SZ'

,@value= @account

-- Verify the correct profile name is enabled

EXEC master.dbo.xp_instance_regread

@rootkey= N'HKEY_LOCAL_MACHINE'

,@key= N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'

,@value_name = N'DatabaseMailProfile'

John