[ @profile_name = ] 'profile_name'

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

[ @description = ] '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. new_profile_id is int, with a default of NULL.

0 (success) or 1 (failure)

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.

Execute permissions for this procedure default to members of the sysadmin fixed server role.

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 ;

Reference

Other Resources

Help and Information