SQL Server Operators – SQL Agent Alert System – Notify Operators

24-Nov-1111 Leave a comment

After adding SQL Operators you need to restart SQL Agent

ERROR in SQL Agent Jobs
… NOTE: Failed to notify ‘<operator name>’ via email.

[476] Database Mail is not enabled for agent notifications. Cannot send e-mail to
[475] Database Mail is not enabled for agent notifications.

May have log in SSMS > SQL Server Agent > Error Logs

SOLVED
Credit to:
http://sqlblog.jfperry.com/jpsql/post/2009/04/07/NOTE-Failed-to-notify-7boperator7d-via-email.aspx

This was set up on one of my work servers. For a different company this needed to be set up.

On SQL 2005+ you have to tell SQL Server Agent / Properties / Alert System, which profile that you’d like to enable. Here’s what it does.
e.g. and don’t forget to restart SQL Agent

To set up SQL Server Agent Mail to use Database Mail

  1. In Object Explorer, expand a server.
  2. Right-click SQL Server Agent, and then click Properties.
  3. Click Alert System.
  4. Select Enable Mail Profile.
  5. In the Mail system list, choose Database Mail.
  6. In the Mail profile list, select a mail profile for Database Mail.

Or less likely Transact SQL

USE [msdb]
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'MyDatabaseProfile'
GO

Filed under Errors, SQL Configuration, SQL Server Tagged with ,

SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

29-Sep-1111 Leave a comment

ERROR:
SQL Agent job calling stored procedure msdb.dbo.sp_send_dbmail @query does not return any records

SOLUTION
Add the Database Name into the @query

BACKGROUND
This may be because of the default database of the account which executes the SQL Agent job.

EXAMPLE
If @query does not include the database name (three-part naming), it is possible that although the EXISTS may fire, the @query does not.
To solve add the Database Name into the @query.

BEGIN TRY

IF EXISTS(SELECT * FROM Production.vAuditSOCDBTable)
  BEGIN
   DECLARE @MyBody nvarchar(100)
   SET @MyBody = ‘<message>’ + char(13) + char(10)

   EXEC msdb.dbo.sp_send_dbmail
    @profile_name = ‘<>’,
    @recipients = ”,
    @blind_copy_recipients = ”,
    @query = ‘SELECT * FROM <databasename>..’,
    –or possibly @execute_query_database = ‘databasename’,
    @subject = ‘subject’,
    –@attach_query_result_as_file = 1 ,
    @body = @MyBody;

   PRINT ‘message’
  END
 ELSE
  PRINT ‘nothing in audit’

END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER() ErrorNumber, ERROR_MESSAGE() [Message]
 PRINT ‘FAILED’
END CATCH

Filed under Errors, SQL Server Tagged with , ,

SQL Database Mail – User security to allow facility

05-Jan-1010 Leave a comment

 
Security > Logins > Pick Login > Properties > User Mapping > msdb > tick on
Database role membership include DatabaseMailUserRole
 
msdb has more database role memberships. May be resolution to LinkedServer issue. 
 
PROBLEM
Procedure sp_send_dbmail, line 141 profile name is not valid
 
SOLVED
 
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
    @principal_name = 'ApplicationUser',
    @profile_name = 'AdventureWorks2008R2 Administrator Profile',
    @is_default = 1 ;
 
 

Filed under SQL Configuration, SQL Server, Troubleshooting Tagged with ,

SQL Server Database Mail Stopped

09-Dec-0909 Leave a comment

 
 
 
USE msdb ;
GO
EXECUTE dbo.sysmail_start_sp ;
GO
 
To get it going again. Also possible to right-click on Management > Database Mail > View Database Mail Log
 
 

Filed under SQL Configuration, SQL Server Tagged with