Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database mail and operator not working together Expand / Collapse
Author
Message
Posted Wednesday, October 14, 2009 12:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
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
Post #802525
Posted Wednesday, October 14, 2009 1:21 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 4,363, Visits: 9,545
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #803054
Posted Thursday, October 15, 2009 5:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 18, 2013 11:45 PM
Points: 34, Visits: 193
Thank you so much....that did it....it was hiding there...
appreciate your help...
Post #803352
Posted Thursday, October 15, 2009 7:40 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:14 PM
Points: 4,363, Visits: 9,545
You are welcome - glad I could help.

Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #803440
Posted Thursday, April 25, 2013 4:23 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, November 19, 2014 8:30 AM
Points: 118, Visits: 483
Fixed it for me too !
Post #1446392
Posted Thursday, April 25, 2013 4:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 9:42 AM
Points: 5,436, Visits: 10,116
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
Post #1446398
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse