Edit

Share via

sp_notify_operator (Transact-SQL)

  • Article

Applies to: SQL Server

Sends an e-mail message to an operator using Database Mail.

Transact-SQL syntax conventions

Syntax

sp_notify_operator
    [ [ @profile_name = ] N'profile_name' ]
    [ , [ @id = ] id ]
    [ , [ @name = ] N'name' ]
    [ , [ @subject = ] N'subject' ]
    [ , [ @body = ] N'body' ]
    [ , [ @file_attachments = ] N'file_attachments' ]
    [ , [ @mail_database = ] N'mail_database' ]
[ ; ]

Arguments

[ @profile_name = ] N'profile_name'

The name of the Database Mail profile to use to send the message. @profile_name is sysname, with a default of NULL. If @profile_name isn't specified, the default Database Mail profile is used.

[ @id = ] id

The identifier for the operator to send the message to. @id is int, with a default of NULL.

One of @id or @name must be specified.

[ @name = ] N'name'

The name of the operator to send the message to. @name is sysname, with a default of NULL.

One of @id or @name must be specified.

An e-mail address must be defined for the operator before they can receive messages.

[ @subject = ] N'subject'

The subject for the e-mail message. @subject is nvarchar(256), with a default of NULL.

[ @body = ] N'body'

The body of the e-mail message. @body is nvarchar(max), with a default of NULL.

[ @file_attachments = ] N'file_attachments'

The name of a file to attach to the e-mail message. @file_attachments is nvarchar(512), with a default of NULL.

[ @mail_database = ] N'mail_database'

Specifies the name of the mail host database. @mail_database is sysname, with a default of msdb. If no @mail_database is specified, the msdb database is used by default.

Return code values

0 (success) or 1 (failure).

Remarks

Sends the message specified to the e-mail address of the operator specified. If the operator has no e-mail address configured, sp_notify_operator returns an error.

Database Mail and a mail host database must be configured before a notification can be sent to an operator.

Permissions

You can grant EXECUTE permissions on this procedure, but these permissions might be overridden during a SQL Server upgrade.

Other users must be granted one of the following SQL Server Agent fixed database roles in the msdb database:

  • SQLAgentUserRole
  • SQLAgentReaderRole
  • SQLAgentOperatorRole

For details about the permissions of these roles, see SQL Server Agent Fixed Database Roles.

Examples

The following example sends a notification e-mail to the operator François Ajenstat using the AdventureWorks Administrator Database Mail profile. The subject of the e-mail is Test Notification. The e-mail message contains the sentence, This is a test of notification via e-mail.

USE msdb;
GO

EXEC dbo.sp_notify_operator
    @profile_name = N'AdventureWorks Administrator',
    @name = N'François Ajenstat',
    @subject = N'Test Notification',
    @body = N'This is a test of notification via e-mail';
GO

Related content