Configure Database Mail

by Amol 30. April 2010 12:55
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.
 
Here is the script to configure database mail by creating Database Mail Account and Database Mail Profile.
Replace Placeholders according to your need. For example Replace #EmailId# with valid Email address.
 

Use [msdb]

GO

 

sp_configure 'show advanced options', 1;

GO

RECONFIGURE

GO

sp_configure 'Database Mail XPs', 1;

GO

RECONFIGURE

GO

 

-- Delete Principle profile if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp

                  @principal_id = 0,

                  @profile_name = '#ProfileName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Pronciple Profile'

 

END CATCH

 

GO

 

-- Delete profile account if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp

                  @profile_name = '#ProfileName#',

                  @account_name = '#AccountName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Profile Account'

 

END CATCH

 

GO

 

-- Delete account if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_account_sp

              @account_name = '#AccountName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Account'

 

END CATCH

 

GO

 

-- Delete profile if exits

BEGIN TRY

 

      EXECUTE msdb.dbo.sysmail_delete_profile_sp

              @profile_name = '#ProfileName#'

 

END TRY

BEGIN CATCH

 

      PRINT 'Invalid Profile'

 

END CATCH

 

GO

 

-- Create a Database Mail account

DECLARE  @emailaddress varchar (100)

        ,@mailservername varchar(128)

 

SET @emailaddress = '#EmailId#'

SET @mailservername = '#MailServerName)'

 

EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = '#AccountName#',

    @description = 'Mail account for administrative e-mail.',

    @email_address =  @emailaddress ,

    @display_name = 'Automated Mailer',

    @mailserver_name = @mailservername ,

    @port = 25,

    @use_default_credentials = 1

 

GO

 

-- Create a Database Mail Profile

EXECUTE msdb.dbo.sysmail_add_profile_sp

       @profile_name = '#ProfileName#',

       @description = 'Profile used for mail'

 

 

GO

 

-- Add the account to the profile

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = '#ProfileName#',

    @account_name = '#AccountName#',

    @sequence_number = 1

 

GO

 

---- Grant access to the profile to the DBMailUsers role

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @principal_id = 0,

    @profile_name = '#ProfileName#',

    @is_default = 1

 

GO

Tags: ,

SQL Server Scripts

Add comment




  Country flag
biuquote
  • Comment
  • Preview
Loading


Category

Recent Posts

Tag cloud