Creates a new Database Mail profile.
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 (Transact-SQL).
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 for the Microsoft SQL Server Database Engine 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 is not msdb.
A. Creating 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. Creating 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 ;