Skip to content

sysmail-add-profile-sp-transact-sql.md

Latest commit

 

History

History
105 lines (72 loc) · 4.4 KB

sysmail-add-profile-sp-transact-sql.md

File metadata and controls

105 lines (72 loc) · 4.4 KB
title sysmail_add_profile_sp (Transact-SQL)
description Created a new Database Mail profile in an instance of SQL Server or Azure SQL Managed Instance.
author markingmyname
ms.author maghan
ms.reviewer randolphwest
ms.date 06/23/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
f1_keywords
sysmail_add_profile_sp_TSQL
sysmail_add_profile_sp
helpviewer_keywords
sysmail_add_profile_sp
dev_langs
TSQL

sysmail_add_profile_sp (Transact-SQL)

[!INCLUDE SQL Server - ASDBMI]

Creates a new Database Mail profile.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: 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 [!INCLUDE ssazuremi-md], 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 [!INCLUDE ssDEnoversion] 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

[!INCLUDE msdb-execute-permissions]

Examples

A. Create a new profile

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

EXECUTE 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 AS 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