Edit

Share via

sysmail_add_profile_sp (Transact-SQL)

  • Article

Applies to: SQL Server Azure SQL Managed Instance

Creates a new Database Mail profile.

Transact-SQL syntax conventions

Syntax

sysmail_add_profile_sp [ @profile_name = ] 'profile_name'
    [ , [ @description = ] N'description' ]
    [ , [ @profile_id = ] new_profile_id OUTPUT ]
[ ; ]

Arguments

[ @profile_name = ] 'profile_name'

The name for the new profile. @profile_name is sysname, with no default.

To send e-mail using SQL Server Agent jobs in Azure SQL Managed Instance, SQL Server Agent can use only one Database Mail profile, and it must be called AzureManagedInstance_dbmail_profile. For more information and a sample script, see Azure SQL Managed Instance SQL Agent job notifications.

[ @description = ] N'description'

The optional description for the new profile. @description is nvarchar(256), with no default.

[ @profile_id = ] new_profile_id OUTPUT

Returns the ID for the new profile. @profile_id is int, with a default of NULL.

Return code values

0 (success) or 1 (failure).

Remarks

A Database Mail profile holds any number of Database Mail accounts. Database Mail stored procedures can refer to a profile by either the profile name or the profile ID generated by this procedure. For more information about adding an account to a profile, see sysmail_add_profileaccount_sp.

The profile name and description can be changed with the stored procedure sysmail_update_profile_sp, while the profile ID remains constant for the life of the profile.

The profile name must be unique in the SQL Server Database Engine instance or the stored procedure returns an error.

The stored procedure sysmail_add_profile_sp is in the msdb database and is owned by the dbo schema. The procedure must be executed with a three-part name if the current database isn't msdb.

Permissions

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

Examples

A. Create a new profile

The following example creates a new Database Mail profile named AdventureWorks Administrator.

EXEC msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'AdventureWorks Administrator',
       @description = 'Profile used for administrative mail.';

B. Create a new profile, saving the profile ID in a variable

The following example creates a new Database Mail profile named AdventureWorks Administrator. The example stores the profile ID number in the variable @profileId and returns a result set containing the profile ID number for the new profile.

DECLARE @profileId INT;

EXECUTE msdb.dbo.sysmail_add_profile_sp
       @profile_name = 'AdventureWorks Administrator',
       @description = 'Profile used for administrative mail.',
       @profile_id = @profileId OUTPUT;

SELECT @profileId;

Related content