October 14, 2009 at 12:06 am
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
October 14, 2009 at 1:21 pm
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
October 15, 2009 at 5:48 am
Thank you so much....that did it....it was hiding there...
appreciate your help...
October 15, 2009 at 7:40 am
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
April 25, 2013 at 4:23 am
Fixed it for me too !
April 25, 2013 at 4:35 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply