Hi friends, today in SQL Server system stored procedure series we will continue with database mail procedure and will learn how to create database mail account using stored procedure sysmail_add_account_sp.
Sysmail_add_account_sp stored procedure is used to create database mail account which contains information about SMTP account.
Syntax of sysmail_add_account_sp is as:
sysmail_add_account_sp [ @account_name = ] 'account_name', [ @email_address = ] 'email_address' , [ [ @display_name = ] 'display_name' , ] [ [ @replyto_address = ] 'replyto_address' , ] [ [ @description = ] 'description' , ] [ @mailserver_name = ] 'server_name' [ , [ @mailserver_type = ] 'server_type' ] [ , [ @port = ] port_number ] [ , [ @username = ] 'username' ] [ , [ @password = ] 'password' ] [ , [ @use_default_credentials = ] use_default_credentials ] [ , [ @enable_ssl = ] enable_ssl ] [ , [ @account_id = ] account_id OUTPUT ]
Arguments
@account_name – Name of new database mail account.
@email_address – It is email address which will be used to send the message. Its datatype is nvarchar(128).
@display_name – Display name to use on email-address from this database mail account. Its datatype is nvarchar(128).
@replyto_address – It is email address where reply to messages will be sent under sent to. Its datatype is nvarchar(128).
@description – Description of the database mail account.
@mailserver_name – Name or ip address of the SMTP mail server used for this database mail account.
@mailserver_type – Type of email server with default value ‘SMTP’.
@port – Port number of the email server with default value 25.
@username – Username to use to login on email server. Its datatype is nvarchar(128),
@password – Password used to logon email server. Its datatype is nvarchar(128).
@user_default_credentials – Flag used to specify whether to send the mail to SMTP server using credentials of SQL Server Database Engine or not having default value 0. When flag value is 1, it will use credentials of Database Engine. When flag value 0, it will use credentials defined in parameters @username and @password.
@enable_SSL – Flag used to specify whether database mail use SSL or not with default value 0.
@account_id – It will return the account id of new database mail account.
Now we will create a database mail account using following code:
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'DB_Email_Account', @display_name = 'SQL Database Email Account', @email_address = 'kapil190588@gmail.com', @replyto_address = 'kapil_k@outlook.com', @mailserver_name = 'smtp.gmail.com', @port=587, @enable_ssl=0
After executing this code, a database mail accounts gets created. Now we need to add this mail account to a database profile which we will see in our later blogs.
That’s all for the day folks.
