1

  1. I prepared scripts for sysadmin, for creating and configuring database mail profile, account and operator. He run the script, so I created temporary job, which fails. Then I set e-mail notification when the job fails. I run the Job, but no e-mail has been sent. Then I tried to run msdb.dbo.sp_send_dbmail procedure, using the same operator and I received the e-mail. What can be the reason of not sending e-mail when the job fails? Which procedure is being run in case of using e-mail notification when the job fails - is it different than sp_send_dbmail?

  2. The other thing is that in database mail log no there is no information about sending e-mails status (it is empty) - maybe I have no permissions to see the log?

  3. I set the profile as public, running dbo.sysmail_add_principalprofile_sp procedure, but when I try to send the e-mail running msdb.dbo.sp_send_dbmail a logged as dbowner I get an error: The EXECUTE permission was denied.. Why it only works when I am logged as my user?

I'm using SQL Server 2008.

Here is a T-SQL code, that I passed to sysadmin for creating and configuring database mail.

-- Create a Database Mail account

EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'Mail Account',
    @description = 'Mail account for administrative e-mail.',
    @email_address = 'xx@xx.pl',
    @display_name = 'Job failure notification',
    @mailserver_name = 'xx.xx.xxx' ,
    @username = 'aaa',
    @password = 'xxx'

-- Create a Database Mail profile


 EXECUTE msdb.dbo.sysmail_add_profile_sp
    @profile_name = 'Database Mail Profile',
    @description = 'Profile used for job failure notifications.' ;

-- Add the account to the profile


EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
    @profile_name = 'Database Mail Profile',
    @account_name = 'Mail Account',
    @sequence_number =1 ;

-- Enable Mail profile in SQL Agent


EXEC master.dbo.xp_instance_regwrite 
      N'HKEY_LOCAL_MACHINE', 
      N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', 
      N'DatabaseMailProfile', 
      N'REG_SZ', 
      N'Database Mail Profile'
GO

-- Create new operator


EXEC msdb.dbo.sp_add_operator @name=N'SQL Job Failure', 
            @enabled=1, 
            @weekday_pager_start_time=90000, 
            @weekday_pager_end_time=180000, 
            @saturday_pager_start_time=90000, 
            @saturday_pager_end_time=180000, 
            @sunday_pager_start_time=90000, 
            @sunday_pager_end_time=180000, 
            @pager_days=0, 
            @email_address=N'xx@xx.pl', 
            @category_name=N'[Uncategorized]'
GO

-- Setting profile as public

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @profile_name = 'Database Mail Profile',
    @principal_name = 'public',
    @is_default = 0 ;
Konrad Z.
  • 1,592
  • 4
  • 20
  • 34

1 Answers1

7

I think you have to restart SQL Agent after executing those scripts. Check

  • what is set in the job notification page - email checkbox must be checked and the operator must be defined
  • Right click on Sql Agent -> Properties - Alert Settings - email settings must be provided

Check out this article in order to make sure you haven't missed anything.

dferidarov
  • 602
  • 4
  • 15
  • Hello dferidarov, thanks for your response. Unfortunately I am not able to check everything using GUI (no permissions). I attached T-SQL code, that was run for creating database mail. But you might be right, that it requires SQL Agent - I'll check it. – Konrad Z. Apr 04 '13 at 11:40
  • Unfortunately it didn't help. It is very strange, because on test environment running this script and restarting SQL Agent, causes sending e-mail when job fails. I have no idea where is the problem. – Konrad Z. Apr 05 '13 at 10:32
  • Have you checked the article that i've posted in my answer for anything missed (like Sql Agent Properties-> Alert Settings) ? – dferidarov Apr 05 '13 at 11:05
  • Yes, but I have no permission to check if the proper profile is enabled in SQL Agent. But you might be right, that there is probably the issue. I checked that part of my script responsible for enabling profile in SQL Agent works correctly only if there is no other profile assigned to the SQL Agent yet. If there is it will not overwrite the current one. – Konrad Z. Apr 05 '13 at 13:40
  • By defult, this SQL Agent Alert settings are not configured to any mail profiler i think. – dferidarov Apr 05 '13 at 13:49
  • But someone before me could add a profile. As far as I see in system table there is already created database mail profile other than mine. I gues, that it is assigned to the SQL Agent profile either and this is why my script does not work. – Konrad Z. Apr 05 '13 at 13:56
  • Database mail profile is properly assigned to SQL Agent. Do you know maybe, within which user e-mails are being sent (in terms of using it as Job notifications)? – Konrad Z. Apr 08 '13 at 11:17
  • "To send Database Mail, you must be a member of the DatabaseMailUserRole database role in the msdb database." - from [msdn](http://msdn.microsoft.com/en-us/library/ms175887(v=sql.105).aspx). Also check the following links [TroubleShooting Databse Mail](http://msdn.microsoft.com/en-us/library/ms187540(v=sql.105).aspx) and [Configuring SQL Agent](http://msdn.microsoft.com/en-us/library/ms186358.aspx) – dferidarov Apr 08 '13 at 11:40
  • I added dbowner to DatabaseMailUserRole and created job by logging as dbowner but it didn't help. Still can send e-mails running sp_send_dbmail procedure only. For the job notification i get: NOTE: Failed to notify '' via email. – Konrad Z. Apr 08 '13 at 13:19
  • I got it - check if sql agent has enabled Alert system. Check you settings according [link1](http://www.msqlserver.com/2011/04/failed-to-notify-operator-name-via.html) , [link2](http://www.sqlservercentral.com/Forums/Topic253575-149-1.aspx0). – dferidarov Apr 08 '13 at 13:45
  • Yes, SQL Agent has enabled Alert System. And it has already been restarted, but there is still the same problem. – Konrad Z. Apr 08 '13 at 14:20
  • I am running out of ideas... Check the DB Mail log with `SELECT * FROM msdb.dbo.sysmail_event_log;` and read the link that i posted up - TroubleShooting Databse Mail by trying to troubleshoot from the beginning. – dferidarov Apr 08 '13 at 14:39