i) Open SQL Server Management Studio->View Menu-> Select Template Explorer-> Expand Database Mail in the Template Explorer->Select the 'Simple Database Mail Configuration' template
it opens a SQL Server Template for database mail configuration
Click Query Menu-> Select Specify Values for template parameters-> Enter profile name, account name, SMTP Server name, email address, display name ->OK

Now modify the "Add Account"  part in the  template as follows:

EXECUTE

 

 

@rv=msdb.dbo.sysmail_add_account_sp

 

 

 

@account_name = @account_name,

@email_address

 

= @email_address,

@display_name

 

= @display_name,

@mailserver_name

 

= @SMTP_servername,

@replyto_address

 

= @email_address,

@username

 

= 'sreekar.m',

@password

 

= 'Password1';

Execute the query now to get the profile created for you.

ii) Execute the following script to create the profile with account

 

DECLARE @profile_name sysname,

 

 

@account_name

sysname,

 

 

@SMTP_servername

sysname,

 

 

@email_address

NVARCHAR(128),

 

 

@display_name

NVARCHAR(128);

 

 

-- Profile name. Replace with the name for your profile

 

 

 

SET @profile_name = 'SampleProfile';

 

 

-- Account information. Replace with the information for your account.

 

 

 

SET @account_name = 'SampleAccount';

 

 

 

SET @SMTP_servername = 'smtp.gmail.com';

 

 

 

SET @email_address = 'sreekar.m@gmail.com';

 

 

 

SET @display_name = 'name of the sender';


 

-- Verify the specified account and profile do not already exist.

 

 

IF

 

EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)

 

 

BEGIN

 

 

 

RAISERROR('The specified Database Mail profile (SampleProfile) already exists.', 16, 1);

 

 

 

GOTO done;

 

 

END

 

;

 

 

IF

 

EXISTS (SELECT * FROM msdb.dbo.sysmail_account WHERE name = @account_name )

 

 

BEGIN

 

 

 

RAISERROR('The specified Database Mail account (SampleAccount) already exists.', 16, 1) ;

 

 

 

GOTO done;

 

 

END

 

;

 

 

-- Start a transaction before adding the account and the profile

 

 

BEGIN

 

TRANSACTION ;

 

 

DECLARE

 

@rv INT;

 

 

-- Add the account

 

 

EXECUTE

 

@rv=msdb.dbo.sysmail_add_account_sp

 

 

 

 

@account_name = @account_name,

 

 

@email_address

= @email_address,

 

 

@display_name

= @display_name,

 

 

@mailserver_name

= @SMTP_servername,

 

 

@replyto_address

= @email_address,

 

 

@username

= 'sreekar.m',

 

 

@password

= 'Password1';

 

 

IF

 

@rv<>0

BEGIN

 

 

 

RAISERROR('Failed to create the specified Database Mail account (SampleAccount).', 16, 1) ;

 

 

 

GOTO done;

 

 

END

 

 

-- Add the profile

 

 

EXECUTE

 

@rv=msdb.dbo.sysmail_add_profile_sp

 

 

 

 

@profile_name = @profile_name ;

 

 

IF

 

@rv<>0

BEGIN

 

 

 

RAISERROR('Failed to create the specified Database Mail profile (SampleProfile).', 16, 1);

 

 

 

ROLLBACK TRANSACTION;

 

 

 

GOTO done;

 

 

END

 

;

 

 

-- Associate the account with the profile.

 

 

EXECUTE

 

@rv=msdb.dbo.sysmail_add_profileaccount_sp

 

 

 

 

@profile_name = @profile_name,

 

 

@account_name

= @account_name,

 

 

@sequence_number

= 1 ;

 

 

IF

 

@rv<>0

BEGIN

 

 

 

RAISERROR('Failed to associate the speficied profile with the specified account (SampleAccount).', 16, 1) ;

 

 

 

ROLLBACK TRANSACTION;

 

 

 

GOTO done;

 

 

END

 

;

 

 

COMMIT

 

TRANSACTION;

 

 

done:

GO