Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database mail and operator not working together


Database mail and operator not working together

Author
Message
lijusmail
lijusmail
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 193
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
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
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
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

lijusmail
lijusmail
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 193
Thank you so much....that did it....it was hiding there...
appreciate your help...
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4463 Visits: 9833
You are welcome - glad I could help.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

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

marsh_peter
marsh_peter
SSC-Enthusiastic
SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)SSC-Enthusiastic (169 reputation)

Group: General Forum Members
Points: 169 Visits: 770
Fixed it for me too !
John Mitchell-245523
John Mitchell-245523
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7466 Visits: 15142
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search