Hi friends, in last previous blogs of SQL Server system stored procedures we created database mail profile and database mail account. In this blog we will learn how to add database mail account to database mail profile using stored procedure sysmail_add_profileaccount_sp.
Sysmail_add_profileaccount_sp stored procedure is used to add database mail account to a database mail profile. Both the mail account and mail profile should already exist else stored procedure returns an error. Stored procedure sysmail_add_profileaccount_sp is present in msdb database and owned by schema dbo. If current database is not msdb then this procedure should be executed using three part name.
Syntax:
sysmail_add_profileaccount_sp { [ @profile_id = ] profile_id | [ @profile_name = ] 'profile_name' } ,
{ [ @account_id = ] account_id | [ @account_name = ] 'account_name' }
[ , [ @sequence_number = ] sequence_number ]
Arguments:
@profile_id – Database mail profile id to which mail account added.
@profile_name – Database mail profile name to which mail account added. Either profile_id or profile_name must be specified.
@account_id – Database mail account id to add to mail profile.
@account_name – Database mail account name to add to mail profile. Either account_id or account_name must be specified.
@sequence_number – Sequence number is used to determine in which sequence database mail uses account within the profile.
Now we will add database mail account to database mail profile which we created earlier in our previous blog here.
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Database Test Mail', @account_name = 'DB_Email_Account', @sequence_number = 1
After executing this procedure database mail account will be added to the mail profile successfully.
That’s all for the day friends:). We will continue with database mail stored procedures in my next blogs.
