Skip to content

Latest commit

 

History

History
105 lines (72 loc) · 4.41 KB

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

File metadata and controls

105 lines (72 loc) · 4.41 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs
sysmail_add_profile_sp (Transact-SQL)
Created a new Database Mail profile in an instance of SQL Server or Azure SQL Managed Instance.
markingmyname
maghan
randolphwest
08/21/2024
sql
system-objects
reference
sysmail_add_profile_sp_TSQL
sysmail_add_profile_sp
sysmail_add_profile_sp
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.

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