Setup Database Mail in SQL Server 2008

  • Article

 

 

 

 

 

 

 

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