Stored Procedure

Sysmail_add_profileaccount_sp – SQL Server Database Mail Stored Procedure – Part 7

Hi friends, in last previous blogs 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 Sysmail_add_account_sp – SQL Server Database Mail Stored Procedure – Part 5 and Sysmail_add_profile_sp – SQL Server Database Mail Stored Procedure – Part 3.

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 :).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s