.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

Configure DataBase Mail in SQL Server 2008 Scripting

Posted By:Sasi Prabhu       Posted Date: June 23, 2011    Points: 200    Category: DataBase    URL: http://www.dotnetspark.com  

Configure Database Mail in SQL Server 2008 through Scripting step by step.
 


Step 1 :

Perform Reconfiguration

use master
go
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE
GO

Step 2 :

Database Mail Profile and Account Creation

use msdb
go

DECLARE @profile_name sysname,
        @account_name sysname,
        @SMTP_servername sysname,
        @email_address NVARCHAR(128),
        @descs nvarchar(256),
        @replymail nvarchar(128),
        @display_name NVARCHAR(128),
        @port int,
        @username nvarchar(128),
        @password nvarchar(128);


        SET @profile_name = 'profilename'; -- Enter Profilename
        SET @account_name = 'Account_Name'; --Enter Account name
        set @descs='Description'; -- Enter Account Description
        SET @SMTP_servername = 'smtp.yourdmain.com'; -- Enter Domain Name
        SET @email_address = 'mail@yourdomain.com'; -- Enter Email Address
        set @replymail='mail@yourdomain.com';  -- Enter Email Address Optional
        SET @display_name = 'SenderName';  -- Name which is Display in the Receiver Mail
        set @port =25; -- Enter Port Number, 25 is Default.
        set @username ='mail@yourdomain.com'; -- username for ur mail
        set @password='password'; -- Password of Email

IF EXISTS (SELECT * FROM msdb.dbo.sysmail_profile WHERE name = @profile_name)
BEGIN
  RAISERROR('The specified Database Mail profile (@profile_name) 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 (@account_name) already exists.', 16, 1) ;
 GOTO done;
END;

BEGIN TRANSACTION ;

DECLARE @rv INT;

EXECUTE @rv=msdb.dbo.sysmail_add_account_sp
    @account_name = @account_name,
    @description=@descs,
    @email_address = @email_address,
    @replyto_address=@replymail,
    @display_name = @display_name,
    @mailserver_name = @SMTP_servername,
    @port=@port,
    @username=@username,
    @password=@password;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail account (@account_name).', 16, 1) ;
    GOTO done;
END

EXECUTE @rv=msdb.dbo.sysmail_add_profile_sp
    @profile_name = @profile_name ,
    @description=@descs;

IF @rv<>0
BEGIN
    RAISERROR('Failed to create the specified Database Mail profile (@profile_name).', 16, 1);
    ROLLBACK TRANSACTION;
    GOTO done;
END;

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 (@account_name).', 16, 1) ;
    ROLLBACK TRANSACTION;
    GOTO done;
END;

COMMIT TRANSACTION;

done:

Step 3 :

Restart the SQL Server Agent.

Step 4 :

Send Mail

GO
use msdb
go
 exec msdb.dbo.sp_send_dbmail @profile_name='ProfileName', -- Enter Sender Name
  @recipients='SenderEmail@domain.com',  -- Assign To Address for email
  @subject='Test message',  -- Enter Subject of the Mail Here.
  @body='This is the body of the test message. Congrates Database Mail Received By you Successfully.' -- Enter Body of the mail Here.
 go

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend